I have an Access Project for the front end and a MSDE as back end. The
database is in a Server and only administrators have access to it. I've
installed a copy of the .adp on each user's computer. I plan to convert to
..ade once the project is finalized.
I have a UserName and password set up (and saved) to connect the project
file to the server.
Next, I need to set up user level security for the forms. When the project
opens, there is a login form asking for username and password, if valid a
main form (switchboard) opens. Based on the login, the user will have access
to certain areas - subforms. Administrators can open all the subforms on the
main form. Managers don't have access to the admin area. And so on.
I need a set up for the Owners of the Company to be able to manage the
permissions from the project file itself, not having to access the database
manager on the server (thanks Andrea, you saved my life with this one). Also
I have a cmd button to change the user, therefor change the permissions.
Is there a way to do this, like creating some code behind the forms, or do I
need to do it on the SQL server?
I've created a table for users with ID's, passwords and departments. Only
the Company owners should be able to access the form to update these
records...
This is an small company without any IT person around and the users need a
simple, straighforward project to use.
Any help and/or ideas will be greatly appreciated.
gaba
hi,
gaba wrote:
> Hi all,
> I have an Access Project for the front end and a MSDE as back end. The
> database is in a Server and only administrators have access to it.
> I've installed a copy of the .adp on each user's computer. I plan to
> convert to .ade once the project is finalized.
> I have a UserName and password set up (and saved) to connect the
> project file to the server.
> Next, I need to set up user level security for the forms. When the
> project opens, there is a login form asking for username and
> password, if valid a main form (switchboard) opens. Based on the
> login, the user will have access to certain areas - subforms.
> Administrators can open all the subforms on the main form. Managers
> don't have access to the admin area. And so on.
> I need a set up for the Owners of the Company to be able to manage the
> permissions from the project file itself, not having to access the
> database manager on the server (thanks Andrea, you saved my life with
> this one). Also I have a cmd button to change the user, therefor
> change the permissions.
> Is there a way to do this, like creating some code behind the forms,
> or do I need to do it on the SQL server?
> I've created a table for users with ID's, passwords and departments.
> Only the Company owners should be able to access the form to update
> these records...
> This is an small company without any IT person around and the users
> need a simple, straighforward project to use.
> Any help and/or ideas will be greatly appreciated.
I think to understand your indtroduction as you use some sort of user
defined application role...
you have a kind of hardcoded user+pwd used throughout all your app for all
your users and an internal tables referencing the "real users" to their
authorized actions...
thus you can not rely on standard SQL Server policies to block unauthorized
users to access some portions of your project...
if this is not correct, then "amen"... nice to hear you again :D
but if this is correct, you are stuck to write your own authorization
policies.. you have then to write a dialog where all "tasks" are listed and
you have to store, in your actual tables, what is granted and what is not...
you can perhaps define a relation like
tb_Users tb_Groups tb_Permissions
-- -- --
Id |--1 Id 1--| Id
Name | Name |--8 IdGroup
Pwd | |--8 IdTask
IdGroup 8--| | Enable/Disable
|
| tb_Tasks
| --
|--1 Id
Name
IdMaster
IdRif
Permission
(hope is readeble)
where you map users (8=many) to 1 group...
tb_Tasks enumerates all your "securable" activities ...
tb_Permissions stores the actual permissions referencing tb_Tasks and
tb_Groups ...
depending on your needs you can default [Enable/Disable] as desired (always
false, always true)...
or, if you need a very little and simple management, you can even drop that
column and insert a row only for denied (or granted) tasks...
relations always are 1 to many (8 stand for many :D) so that 1 user can only
be member of 1 group...
you know that you have not a "trusted" security policy as anyone can grab
oSql.exe, log in as "whatever" and perform whatever action on the database
depending on the "whatever" login's permissions... more. the "user defined
application role" must be enabled to perform whatever activitiy on the
database it self, and even probably perform backup/restore... quite large
permissions for a "normal" user..
can you reconsider the security design?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
Nice to hear from you again. Thanks for your answer. The price of learning
under pressure...
The users of this project are going to use forms to manipulate the data.
They'll never have direct access to the database itself. The groups are going
to be
Administrator - all rights.
managers - limited rights
technicians - limited to their areas
data entry - limited to their areas
I need to design the project in a way that any of the members of these
groups can access the .ade file at any computer and get the information they
are allow to view/change. Also they need to login as another user without
leaving the database.
I'll study your ideas and how I can apply them. I was thinking on putting
some VBA code behind the login form and depending on the username and
password verify the group the user belongs to. Based on this info, I'll
disable the buttons on the main form therefore the user only can access their
areas. Is this aproach too naiive? These users don't know/care much about
computers and/or databases. The only thing they care is to access the
information quickly.
Have a nice weekend. I'll let you know if I get somewhere.
Thanks a lot,
gaba
"Andrea Montanari" wrote:
> hi,
> gaba wrote:
> I think to understand your indtroduction as you use some sort of user
> defined application role...
> you have a kind of hardcoded user+pwd used throughout all your app for all
> your users and an internal tables referencing the "real users" to their
> authorized actions...
> thus you can not rely on standard SQL Server policies to block unauthorized
> users to access some portions of your project...
> if this is not correct, then "amen"... nice to hear you again :D
> but if this is correct, you are stuck to write your own authorization
> policies.. you have then to write a dialog where all "tasks" are listed and
> you have to store, in your actual tables, what is granted and what is not...
> you can perhaps define a relation like
> tb_Users tb_Groups tb_Permissions
> -- -- --
> Id |--1 Id 1--| Id
> Name | Name |--8 IdGroup
> Pwd | |--8 IdTask
> IdGroup 8--| | Enable/Disable
> |
> | tb_Tasks
> | --
> |--1 Id
> Name
> IdMaster
> IdRif
> Permission
> (hope is readeble)
> where you map users (8=many) to 1 group...
> tb_Tasks enumerates all your "securable" activities ...
> tb_Permissions stores the actual permissions referencing tb_Tasks and
> tb_Groups ...
> depending on your needs you can default [Enable/Disable] as desired (always
> false, always true)...
> or, if you need a very little and simple management, you can even drop that
> column and insert a row only for denied (or granted) tasks...
> relations always are 1 to many (8 stand for many :D) so that 1 user can only
> be member of 1 group...
> you know that you have not a "trusted" security policy as anyone can grab
> oSql.exe, log in as "whatever" and perform whatever action on the database
> depending on the "whatever" login's permissions... more. the "user defined
> application role" must be enabled to perform whatever activitiy on the
> database it self, and even probably perform backup/restore... quite large
> permissions for a "normal" user..
> can you reconsider the security design?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
No comments:
Post a Comment