BobMiner
asked on
WorkGroup file for only 1 Access2k file..
I made a .MDW workgroup file using the WorkAdmin. My original intent was to set up security for just 1 Access file, but now it appears that all Access use is dependant upon the users/groups in this MDW.
1. How can I undo this .mdw influence so unrestricted use of Access is reinstated?
2. Is there a way to use the security features to affect only the one .MDB?
Thanks
Bob
1. How can I undo this .mdw influence so unrestricted use of Access is reinstated?
2. Is there a way to use the security features to affect only the one .MDB?
Thanks
Bob
Bob:
Here is a security primary which brewdog has put out. This might help give you some clues as to where to go.
1) Open Workgroup Administrator (installs with Access, usually located in C:\WINNT\system32\ and called WRKGADM.EXE) and choose New. Give the workgroup a name and workgroup ID. Workgroup ID is important because, if your workgroup ever gets corrupted, you can restore it only by creating a new one using the exact same name and workgroup ID. This is case-sensitive, too.
a) This must be done so users can still get into the default, unsecure version of Access without logging in
b) This creates a new system database, which stores all groups, users, and passwords
i) System database stores: groups, users, passwords
ii) Standard database stores: permissions on individual objects
2) Create an icon on your desktop that points to the Access executable and your workgroup, something like: “C:\Program Files\Microsoft Office\Office\msaccess.exe
a) I put paths in quotes because of the spaces in the file names
b) The /wrkgrp switch functions like the ini file did in Access 2, redirecting Access immediately to verify the security system involved before looking to anything else
3) Open Access through your new icon
4) Choose Tools | Security | User and Group Accounts
a) Create a new user name and personal ID for yourself (or whomever will be the system administrator)
b) Add this new user to the Admins group
c) Enter a new password for the current user – Admin – you are by default logged in as Admin. Passwords are case-sensitive; this is the trigger for Access’ security system: the Admin user has to have a password
d) There is no old password
5) Exit Access and re-open
a) Security will be enforced now that the Admin user has a password
b) Log on with your own (new) user ID
c) You do not have a password yet; leave the password blank
6) Create a blank database
a) This will make you, and not the Admin user, the owner of the database and all of it objects
i) Even if you remove all permissions for all objects from other users, if another user is the owner of an object (or the database itself), Access will grant them certain rights (open/read, etc.)
b) If you are securing an already-existing database, import all objects from that database into this new blank database
7) Choose Tools | Security | User and Group Accounts and remove Admin from the Admins group. This must be done if you want your user ID to have sole control over the database. If you don’t take this step, any user can open your database(s) as long as they guess the password and have whatever privileges you have left to the Admin user.
8) Add, through Tools | Security | User and Group Accounts, other groups and users who will need access to your database(s)
a) Create Groups first, then assign Users to those groups
i) Each individual user is granted, by Access, all rights assigned to them individually and all rights given to any and all groups to which they belong. Access rights are as widely inclusive as possible
b) Assign Group permissions first, then individual User permissions next
9) Set a password for the current user, the system administrator
10) Exit Access
11) Modify your desktop icon to point to the Access executable, then your database, then the workgroup file:
a) “C:\Program Files\Microsoft Office\Office\msaccess.exe
You can at this point either open Workgroup Administrator again and Join the default system.mdw to reset the security on the default version of Access, or let it reset itself when you reboot your machine. On each user’s machine, then, you will want to create an icon pointing directly to your database, with a command line like that shown above. This prevents user confusion when opening the unsecure version of Access.
Here is a security primary which brewdog has put out. This might help give you some clues as to where to go.
1) Open Workgroup Administrator (installs with Access, usually located in C:\WINNT\system32\ and called WRKGADM.EXE) and choose New. Give the workgroup a name and workgroup ID. Workgroup ID is important because, if your workgroup ever gets corrupted, you can restore it only by creating a new one using the exact same name and workgroup ID. This is case-sensitive, too.
a) This must be done so users can still get into the default, unsecure version of Access without logging in
b) This creates a new system database, which stores all groups, users, and passwords
i) System database stores: groups, users, passwords
ii) Standard database stores: permissions on individual objects
2) Create an icon on your desktop that points to the Access executable and your workgroup, something like: “C:\Program Files\Microsoft Office\Office\msaccess.exe
a) I put paths in quotes because of the spaces in the file names
b) The /wrkgrp switch functions like the ini file did in Access 2, redirecting Access immediately to verify the security system involved before looking to anything else
3) Open Access through your new icon
4) Choose Tools | Security | User and Group Accounts
a) Create a new user name and personal ID for yourself (or whomever will be the system administrator)
b) Add this new user to the Admins group
c) Enter a new password for the current user – Admin – you are by default logged in as Admin. Passwords are case-sensitive; this is the trigger for Access’ security system: the Admin user has to have a password
d) There is no old password
5) Exit Access and re-open
a) Security will be enforced now that the Admin user has a password
b) Log on with your own (new) user ID
c) You do not have a password yet; leave the password blank
6) Create a blank database
a) This will make you, and not the Admin user, the owner of the database and all of it objects
i) Even if you remove all permissions for all objects from other users, if another user is the owner of an object (or the database itself), Access will grant them certain rights (open/read, etc.)
b) If you are securing an already-existing database, import all objects from that database into this new blank database
7) Choose Tools | Security | User and Group Accounts and remove Admin from the Admins group. This must be done if you want your user ID to have sole control over the database. If you don’t take this step, any user can open your database(s) as long as they guess the password and have whatever privileges you have left to the Admin user.
8) Add, through Tools | Security | User and Group Accounts, other groups and users who will need access to your database(s)
a) Create Groups first, then assign Users to those groups
i) Each individual user is granted, by Access, all rights assigned to them individually and all rights given to any and all groups to which they belong. Access rights are as widely inclusive as possible
b) Assign Group permissions first, then individual User permissions next
9) Set a password for the current user, the system administrator
10) Exit Access
11) Modify your desktop icon to point to the Access executable, then your database, then the workgroup file:
a) “C:\Program Files\Microsoft Office\Office\msaccess.exe
You can at this point either open Workgroup Administrator again and Join the default system.mdw to reset the security on the default version of Access, or let it reset itself when you reboot your machine. On each user’s machine, then, you will want to create an icon pointing directly to your database, with a command line like that shown above. This prevents user confusion when opening the unsecure version of Access.
ASKER
Thanks Jim,
I actually went thru all those steps already...now I want to click my heels & return to how it was: access opens without prompting for a password, and I don't need to redirect the run command thru the /wrkgrp runline option.
I'm developing a VB5 frontend for workstations with an Access 2k file that will be loaded on a network fileserver. I'm at the point where I need to secure the DB, log in users, store their identities for use in the WHERE parts of SQL statements so they only see the records that pertain to them. So I'm dabbling around in the included features rather than writing some lame login stuff from scratch that would probably be faulty. I'm afraid to implement this at work cause it appears to affect every instance of Access on the system. I just want these features implemented in the little DB that'll reside on the server. The DB is a bunch of tables, queries, and few reports...no forms as all input is accomplished in VB. Should I look at the password feature? If memory serves, that can be bypassed/defeated by holding shift when you open the DB, right?
Hope you can help some more. I upped the points to 100.
Bob
I actually went thru all those steps already...now I want to click my heels & return to how it was: access opens without prompting for a password, and I don't need to redirect the run command thru the /wrkgrp runline option.
I'm developing a VB5 frontend for workstations with an Access 2k file that will be loaded on a network fileserver. I'm at the point where I need to secure the DB, log in users, store their identities for use in the WHERE parts of SQL statements so they only see the records that pertain to them. So I'm dabbling around in the included features rather than writing some lame login stuff from scratch that would probably be faulty. I'm afraid to implement this at work cause it appears to affect every instance of Access on the system. I just want these features implemented in the little DB that'll reside on the server. The DB is a bunch of tables, queries, and few reports...no forms as all input is accomplished in VB. Should I look at the password feature? If memory serves, that can be bypassed/defeated by holding shift when you open the DB, right?
Hope you can help some more. I upped the points to 100.
Bob
I thought you might have but no knowing your expertise level, I had to start somewhere.
The shift key doesn't bypass the password feature of Access. That is done by Access even before the database is loaded. But to use the password you have to have a dedicated MDW, which you don't want to do.
I'd suggest taping into the network security to verify that users and their passwords are valid. What type of network is it? Usually the network not only requires you to have a password to login but can restrict users or groups of users from drives, directories, folders if they havn't been given that permission by the network admin.
You can get the user's name from the network to be used in your app. Also you can use Access security to allow users or groups of users to have access to certain forms, queries, tables, etc.
I'm trying to figure out how you will be tying in the user names to the Where clauses to restrict them to certain records.
If you don't have the users login through Access, then it doesn't appear that they can belong to a group or be recognized by Access for security.
Depending on the network, you can pass the user name to Access through the shortcut by using special network functions. If this is possible, then you can pass the network user name and then workgroup that they should be long to. Just thinking out loud here.
Enough to think about for the moment. Why don't you tell me more what you plan or want to do and lets see if we can come up with a good plan of attack.
Jim
The shift key doesn't bypass the password feature of Access. That is done by Access even before the database is loaded. But to use the password you have to have a dedicated MDW, which you don't want to do.
I'd suggest taping into the network security to verify that users and their passwords are valid. What type of network is it? Usually the network not only requires you to have a password to login but can restrict users or groups of users from drives, directories, folders if they havn't been given that permission by the network admin.
You can get the user's name from the network to be used in your app. Also you can use Access security to allow users or groups of users to have access to certain forms, queries, tables, etc.
I'm trying to figure out how you will be tying in the user names to the Where clauses to restrict them to certain records.
If you don't have the users login through Access, then it doesn't appear that they can belong to a group or be recognized by Access for security.
Depending on the network, you can pass the user name to Access through the shortcut by using special network functions. If this is possible, then you can pass the network user name and then workgroup that they should be long to. Just thinking out loud here.
Enough to think about for the moment. Why don't you tell me more what you plan or want to do and lets see if we can come up with a good plan of attack.
Jim
ASKER
Thanks again, I REALLY appreciate your time.
>>If you don't have the users login through Access, then it doesn't appear that they can belong to a group or be recognized by Access for security. <<
I agree, and I must say, I like the situation I've got with the MDW file,permissions, etc as it pertains to *my* project. I'm just afraid that I'll disable anybody & everybody's use of access as they knew it if I copy this stuff to the LAN. Is there anyway to only have "MY" program run through the MDW, but everybody else's use remain unchanged????
I got the password/user thing working with code from the MSDN page as follows:
Dim dbe As PrivDBEngine
Dim wrk As Workspace
wg = path & "Secured.mdw"
Set dbe = New PrivDBEngine
dbe.SystemDB = wg
dbe.DefaultPassword = "badassgrass"
dbe.DefaultUser = "Bob&Mike"
Set wrk = dbe.Workspaces(0)
sPath = _
"C:\Bobs Work Programs\Shift Balance\DailyData.mdb"
Set db = wrk.OpenDatabase(sPath)
All I have to do is make a Login form, and I can use the populated textbox.text data to send into the above code....then I'll know who is logged on etc.
So....here's what I'm doing:
Personnel Table : ID, Name, blah blah ..
Schedule Table : ID, Days1-7 shifts
Leave Table : ID, date, Approved(bool)
Sched Change Table : ID, date, newshift
Overtime Table : ID, date, OTshift
Date Table : date, weekday number
Transaction Table : Busy (boolean)
Overtime Query : Who's on OT on datetable's date.
Leave Query : Who's on Leave on datetable's date.
Eligble for OT Quuery: who's eligible for OT (not on Leave, not on OT, not scheduled to work.....blah blah)
ELigible for Sched change Query :who's eligible for OT (not on Leave, not on Sched Chg already, is scheduled to work....blah blah)
THe user (a supervisor) clicks on a calender.ocx, the date goes into the datetable, all the queries are populated, snapshots are made, the schedule form is populated. Various drag & drop operations move people to various shcedule changes, overtime assignments, and leave requests whereupon the Transaction table isinterogated, if "Not Busy", is set to "Busy" while tables are addnew-ed, requery, Transaction table to "Not Busy" repopulate the schedule form.
There are 5 areas (so five workstations) running simultaneously. The user table has an "Area" field which is appended to every record of every table so that each Area only sees the data pertaining to employees on their area. I'd LOVE to have the Area saved in the user's MDW file/table. Any way to do that? I thought of making a 3 letter requirement and concantenating it to the PID which I could then retrieve from the users property of the workspace, no?
So now what do you think?
Thanks again,
Bob
>>If you don't have the users login through Access, then it doesn't appear that they can belong to a group or be recognized by Access for security. <<
I agree, and I must say, I like the situation I've got with the MDW file,permissions, etc as it pertains to *my* project. I'm just afraid that I'll disable anybody & everybody's use of access as they knew it if I copy this stuff to the LAN. Is there anyway to only have "MY" program run through the MDW, but everybody else's use remain unchanged????
I got the password/user thing working with code from the MSDN page as follows:
Dim dbe As PrivDBEngine
Dim wrk As Workspace
wg = path & "Secured.mdw"
Set dbe = New PrivDBEngine
dbe.SystemDB = wg
dbe.DefaultPassword = "badassgrass"
dbe.DefaultUser = "Bob&Mike"
Set wrk = dbe.Workspaces(0)
sPath = _
"C:\Bobs Work Programs\Shift Balance\DailyData.mdb"
Set db = wrk.OpenDatabase(sPath)
All I have to do is make a Login form, and I can use the populated textbox.text data to send into the above code....then I'll know who is logged on etc.
So....here's what I'm doing:
Personnel Table : ID, Name, blah blah ..
Schedule Table : ID, Days1-7 shifts
Leave Table : ID, date, Approved(bool)
Sched Change Table : ID, date, newshift
Overtime Table : ID, date, OTshift
Date Table : date, weekday number
Transaction Table : Busy (boolean)
Overtime Query : Who's on OT on datetable's date.
Leave Query : Who's on Leave on datetable's date.
Eligble for OT Quuery: who's eligible for OT (not on Leave, not on OT, not scheduled to work.....blah blah)
ELigible for Sched change Query :who's eligible for OT (not on Leave, not on Sched Chg already, is scheduled to work....blah blah)
THe user (a supervisor) clicks on a calender.ocx, the date goes into the datetable, all the queries are populated, snapshots are made, the schedule form is populated. Various drag & drop operations move people to various shcedule changes, overtime assignments, and leave requests whereupon the Transaction table isinterogated, if "Not Busy", is set to "Busy" while tables are addnew-ed, requery, Transaction table to "Not Busy" repopulate the schedule form.
There are 5 areas (so five workstations) running simultaneously. The user table has an "Area" field which is appended to every record of every table so that each Area only sees the data pertaining to employees on their area. I'd LOVE to have the Area saved in the user's MDW file/table. Any way to do that? I thought of making a 3 letter requirement and concantenating it to the PID which I could then retrieve from the users property of the workspace, no?
So now what do you think?
Thanks again,
Bob
Supposedly, you are supposed to be able to have the shortcut use your MDW for logging in to your application. You add /wrkgrp Secure.MDW but I have not had a lot of luck using it. That is why I finally dropped it out.
BTW, while we are on MDWs. I discovered that even though I am logged into using my developers MDW when I am working in Access 97, if I open Access 2000, Access logs me out of my developers MDW and puts me into the default System.MDW. Damn frustrating when I try to get back into my Access 97 work. Have to go through the Workgroup Admin and relogin again to the right MDW.
What you are doing here interests me. Back in the days of DOS based and not multi-user databases, I had to do a similar task in order to let different people use the software at the same time and verify who was using what module so I didn't have conflicts.
We were on Novell and I used the Novell Group function to get the Area information that I needed.
The owner of the MDW is the Jet Engine. You can open an MDW and add queries and such. However, you can not change any of the queries there. I played around with one and found that you can change the owner of mSysAccounts and possibly even add another field to it for the area.
There is no PID but there is a password and a SID. However they are binary encoded fields. You could add the Area field to each user. I imagine that you would have to write an Access or VB program in order to go in and add the values. I don't think that it would be available through the Workgroup Admin program.
I'm about running dry on more responses to this question and we are way over the initial question parameters.
Why don't we close this question out, so I can get points to edge myself into fifth place in the Access topic? We can still go back and forth on this question either through here or email.
Jim
BTW, while we are on MDWs. I discovered that even though I am logged into using my developers MDW when I am working in Access 97, if I open Access 2000, Access logs me out of my developers MDW and puts me into the default System.MDW. Damn frustrating when I try to get back into my Access 97 work. Have to go through the Workgroup Admin and relogin again to the right MDW.
What you are doing here interests me. Back in the days of DOS based and not multi-user databases, I had to do a similar task in order to let different people use the software at the same time and verify who was using what module so I didn't have conflicts.
We were on Novell and I used the Novell Group function to get the Area information that I needed.
The owner of the MDW is the Jet Engine. You can open an MDW and add queries and such. However, you can not change any of the queries there. I played around with one and found that you can change the owner of mSysAccounts and possibly even add another field to it for the area.
There is no PID but there is a password and a SID. However they are binary encoded fields. You could add the Area field to each user. I imagine that you would have to write an Access or VB program in order to go in and add the values. I don't think that it would be available through the Workgroup Admin program.
I'm about running dry on more responses to this question and we are way over the initial question parameters.
Why don't we close this question out, so I can get points to edge myself into fifth place in the Access topic? We can still go back and forth on this question either through here or email.
Jim
ASKER
Thank you Jim....I have run on a bit ;-)
I bumped the points up to 150.
Please do allow me to email....I'm at
bobminer@lobo.net
I look forward to further chatting.
Regards,
Bob
PS. The shortcut does work on my machine:
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE
If I run the access.exe directly I can't get in! Please do EMail me. I'm gonna repost this question just to figure out how to undo this inability to run access.
I bumped the points up to 150.
Please do allow me to email....I'm at
bobminer@lobo.net
I look forward to further chatting.
Regards,
Bob
PS. The shortcut does work on my machine:
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE
If I run the access.exe directly I can't get in! Please do EMail me. I'm gonna repost this question just to figure out how to undo this inability to run access.
ASKER
Hey Jim....
DO answer this question.....
AND check out the "UNDO an MDW file?" question. The long & short of it is I inadvertently had 2 sysem.mdw's. When I deleted the *new* one (leaving the original in the Windows\System dir) all Access files went back to no password prompt EXCEPT the secured one! Woo Hoo! Thats *EXACTLY* as I wanted it.
Thanks for all you input,
Bob
DO answer this question.....
AND check out the "UNDO an MDW file?" question. The long & short of it is I inadvertently had 2 sysem.mdw's. When I deleted the *new* one (leaving the original in the Windows\System dir) all Access files went back to no password prompt EXCEPT the secured one! Woo Hoo! Thats *EXACTLY* as I wanted it.
Thanks for all you input,
Bob
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>"Make this comment an answer".
I'll do it.
Bob
I'll do it.
Bob
MDWs only restrict access to the MDB and really have no other security features. That normally is if the only way to get into the MDB is through this workgroup file. If there were no other security provisions made on the MDB, anyone can change to the System.MDW and open the file. Have you tried this to see if it works?
1. Be sure that there are no restrictions on permissions in the MDB.
Change to the System.MDW and create a blank database. Import all objects from the original MDB. If they all import without complaining, you have an unrestricted use Access file.
2. Forget the MDW unless you really want people to log in with a username and password. By changing the permissions within the MDB so allow and exclude certain groups or users from having certain permissions is the way to go.
What security features did you want to have for the one MDB?
Give me a little more detail and I'll give you the security setups for that.
Jim