Security Problem

[pullingteeth]

My security was working fine on my MS Access DB. I had set up a workgroup file which administrators had to reference with a shortcut in order to gain admin access to the DB. Users double clicking on the DB directly (i.e. opening as user "Admin&quot found they had no permissions. Worked great.

Today any new DBs I create following the process of making a new admin user, removing Admin from admins, etc etc, are no longer secure. Any user can double click on them and will be Admin with full administrator rights.

This is bizarro and I'm pulling my teeth out over it. Any suggestions on what might have changed?

I have recreated my workgroup file a couple of times to make sure it isn't being weird. And I'm testing from other users' machines.

 

[DigiTomi]

Hello,

I solved security problems by hiding the database from the users. I work in NT environment.

What I have done is created a hidden shared directory (\db$), and gave full access to this directory to the administrators only. Within this directory I created a new, standrard directory, and put the database there (\db$\xyz\db.mdb). For the users, I gave them (for xyz only) "change" rights. This way they are not able to map the directory, but can access the database through a batch file which opens it together with the associated workgroup file.

This way I am sure that my users never open my database with direct doubleclick.

Regards

 

[RickSpr]

They may have full permissions because the Admin account is the owner of the database and all its objects. The owner of an object has irrevocable permissions on it. You need to be logged in as another user when you create them.

You can check the owner of an object in the User and Group Permissions dialog, on the Change Owner tab.

If that's the problem, log on with your administrator account, create a new database, and import everything into it. Then recreate your permissions setup. Replace the original database with the new one, and they shouldn't be able to get into it.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[pullingteeth]

Thanks DigiTomi - I believe we're on a windows 95 network, unfortunately. (The IT desk is moderately helpful.) But I will find out if that is possible.

And good thought RickSpr - this is where it gets weird. I tried exactly that on friday. The new database I created was owned my majoradmin, all objects were owned by majoradmin, and the user Admin was not in the admins group. As soon as I ditched the special workgroup file (using the default instead) and double clicked on it (thus pretending to be a normal user) the ownership of the database changed to Admin. Thus anyone double clicking gained full rights to it. How on earth can that happen?

 

[RickSpr]

I know of one way it could happen--but it's really, really a stretch.

If the Admin user in the default workgroup and the majoradmin user in the custom workgroup happened somehow to have the same Security ID (SID), it would look exactly this way. Like permissions, I believe ownership is identified with a SID rather than with an actual account name. Whatever account has a matching SID in the active workgroup will be displayed as the owner. (If no account matches, because the owner account is not defined in the active workgroup, the owner is shown as "(unknown)".)

But the chances of them having the same SID are astronomically against. Unless, that is, you created your custom workgroup by copying the default workgroup, opening it as an Access database, and then editing the MSysAccounts table (which you shouldn't have permission to do) to paste a duplicate account into it. Or something like that. You didn't take any tricky shortcuts did you?

The other thing that occurs to me is that maybe somebody used a password cracker on your default workgroup file. I don't know that any such cracker exists, but theoretically one could set the Admin account's SID to the same as the Engine account (the ultimate creator of all databases) and have full, irrevocable permissions on everything.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[pullingteeth]

I don't believe I took shortcuts. Here's the process I took:

1) Created new DB file, having logged in as MajorAdmin.
2) Checked ownership of DB (MajorAdmin).
3) Changed account Admin to no longer be in the Admins group.
4) Imported data & from previously secured DB.

The "previously secured DB" process was:

1) Create new workgroup file.
2) Create new DB.
3) Create new user, MajorAdmin.
4) Put MajorAdmin in the Admins group.
5) Close DB, reopen as MajorAdmin. **
6) Remove Admin from the Admins group.
7) Go through user permissions to ensure that MajorAdmin is the only one with Administer rights. I believe also at this point MajorAdmin has ownership of the DB. I don't remember whether I check that, though.
8) Create tables & forms.

As I said, this was solid until about a week ago. About a week ago, I started replicating. Also, about a week ago, IT messed around with the shared folder permissions. But I can't see why either of these would have altered Access' internal user permissions.

(Oh for a MySQL/tomcat/apache setup.)

** Thought occurring as I type this: to do this step, I had to give Admin a password (so as to bring up the password prompt). Later, I decided that I didn't want average users to have to worry about a password (since Admin was given basic read-only rights) so I cleared the password for Admin. Does this mean that Access doesn't remember the permissions for Admin unless there is also a password specified for Admin?

 

[psemianonymous]

There is one obvious answer that you haven't explicitly mentioned: remove permissions from the Users group and the Admin user. The users group and the admin user are the same PID (or whatever you want to call it) no matter what workgroup you have created, thus you have to remove all permissions for them.

Folder/network security doesn't have anything to do with your users being able to osmose through the Access/JET security setup, but the abovementioned trick is neat, disallowing them to browse out to the folder. Neat.


As for the Admin password, if it is not set, the default permissions for the Admin user are used. What I mean is that security is still active even if you aren't required to enter a password. The only thing that is different is that you can't log in as anyone *except* the Admin user unless you set the password again.


If you're ever in doubt as to what you are logged in as, hit CTRL-G and type in

?CurrentUser()

and it will either say "Admin" or "MyPowerAdminUserEtc" or something. You get the idea.


Pete

 

[pullingteeth]

<I>As for the Admin password, if it is not set, the default permissions for the Admin user are used. What I mean is that security is still active even if you aren't required to enter a password. The only thing that is different is that you can't log in as anyone *except* the Admin user unless you set the password again.</I>

Actually you can if you open it with explicit reference to the &quot;key&quot; workgroup file and to the user. E.g. if you create a shortcut pointing to:

&quot;C:\Apps32\Microsoft Office\Office\MSACCESS.EXE&quot; /wrkgrp \\...\data\key.mdw /user majoradmin \\...\data\db1.mdb

That way anyone directly double clicking on it gets (theoretically) the low-permission Admin account; anyone opening with the shortcut is required to login as a specified higher permissioned user.

 

[RickSpr]

I see a problem (maybe not the problem). In step 7 you said you believe MajorAdmin owns the database, but if your list of steps is accurate it's really Admin. Whoever you are logged on as when you create the database is the owner of the database. (MajorAdmin would be the owner of the objects within the database, however.)

I'm a bit confused by your process. You remove Admin from the Admins group in step 6 of the second process. Later, you do it again in step 3 of the first process. How did Admin get back in the Admins group?

In step 1 of the second process you create a workgroup file. You remembered to use a unique Workgroup ID, I trust?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[pullingteeth]

RickSpr: I'm a bit confused by your process. You remove Admin from the Admins group in step 6 of the second process. Later, you do it again in step 3 of the first process. How did Admin get back in the Admins group?


Yeah, you'd figure, huh. SOLUTION/RESOLVED at bottom, btw. Thanks for the help, everyone.

OK, first, I realized my description above was misleading. I did want Admin to be able to modify data, but not table designs, and not any DB permissions. This (as follows) is how I set it up. Perhaps you can follow the steps and tell me whether you get the same result, because it seems counterintuitive.

1) I create a new workgroup file (run wrkgadm, create desktop/key.mdw, id is 209180928, join desktop/key.mdw)
2) I create a new access db (right click, new access, save as desktop/db1.mdb)
3) Create new user, MajorAdmin (tools, security, user & group accounts, new, major admin, id is 2029222)
4) Put MajorAdmin in Admins group (select Admins, Add>>)
5) a) Give Admin a password (change logon password, new password = foo, confirm = foo)
b) Close DB
c) Open DB by double clicking on it
d) Username is majoradmin, password is blank
e) Change MajorAdmin password (as in a, password is foo2)
6) Remove Admin from Admins group (tools, security, user & group accounts, Name = Admin, Select Admins, <<Remove)
7) Now check DB ownership (tools, security, user & group permissions, change owner, database) == Admin
8) Now without closing Access, create new Access file (File, New, desktop/db2.mdb)
9) Check DB ownership as in 7 == MajorAdmin
10) Create quick table with some junk data
11) Now remove all admin permissions from group Users (tools, security, user & group permissions, groups, Users, database (uncheck all except open/run), tables (uncheck all except read/insert/update/delete/) ...)****
(****If you remove all permissions, Admin is locked out whatever. But if you only remove these, then the problem below occurs)
12) Close DB
13) Delete DB1.mdb

Now pretend you are a user without the special workgroup file.
a) Run wrkgadm
b) Create
c) windows/system/system.mdw (just to ensure it is default)
d) join system.mdw
Now double click db2
Check DB owner -- It says &quot;admin&quot; on mine. This was what was confusing me.

---------
So the solution was to remove ALL permissions from Admin/Users, and to create a new user/group (james, user2), and to require ALL users to use the key.mdw file created to open the DB. (Perhaps specifying the low permission username in the shortcut).

 

[RickSpr]

The fact that Admin shows up as the owner of the database and, I presume, the table explains everything you've described. The owner has irrevocable permissions to open the database and to use and administer objects.

Your solution is actually a workaround. It should be working the way you intended, and the way you say it used to.

I don't have Access 2002 installed, so I followed your instructions using Access 2000.

I had to make one slight adjustment. I don't have &quot;Microsoft Access Database&quot; on my New shortcut menu, so I used the &quot;New Office Document&quot; shortcut from the Office Shortcut Bar to create db1.mdb. I feel sure that wouldn't make any difference.

Otherwise, I followed every step carefully. When I got to the end, the owner of db2.mdb came out <Unknown>, which is what I would expect. The real owner, MajorAdmin, isn't defined in system.mdw.

I have three conjectures:
1. As I suggested earlier, the algorithm that generates user account SIDs may happen to be generating the same SID for MajorAdmin/PID=2029222 as the predefined SID for Admin. The SID is what Jet considers to be the unique user ID, so two accounts with the same SID are indistinguishable to Jet. This is extremely unlikely, but you could test it by incrementing the PID for MajorAdmin and repeating the process. Alternatively, you could test it by joining key.mdw and checking the owner. If it shows up as MajorAdmin, that strongly supports this theory. BTW, you can also open the workgroup files as databases, open the MSysAccounts tables, and compare the SIDs. Some of the characters are not displayable, so you can't necessarily tell whether they're the same, but if they appear different then they certainly are different.
2. There may be a bug in Access 2002, perhaps activated by your specific sequence of steps, which causes it to assign the wrong SID as the owner of the database somehow. I'd be interested in finding out whether the junk table was also owned by Admin, under the system.mdw workgroup. Also whether, when you return to the key.mdw workgroup, the owner remains Admin or reverts to MajorAdmin.
3. There may be a bug in Jet that causes it to fetch the wrong SID when retrieving the database owner. Perhaps the bug is conditioned on the actual owner not being present in the workgroup file and/or the Admin account having no password. It would be interesting to test whether giving Admin a password makes any difference.

Although the first hypothesis is really unlikely, a search of the Knowledge Base turned up nothing to support the second and third hypotheses. If it is, in fact, a bug, Microsoft apparently doesn't know about it.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[pullingteeth]

Actually it is with Access 97. I'll test your conditions as best as I can when I get a chance and report back here.

Thanks

 

[RickSpr]

Oops! I must have gotten confused with the Access version because of another thread. I'll repeat the process with Access 97 myself.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

Ok, I repeated with Access 97 and the owner of db2.mdb came up as <Unknown> under the system.mdw workgroup.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Kanga35]

May I suggest too to change the name of the security file from system.mdw to something else to match your DB and in a different directory than Access, so Access reinstallation or repair does not delete or overwrite it. Use Access workgroup admin or a shortcut to hook to it when opening your DB.