Trying to program workgroup security using DAO... Help!!

[Michael Scott]

I am at my wits' end trying to secure a database using DAO rather than
the built-in wizard. Please... someone tell me what I'm doing wrong!

Here are the steps I've taken to secure a target database (which is
currently completely unsecured).

A. Creation of new workgroup file.

1. Created a new workgroup file.
2. Created a new administration user and added it to the "Admins"
group.
3. Created all the necessary users and added them all to the "Users"
group.
4. Created a number of new groups with specific business roles -
DataInput, Manager, ReadOnly, Audit etc.
5. Added each of the new users to the Users collection of one or more
of the new groups, as necessary.
6. Removed the existing "Admin" user from the "Admins" group and
assigned it a password.

B. Assigning permissions.

1. Assigned permission dbSecDBOpen on the database itself (MSysDB) for
each of the new groups I created.
2. Assigned all specific permissions to objects for the new groups I
have created (I am assigning allpermissions to groups, not users)
3. Removed all permissions assigned to user "Admin" and group "Users".
I am doing this by looping through every document in every container,
setting its Owner property (first to "Admin" and then to "Users") and
then setting its Permissions to dbSecNoAccess.
4. Changed the owner of every object in the database by looping as
above and setting the Owner property of each document to the new
administration user I have created.
5. Belt and braces - set the Permission on MSysDb to dbSecNoAccess for
both group "Users" and user "Admin".

When I use the workgroup I have created everything works absolutely as
I think it should, with every user having precisely the permissions I
gave them

The problem is that when I rejoin the system.mdw workgroup I can get
straight into this secured database and have full access to
everything, without being prompted for an ID or password. It seems
that user Admin's permissions have not been revoked.

I'm developing in Access 2003 under XP. Can anyone tell me where I'm
going wrong? Have I missed a step or completely misunderstood
something?

Many thanks.

Michael

 

[Joan Wild]

Why are you using DAO? Not using the wizard is fine, but you could use the security menu items.

Anyway, I believe you missed a step between 1 and 2. After you created a new workgroup file, you then created a new user to be a member of Admins.

However, when you created the new user, you were still *using* system.mdw. You needed to exit Access, and restart it using the newly created mdw file.

Usually when you create a new mdw, it makes it the default, but you need to start another session of Access for that to kick in.

What you can do is confirm the mdw *in use* by hitting Ctrl-G and typing
?DBEngine.SystemDB

 

[Michael Scott]

Why are you using DAO? Not using the wizard is fine, but you could use the security menu items.

Anyway, I believe you missed a step between 1 and 2. After you created a new workgroup file, you then created a new user to be a member of Admins.

However, when you created the new user, you were still *using* system.mdw. You needed to exit Access, and restart it using the newly created mdw file.

Usually when you create a new mdw, it makes it the default, but you need to start another session of Access for that to kick in.

What you can do is confirm the mdw *in use* by hitting Ctrl-G and typing
?DBEngine.SystemDB

Thanks, Joan.

I'm using DAO because this is an automated procedure within a larger
application - we will be offering the user a different interface to
the workgroup security (because many users, our own included, find the
Microsoft interface confusing) and will be installing this application
on remote sites. I want the user to select the permissions they want
to apply to an unsecured database, click a button and hey presto. One
locked-down database.

I forgot to say that after creating the workgroup file in step 1 I
then create a new PrivDBEngine, set its SystemDb to the new workgroup,
create a workspace and from then on work within that workspace:

....
Application.CreateNewWorkgroupFile Path:= strWorkgroupPath,
Replace:=True
Set dbe = New PrivDBEngine
dbe.SystemDb = strWorkgroupPath
set wsp = dbe.CreateWorkspace("", "Admin", "", dbUseJet)
[Then create new users, groups and assign users to groups]
....

After much testing and hair-pulling I think the problem is that the
original, unsecured database was created under system.mdw and is thus
owned by Admin. I don't think I can change the owner of a database,
can I? I think I might have to create a new database (using DAO, in
the new workgroup), copy all objects from the unsecured database to
this new database and *then* set the permissions. The copy then
becomes the secured database and the original database becomes the
unsecured backup.

Looks like I'm going to be spending the weekend writing some code that
will create a new database and copy everything from the unsecured
target database to the database that has just been created... unless
you can tell me an easier way?

Thanks for your time.

 

[Joan Wild]

I forgot to say that after creating the workgroup file in step 1 I
then create a new PrivDBEngine, set its SystemDb to the new workgroup,
create a workspace and from then on work within that workspace:
...
Application.CreateNewWorkgroupFile Path:= strWorkgroupPath,
Replace:=True
Set dbe = New PrivDBEngine
dbe.SystemDb = strWorkgroupPath
set wsp = dbe.CreateWorkspace("", "Admin", "", dbUseJet)
[Then create new users, groups and assign users to groups]

Yeah but, you are logging in as 'Admin' to this new workspace - Admin is common to all mdw files. So you don't want to use that user.

After much testing and hair-pulling I think the problem is that the
original, unsecured database was created under system.mdw and is thus
owned by Admin.
Exactly.

I don't think I can change the owner of a database,
can I? I think I might have to create a new database (using DAO, in
the new workgroup), copy all objects from the unsecured database to
this new database and *then* set the permissions. The copy then
becomes the secured database and the original database becomes the
unsecured backup.

Yes that's right.

 

[Michael Scott]

Thanks again, Joan.

Just one little question.

Yeah but, you are logging in as 'Admin' to this new workspace - Admin is common to all mdw files. So you don't want to use that user.

But I have only just created that new workgroup, and therefore there
are no other users I can use, are there? And Admin is the only member
of the Admins group.

Which user do you think I should be using?

 

[Joan Wild]

Sorry, you're right, you need to create the user first. But then use the new user to create the new database, etc.

 

[Michael Scott]

Sorry, you're right, you need to create the user first. But then use the new user to create the new database, etc.

That's what I plan to do. Once I've created the new administrator user
I'll use that to create the new database and set permissions.

Thanks for your help.