New Table Permissions

[Andrea]

I have secured a database and have a group that has no permissions to create
a new table. The first individual user of this group also has no permissions
to create a new table. When I sign on as that first individual user, I can
create a new table and add data to it. What am I missing? I've checked that
I am using the right workgroup file and have checked the permissions.

 

[Chris O'C via AccessMonster.com]

Did you run the vba code listed in the security faq to prevent users from
creating new tables and queries? You have to do it for both the default
admin user and the users group. Run the code when logged in as the db owner.

Public Function revokePermOnNewObj()
Call noNewObjects("Admin")
Call noNewObjects("Users")
End Function

Public Function noNewObjects(strUser As String)
Dim db As Database
Dim con As Container

Set db = CurrentDb
Set con = db.Containers("Tables")
con.UserName = strUser
con.Permissions = con.Permissions And Not DB_SEC_CREATE

Set con = Nothing
Set db = Nothing
End Function


Chris
Microsoft MVP

 

[Joan Wild]

You likely think that the permissions you set for <New Tables/Queries>
affect whether a user can create a new table/query. However that's not the
case. Those permissions are just those the user will have on any newly
created table/query.

A user who creates a table, owns it and therefore will have full permissions
on the table. For a query, only if they have permissions on the underlying
table will they be able to create the table. You need to use the code Chris
posted to prevent users from creating tables.

 

[Andrea]

Thank you. That clarifies the permissions for me. I wish there was a little
more written about the interaction of various permssions. I've read the
white paper and the MS Security FAQ, and probably about a half dozen other
chapters or web pages on security, but find relatively little specifics about
permissions. But your reply definitely helps. Thanks.

 

[Andrea]

I hope I'm not posting this twice. But here goes. I did see the code you
post in some of the documents I've read, but didn't think I needed it. I'd
like to clarify it a little. If I have two groups, A and B, and GroupA will
not be able to create new tables and GroupB will be able to, then I would
call the function passing GroupA, but leave GroupB as is?

 

[Chris O'C via AccessMonster.com]

Passing GroupA to that function won't do you *any* good. Here's why. Once
you revoke the table and query creation permissions with the code I mentioned,
only members of the Admins group will be able to create new tables and
queries. So revoking GroupA in addition is redundant, because members of
GroupA are also members of the Users group - which can't create tables and
queries.

But say you ignored the advice to revoke the default admin user and the Users
group and only revoked the table and query permissions of GroupA. Would that
work? No. Everyone in GroupA is also a member of the Users group, which
doesn't have their permissions revoked. So everyone in GroupA would still be
able to create new tables and queries by virtue of membership in the Users
group.

If there's a group of db users you don't want to create tables and queries,
you *have* to revoke these permissions from both the default admin user and
the Users group. Doesn't matter how many other custom groups there are in
your workgroup because they get revoked by virtue of their membership in the
Users group.

Chris
Microsoft MVP

 

[Andrea]

I'm sorry to be so dense, but what if there is a group I DO want to create
tables and queries? If I revoke these permissions from admin and Users, and
since the other group is a member of Users, won't I revoke table creation
permission from all groups? At this point it seems to me I'd be better off
not revoking table creation permission from any groups.

 

[Chris O'C via AccessMonster.com]

If you revoke these permissions from the admin user and the Users group, the
only ones with these permissions will be the Admins group. If you want
certain users to build tables and queries, you have to decide whether they
belong in the Admins group or not. If not, you'll have to give that group
permission to create tables and queries.

Chris
Microsoft MVP

 

[Joan Wild]

I would suggest that you give users a second frontend for the creation of
adhoq queries; that way you don't have to upset the production database.

 

[Andrea]

Ok, I think I've got it. Thank you for your patience. I think Joan Wild's
suggestion below is also a good idea, but may not be something I can
implement right away. I appreciate your knowledge and willingness to share.

Andrea

 

[Andrea]

The more I learn about permissions and how they really work, the more this
makes sense to me. I'd seen this suggestion a number of times, and now it is
making more and more sense. Not sure I can implement it right away, but then
again, maybe it wouldn't be that difficult. Thanks for your expertise and
for you great suggestions. I really appreciate them.

 

[Graham Wideman [Visio MVP]]

Hi Andrea,

You might also find this article and tool helpful:

http://grahamwideman.com/gw/tech/access/accesssec/index.htm

Graham