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.

"Andrea Montanari" wrote:

> 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.shtm http://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
>
>