Setting permissions for new tables

[Adam]

I am trying to secure an Access 2000 database through VBA code.

When I first secure the database, I set permissions for the various existing
tables, and everything seems to work just fine. The trouble is setting
permissions for new tables. I would like one user group to have permissions
to create and modify the design of new tables. I believe (and perhaps I am
wrong here) that the way to do that is to set permissions on the tables
container object with code such as the following:

Set Con = db.Containers("Tables")
Con.UserName = "SuperUsers"
Con.Permissions = dbSecFullAccess

Now, when I do this, a member of my SuperUsers group can indeed create a
table, and even modify its design, delete it, etc. The same member can even
set permissions on the new table for other users. However, when I try to set
permissions on the table through code, it doesn't work. The distressing
thing is I don't even get an error message: the code just stops running when
it gets to the bit when it trys to set the security on the new table.
Specifically, I set a Document object to the table with code such as:

Set Doc = db.Containers("Tables").Documents("NewTable")

and then try to set the security with the line

Doc.UserName = "Admins"
Doc.Permissions = dbSecNoAccss Or dbSecRetrieveData

The first of those lines doesn't run at all: the code just stops when it
gets there.

Does anyone have any idea what is happening here?

Many thanks

 

[Rick B]

Why are you doing all this through code? Access has very good built-in
security. If you go to User and Group Permissions, you can even tell Access
how to hanlde new reports. This is a handly little feature that basically
let's you set up 'default' security settings to be applied when you add new
objects to your database.


Rick B


I am trying to secure an Access 2000 database through VBA code.

When I first secure the database, I set permissions for the various existing
tables, and everything seems to work just fine. The trouble is setting
permissions for new tables. I would like one user group to have permissions
to create and modify the design of new tables. I believe (and perhaps I am
wrong here) that the way to do that is to set permissions on the tables
container object with code such as the following:

Set Con = db.Containers("Tables")
Con.UserName = "SuperUsers"
Con.Permissions = dbSecFullAccess

Now, when I do this, a member of my SuperUsers group can indeed create a
table, and even modify its design, delete it, etc. The same member can even
set permissions on the new table for other users. However, when I try to set
permissions on the table through code, it doesn't work. The distressing
thing is I don't even get an error message: the code just stops running when
it gets to the bit when it trys to set the security on the new table.
Specifically, I set a Document object to the table with code such as:

Set Doc = db.Containers("Tables").Documents("NewTable")

and then try to set the security with the line

Doc.UserName = "Admins"
Doc.Permissions = dbSecNoAccss Or dbSecRetrieveData

The first of those lines doesn't run at all: the code just stops when it
gets there.

Does anyone have any idea what is happening here?

Many thanks

 

[Adam]

Thanks, Rick.

There are good reasons why I'm doing it through code. The database is a
system for management of data from clinical trials. The original database
simply has all the necessary code and some tables and forms that allow the
user to set things up, but doesn't have anywhere to store the clinical data.
For each trial, a new version of the database is created (by exporting all
the database objects to a new empty database), and the user then sets up
tables and forms for the specific project by entering details of what the
study is measuring and then running the code I have written to create the
tables and forms. I don't want the user to have to manually set the security
for them all, which is why I'm doing all the security through code.

Having said all that, I think I've partially solved my problems. One problem
was with the way I was setting the db object. Bizarrely, using

Set db = CurrentDb

doesn't work, but using

Set db = DBEngine.Workspaces(0).Databases(0)

works just fine. Is this because the workspace needs to be specified
explicitly when dealing with a secured database?

There is one thing which is still puzzling me, however. Why do I need to do
any of this at all? My understanding was that setting security permissions
for the tables container object set default permissions for all new tables
created, but this doesn't seem to happen. The permissions that are initially
set on new tables are not the same as the permissions set for the tables
container object.

Any ideas?

 

[Joan Wild]

store the clinical data. For each trial, a new version of the
database is created (by exporting all the database objects to a new
empty database), and the user then sets up tables and forms for the
specific project

My understanding was that setting security
permissions for the tables container object set default permissions
for all new tables created, but this doesn't seem to happen. The
permissions that are initially set on new tables are not the same as
the permissions set for the tables container object.

The user owns the new database and tables. As owner they have full
permissions on the new tables, regardless of what you try to set.

 

[Adam]

Yes, that's true for the user who creates the new database, but shouldn't
other users have the permissions that I assigned to the table container?

Adam

 

[Joan Wild]

Not if they import the table - they'll own it.

Yes, that's true for the user who creates the new database, but
shouldn't other users have the permissions that I assigned to the
table container?

Adam

 

[Adam]

But they don't import the table. The user who creates the new tables is the
same one who creates the database in the first place. That user does indeed
have full permissions to everything. Other users have permissions that are
not the ones I set in the code.

Adam

 

[Joan Wild]

What permissions do the 'other users' have?

But they don't import the table. The user who creates the new tables
is the same one who creates the database in the first place. That
user does indeed have full permissions to everything. Other users
have permissions that are not the ones I set in the code.

Adam

 

[Adam]

The Admins and Users groups have permission to Read Design and Read Data.
None of the groups that I have set up myself has any permissions at all.

Adam

 

[Joan Wild]

If I understand you correctly, some user creates a new database and imports
necessary tables/forms via code and you set permissions via code. The
problem is setting the permissions for 'new tables/queries' via code.

In order to set permissions for that, the 'some user' would need to be a
member of the Admins Group - are they?

 

[Adam]

Yes.

If I understand you correctly, some user creates a new database and imports
necessary tables/forms via code and you set permissions via code. The
problem is setting the permissions for 'new tables/queries' via code.

In order to set permissions for that, the 'some user' would need to be a
member of the Admins Group - are they?