?
Solved

Microsoft Access mdw security file.

Posted on 2005-05-04
14
Medium Priority
?
620 Views
Last Modified: 2013-12-05
As you know Microsoft Access has a limitation of 255 users which can access a database at a time, the problem I have is that our company relies heavily on access and the mdw file that controls the security for access has reached it's limit (more then 255 people trying to open one of our Access DB at one time) therefore we have copied the mdw file and have created one for each major division of our company (Seven total) So far we have had no issues that we are aware of however our concern / question is as follows:  Is the security controlled solely through the CID number that gets created (Like a autonum)when you create a new account? Or does Access take into consideration the users ID and Password they are required to enter when they open the database or does Access take into consideration only the CID number.

If I create a account for a new user for the IT Department lets say ID: i1990 (Full Rights) and Access assigns this account a CID number of 01 and then I create a account for a new user for the Finance Department lets say ID: i1991 (Read Only) and since the CID is a autonum and assigns this a CID number of 01 as well will Access distinguish the two and allow the appropriate rights?

Thanks

Arnold
0
Comment
Question by:IEHP1
  • 7
  • 3
  • 2
  • +2
14 Comments
 
LVL 17

Expert Comment

by:Arji
ID: 13932239
Yes it should.   I beleive access is granted based on all three components of login: username, password and CID.  I'm really surprised Access is handling 255 simultaneous users.....good for you!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 13932568
IEHP1,

How do you have Access configured that you can have 255 users?
I've read that the practical limit is anywhere between 10-25 concurrent users.
0
 
LVL 17

Expert Comment

by:Arji
ID: 13932678
boag2000

That's a good question....I'm wondering that myself. :-)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 39

Expert Comment

by:stevbe
ID: 13933708
<will Access distinguish the two and allow the appropriate rights?>
Yes.

0
 

Author Comment

by:IEHP1
ID: 13936715
Rob,

Is this a best guest or are you sure?  I beleive your right however I have another co-workers that disagrees and believes that the permissions are based on the CID only.
0
 
LVL 17

Expert Comment

by:Arji
ID: 13936904
I'm pretty sure from what I remember.  It is my impression that the CID is encoded from the username, password and Personal ID(entered when you create a new user).  I also think that the workgroup ID also comes into play too.  Since you should have entered a different workgroup ID for each workgroup, I believe that is also a login consideration.  I'd have to research to verify though. I don't always remember correctly.  I studied that stuff years ago.  It's the over 50 syndrome. :-)

0
 

Author Comment

by:IEHP1
ID: 13937133
Rob,

I hate to ask but can you please verify, I really need a "I'm Sure" type of answer on this one.
0
 
LVL 17

Expert Comment

by:Arji
ID: 13937317
Here's a Microsoft article on User Level Security.  The CID is actually the Personal ID entered when creating a user.  Apparently Access uses all three to validate a user.  For your application, it is important to note that the .mdw file does not store permissions. It's only used to validate a user.  The permissions are within the MDW file.  So, for you, since you have different databases and different MDWs(created with different workgroup IDs), your users should not be able to sign into one workgroup and gain access to a database he/she does not have permission to.
I guess I remembered mostly well. :-)

Hope this helps.

http://support.microsoft.com/default.aspx?scid=%2Fsupport%2Faccess%2Fcontent%2Fsecfaq.asp#_Toc493299661
0
 
LVL 17

Expert Comment

by:Arji
ID: 13937435
Also found this on another site:

http://www.moretools.com/lessons/access_security.htm


"A Workgroup Information File (*.MDW) stores information to authenticate a user. It stores the user names, group names, and passwords. It does not store any permission or rights to any database. Its main purpose is to verify that a user is really who they say they are. The permissions of the database objects, tables, queries, forms, etc., are stored in each MDB file. The System.mdw is the default workgroup filename created when you install MS Access.
 
When you create a new User, you will be prompted for a User Name, Password, and a PID or Personal Identification number. A PID can be any text or numbers up to 20 characters long. All three values uniquely identify each user."
0
 
LVL 85
ID: 13937559
Access determines rights based on (a) the workgroup file you use to login and (b) the permissions you have set on the objects in the database. If you merely copied/pasted the .mdw file to make new files, then the "original" users in the mdw file are EXACTLY the same. Once a user logins in, the mdw file has no other use ... it's merely a "gateway" to open a properly secured database. If you use the EXACT same data to create a workgroup and use the EXACT same data to create a user, then that user will be identical between different workgroups. Note the EXACT ... they must be exactly the same, down to the PID used to create them.

The .mdw file stores Users, Groups, GroupMemberships, and Passwords. Permissions are stored in the database itself (i.e. the permissions "travel" with the database, not the mdw file) ... therefore, if a user logs in using WorkgroupA, WorkGroupA  , and in a properly secured database you should ... therefore, you can use Workgroup1.mdw to open

And 255 concurrent users on a Access database ... you need to package and sell this .. I've never heard of more than 150 ...
0
 
LVL 17

Accepted Solution

by:
Arji earned 2000 total points
ID: 13937726
LSMConsulting
That's why it's important to create workgroup files with different workgroup IDs and users with at LEAST different Personal IDs
0
 

Author Comment

by:IEHP1
ID: 13937750
Once it validates to user and allows them to open the database does it continue to use all three or does it use the CID only that is the issue that we are not sure of.  I'm being told that once your in the db Access only uses the CID to validate the permissions assigned so if you create the user account in multiple MDW files that they will share the same CID and that they can possible have different permissions then originally assigned to them.
0
 
LVL 17

Expert Comment

by:Arji
ID: 13938067
That's solved by using different MDWs created with different IDs and the users within them should have different Personal IDs.  From there, it won't matter if a user has the same username and password for any of the databases as long as they are members of different workgroups and their usernames/passwords were created with different PIDs witin those different workgroups.  The CID in the MDB is referenced by the combination of the username, password and personal ID.  That's how the MDB knows what user has permissions to what.....but as Scott(LSMConsulting) said you can't just make copies of one mdw and expect everything to be secure.  You must create different mdws so that the encoding is different.

workgroup1                                                 workgroup2
Username  Password   PID                             Username  Password   PID
Joebob       kinky         123456abcd                 JoeBob      kinky          9549484jhflsdkjf

Joebob is  joined to the workgroup1 and has access to database1

Even though JoeBob is a member of workgroup2, he cannot access database1 because his credentials are different.  This is why it is very import to store away several things as backup:

Usernames       Passwords     PID                    MemberOfWorkgroup    [Workgroup Owner Name]   WGOrganizatrion     WorkgroupID      Database
JoeBob             kinky            123456abcd          workgroup1                       Name you used              Department            ID you used       DB1
JoeBob             kinky            9549484jhflsdkjf    workgroup2                       Name you used              Department         diff ID you used    DB2  

That way if you have to re-create a workgroup file you'll be able to duplicate it.  Without this information you're hosed if you lose the MDW.


0
 
LVL 85
ID: 13938615
As Routinet explained, Access "builds" a user from combination of UserName and PID (NOT the password) and the WorkGroupID, and creates what's known as a Security ID (a SID) ... the workgroup file stores this information, along with group memberships and such, in several tables (the User information is encrypted and stored in the MSysAccounts table in the Workgroup file, for example). Therefore, a UNIQUE combination of UserName, PID, and WorkgroupID (generated when you originally build the workgroup) would guarantee a unique user.

Thus, if you simply copy MyWorkGroup.mdw to MyNewWorkgroup.mdw, you've done nothing more than change the name - the WorkGroupID is still the same, as is the UserName and PID for ALL Users stored in both workgroups. So what you have is 2 identical workgroup files with EXACTLY the same information but different names ...

As to permissions - the workgroup file does nothing more than (a) authenticate the user and (b) "tell" the database User and User-Group information ... the actual permissions are stored in the database itself (in the MSysACES table). Each time you request to open or otherwise manipulate an object, the Jet engine queries this table for the unique combination of (a) the User's SID and Object ID and/or (b) the GroupID and ObjectID combo of any groups to which this user belongs (recall that this information was written to the db upon successful login). So in theory, if you could open a properly secured database with multiple workgroup files (and if you can, you haven't secured your database properly) if those files were IDENTICAL and the User's SIDs was IDENTICAL, you would then be able to manipulate object permissions in the database using either workgroup file.

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question