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.

 

[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?

 

[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.

 

[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