I am using Access 2003. I think I finally figured out Access security, but I'm not sure if I did it right.
Here are my steps:
--Opened a blank copy of Access and created a new blank database. Named it SecurityTest.mdb and saved it on the shared drive.
--In that new database, went to File, Get External Data, Import. Imported all the objects from my unsecured database.
--Went to Tools, Security, Workgroup Administrator. Temporarily joined the Access default workgroup at C:\Documents and Settings\[user]\Application Data\Microsoft\Access\System.mdw.
--Closed the SecurityTest.mdb to refresh the settings. Then opened it again.
--Went to Tools, Security, User Level Security Wizard.
--Clicked 'Create a new Workgroup Information File'
--Clicked 'I want to create a shortcut to open my security-enhanced database'. Clicked Next.
--For the screen where you can choose the database objects to protect, left it as-is (everything selected) and clicked Next.
--For the Groups, selected only: Full Data Users and Read-Only Users. Clicked Next.
--For the next screen, left the default: 'No, the Users group should not have any permissions'. Clicked Next.
--For the next screen, added 3 users:
1). 'readonly' as the user name and password.
2). 'team' as the user name and password.
3). 'dba' as the user name and testtest as the password.
Now had 4 users: [user] plus the 3 above. Clicked Next.
--Left the default 'select a user and assign the user to groups'.
1). For [user], checked all 3 boxes (Full Data Users, Read-Only Users and Admins).
2). For readonly, checked the Read-Only Users.
3). For team, checked Full Data Users.
4). For dba, checked all 3 boxes (Full Data Users, Read-Only Users and Admins).
--Clicked Next. Clicked Finish. Closed the SecurityTest.mdb.
--Created batch shortcut file named shortcut.bat. Went to Start, Programs, Accessories, Notepad. In Notepad, typed:
@echo off
START " " "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "\\nctbp\Common\Program\Project Health\SecurityTest.mdb" /WRKGRP "\\nctbp\Common\Program\Project Health\Security.mdw"
To test it, I tried to open the SecurityTest.mdb directly. I got a message that said I don't have permissions to open it. Good.
Then I tried using the shortcut.bat. I tested all 3 user ids. They all worked as they should with the correct permissions.
But did I do all this correctly?
I'm wondering because I logged in as dba and wanted to modify the workgroup. Therefore, I tried to run the security wizard again, but got a message 'you must be a member of the Administrators (Admins) group to run the Security Wizard and help protect your database'.
But if i go to Tools, Security, User and Group Accounts and select dba in the Users tab, it says dba is a member of Full-Data Users, Read-Only Users, Admins and Users.
So isn't dba a member of the Admins group?
Also, I was testing creating a new group. So I went to Tools, Security, User and Group Accounts and clicked on the Groups tab. I clicked New and created a group called ACT Group with a personal id of 1234. Then I went to Users and clicked New and created a new user called act with a personal id of 1234. Then in the Users tab, I added the ACT Group to the act user. So now the act user is a member of ACT Group and Users Group.
I clicked 'clear password'.
But if i close the database and use shortcut.bat to log in, and type act as the user and leave the password blank, i get 'you do not have the necessary permissions to use the <name> object.
So i think i did something wrong somewhere.
It works okay as is, but i'm not sure how to create new users and groups.
thanks and sorry for the long post!!
Here are my steps:
--Opened a blank copy of Access and created a new blank database. Named it SecurityTest.mdb and saved it on the shared drive.
--In that new database, went to File, Get External Data, Import. Imported all the objects from my unsecured database.
--Went to Tools, Security, Workgroup Administrator. Temporarily joined the Access default workgroup at C:\Documents and Settings\[user]\Application Data\Microsoft\Access\System.mdw.
--Closed the SecurityTest.mdb to refresh the settings. Then opened it again.
--Went to Tools, Security, User Level Security Wizard.
--Clicked 'Create a new Workgroup Information File'
--Clicked 'I want to create a shortcut to open my security-enhanced database'. Clicked Next.
--For the screen where you can choose the database objects to protect, left it as-is (everything selected) and clicked Next.
--For the Groups, selected only: Full Data Users and Read-Only Users. Clicked Next.
--For the next screen, left the default: 'No, the Users group should not have any permissions'. Clicked Next.
--For the next screen, added 3 users:
1). 'readonly' as the user name and password.
2). 'team' as the user name and password.
3). 'dba' as the user name and testtest as the password.
Now had 4 users: [user] plus the 3 above. Clicked Next.
--Left the default 'select a user and assign the user to groups'.
1). For [user], checked all 3 boxes (Full Data Users, Read-Only Users and Admins).
2). For readonly, checked the Read-Only Users.
3). For team, checked Full Data Users.
4). For dba, checked all 3 boxes (Full Data Users, Read-Only Users and Admins).
--Clicked Next. Clicked Finish. Closed the SecurityTest.mdb.
--Created batch shortcut file named shortcut.bat. Went to Start, Programs, Accessories, Notepad. In Notepad, typed:
@echo off
START " " "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "\\nctbp\Common\Program\Project Health\SecurityTest.mdb" /WRKGRP "\\nctbp\Common\Program\Project Health\Security.mdw"
To test it, I tried to open the SecurityTest.mdb directly. I got a message that said I don't have permissions to open it. Good.
Then I tried using the shortcut.bat. I tested all 3 user ids. They all worked as they should with the correct permissions.
But did I do all this correctly?
I'm wondering because I logged in as dba and wanted to modify the workgroup. Therefore, I tried to run the security wizard again, but got a message 'you must be a member of the Administrators (Admins) group to run the Security Wizard and help protect your database'.
But if i go to Tools, Security, User and Group Accounts and select dba in the Users tab, it says dba is a member of Full-Data Users, Read-Only Users, Admins and Users.
So isn't dba a member of the Admins group?
Also, I was testing creating a new group. So I went to Tools, Security, User and Group Accounts and clicked on the Groups tab. I clicked New and created a group called ACT Group with a personal id of 1234. Then I went to Users and clicked New and created a new user called act with a personal id of 1234. Then in the Users tab, I added the ACT Group to the act user. So now the act user is a member of ACT Group and Users Group.
I clicked 'clear password'.
But if i close the database and use shortcut.bat to log in, and type act as the user and leave the password blank, i get 'you do not have the necessary permissions to use the <name> object.
So i think i did something wrong somewhere.
It works okay as is, but i'm not sure how to create new users and groups.
thanks and sorry for the long post!!
