User level security question

[floydman]

I was previously using a single database password but had to move to user lever security when I made a Master and Replica database, here's my problem:

I set up 2 users, Steve and Generic, Steve is for me to Admininster the database and Generic is for everyone else to login. I made Steve a member of Admins and Users and Generic a member of Users only.

I figured that I could login as Steve and make changes but all the Forms show up as belonging to Admin and I can't get to design mode as Steve. Why not if I'm a member of the Admins group ? Did I goof here because I didn't see an option to set a password for the Admin user ?

 

[statsman]

Thats because you left the user "Admin" in the database.

The user "Admin" is the default user (why Microsoft used that name I can't imagine). Don't confuse the user "Admin" with the group Admin & Users.

Go back and check the rights that "Admin" has. Give yourself the same rights. Then move "Admin" from the group Admin&Users to generic.

BTW - I'm still rather new at security myself. I'm sure someone else with more experience will correct me if I'm wrong. Perhaps you should wait til Tuesday or Wednesday of next week to see if anyone else has posted a suggestion.

In the meantime go to:
http://www.geocities.com/jacksonmacd
download the first file – Security Paper by Jack Macdonald. It’s a little long but it tells you everything you need to know.

 

[The_Doc_Man]

More or less got it, Statsman

The idea is

First, COPY the default workgroup to another name in the same folder as the DB is located. OR use the workgroup administrator function to create a new workgroup in that place. Assure that the name is NOT SYSTEM.MDW because there is a Mack Truck sized hole in doing that.

Then make yourself (or a clone of yourself) a member of the ADMINS (note: plural) group. Then log out. Then log in as the account you made a member of ADMINS group. Take the ADMIN user (not plural) out of the ADMINS group (plural). Do the steps in this paragraph in the exact order as stated or else kiss your DB bye-bye.

Do what you wish with the other accounts. Please note that you CANNOT delete the ADMIN account or take it out of all groups. All accounts are at a minimum in the Users group. ALWAYS grant permissions to groups. THEN give membership in the group to the individual users. (OK, for small databases, not that big a deal... but still falls under "best practices" for Access security.) Note also that there is no reason to allow the USERS group or the ADMIN (singular) account to have any permission at all. Typical usage is to define user groups by the roles they play, then give permissions according the needs of each role. It is usually bad form to allow "USERS" group any more than simple READ access to something innocuous. Let all significant permissions derive from the groups you define.

Never allow empty passwords. This is one of the common holes people leave behind.

Don't forget that you have the ability to set permissions not only on table, query, form, and other objects. You can also set permissions on the DATABASE OBJECT as a whole. A permission that applies to DB objects but not to others is "Open Exclusive" - which you can deny to non-admin users. And if you don't assure non-blank passwords then some doofus will come along and use SYSTEM.MDW to get into your database as ADMIN.

 

[floydman]

Many thanks statsman and docman, still a little confused though ( I haven't made any changes yet as I want to understand this before I go ahead).

The user 'Steve' is a member of the Admins group.

The user 'Admin' is only a member of the User group.

When I login as Steve and try to open a form in Design view I get the message '...to read this object, you must have Read Design permission for it...'.

I then go to User and Group Permissions and select Groups. The group Admins has no permissions on any of the database objects, forms, tables queries etc. From what I can see none of the Groups have any permissions on anything. Shouldn't they be there by default?

 

[The_Doc_Man]

Not always by default. Depends on the owner. But that's OK, the interface for setting permissions allows you to select an object type, click on the top object, then scroll down to the bottom object and do shift-click to select all objects. Then set the permissions you want for Admins group. Gotta do that for every object type. Oh, you can also set the default permissions for new objects too.

You might also wish to double-check the ownership to assure that the right owner has been asserted. Normally, Admins (group) can be owner. Or make Steve (user) the owner. You really don't want Admin (user) as the owner because as such, admin can take over ownership. (It's a subtle little ability but OH so deadly.) From there, it is all over but the whimpering.

 

[floydman]

Many thanks, went through it all and seems to well now, the User Steve now is in control.
Cheers