Basing Object Permissions on Ownership

[Brian Smith]

I'm creating a database where I'd like to prevent users from making any
design changes, etc. to objects that I've created but at the same time I
want them to be able to create their own objects. One problem I'm trying to
overcome is how to handle situations where I want to be able to send them
new objects I've created without them inheriting all of the permissions I've
assigned to New Objects. Is there a way of assigning permissions to
individual objects based on ownership of the object through VBA or some
other method? Hopefully what I'm trying to do is clear. I should also point
out that the security is working on the objects I've created in the past so
that is not an issue.

I trust one of you gurus out there has dealt with such a situation and can
enlighten me on the best way to handle it.

Thanks.

Brian

 

[Joan Wild]

I usually provide a separate frontend mdb for users to create their own
objects in. Then it doesn't interfere with the production frontend.

 

[TC]

I always feel very uncomfortable with users being able to create their
own objects.

A nontrivial, properly normalized database will have data scattered all
over the place (from an end-user's perspective). How confident are you,
that the user can write a correct query to pull that data out
correctly?

For example, the data for an invoice might be stored in 5 or more
tables (invoice header, invoice line, customer, product, price history,
maybe more). There are not many customers on my side of the planet, who
could ever hope to join those tables correctly ...

My solution to the adhoc query problem, is this. I designed a flat file
structure (a bit like XML) for transferring SQL statements via email.
When the user wants a new adhoc query, they email me their
requirements. I create & test the query on my PC. Then I export the new
query to my XML-like format & send it to them via email, as a small
attachment. They download the attachment, then use my "add new adhoc
program" function to browse to & select that attachment. This loads the
new adhoc program into the permanent database, from which they can run
it, for ever afterwards, by selecting it from the list of adhoc
programs displayed.

Well - my customers /don't/ actually do any of those things - because I
haven't deployed this feature yet !! But it works fine, in my testing.
It will permanently fix the adhoc query/program problem, IMO.

HTH,
TC

 

[Brian Smith]

I always feel very uncomfortable with users being able to create their
own objects.

A nontrivial, properly normalized database will have data scattered all
over the place (from an end-user's perspective). How confident are you,
that the user can write a correct query to pull that data out
correctly?

I agree with your views completely. I'm not very confident at all that they
will be able to write queries correctly as I've been told they have very
limited knowledge of Access and, as you've pointed out, the data is
scattered all over the place. I'm essentially doing subcontract work on the
project and have been informed that the users would like to be able to
create their own queries and reports.

I like the general basis of your solution because it sounds like it would
keep everyone happy. I believe most users just want to be able to get the
correct data they need for whatever it is they are working on and don't
really want to know how things are working behind the scenes. If they did,
they'd more than likely be developing their own solutions.

Brian

 

[Brian Smith]

I usually provide a separate frontend mdb for users to create their own
objects in. Then it doesn't interfere with the production frontend.

Joan, if you don't mind, would you please explain a bit more exactly how you
implement this solution. Do you include anything special in this separate
frontend and do you do anything special to avoid confusion on the part of
users?

Thanks.

Brian

 

[Joan Wild]

Joan, if you don't mind, would you please explain a bit more exactly
how you implement this solution. Do you include anything special in
this separate frontend and do you do anything special to avoid
confusion on the part of users?

I provide a separate frontend, linked to the backend tables. This is given
only to a very few users, who know what they are doing (and only use it for
adhoq querying/reporting - not editting/adding/deleting. Just give them a
separate shortcut on the desktop, pointing to the adhoq mdb and the secure
mdw (so they still have to login)

That said, if you have users that aren't up-to-speed, you could create the
basic queries they need, and remove all permissions on the tables. Make
these queries RWOP queries, and consider removing insert/update/delete
permissions on the queries if you're concerned users will mess up.

They then would base any queries on the queries you provide, and can then
create reports based on these.

 

[TC]

Joan's idea is a good approach. Give them some basic, pre-written
queries to take some of the load away from them. For example, for an
Invoice system, you might give them an Invoice Header query which
joined invoice header & customer details, and an Invoice Line query
which joined invoice line, product, and price history. Then all they
have to do is to join the pre-written queries properly.

OTOH, if you are doing this on a contract basis, I would tend not to
argue the point, & just give them what they have asked for ...

Cheers,
TC

 

[Tom Stoddard]

I'm creating a database where I'd like to prevent users from making any
design changes, etc. to objects that I've created but at the same time I
want them to be able to create their own objects. One problem I'm trying to
overcome is how to handle situations where I want to be able to send them
new objects I've created without them inheriting all of the permissions I've
assigned to New Objects. Is there a way of assigning permissions to
individual objects based on ownership of the object through VBA or some
other method? Hopefully what I'm trying to do is clear. I should also point
out that the security is working on the objects I've created in the past so
that is not an issue.

I believe that a user can create their own objects regardless of what
permissions they have on new objects. If your user creates a new object then
they will be the owner of that object and they will be able to do whatever
they want to do with that object. As long as they don't have modify design
permissions on new objects then they won't be able to change the design of
objects which you create and add to the database (or objects created by
anyone else for that matter) but they will still be able to create their own
objects.

For example, if a user has read design permissions on new queries but does
not have modify design permissions then they would be able to open and view
a query which you created but they wouldn't be able to modify the design of
the query or to delete the query. They would, however, be able to create
their own queries and do whatever they want to those queries because they
would be the owner of those queries which would give them inherent
permissions to those queries.

I'm not suggesting that it's a good idea to let users create their own
objects but if you have no choice, this would do what you want.

 

[Brian Smith]

I believe that a user can create their own objects regardless of what
permissions they have on new objects. If your user creates a new object then
they will be the owner of that object and they will be able to do whatever
they want to do with that object. As long as they don't have modify design
permissions on new objects then they won't be able to change the design of
objects which you create and add to the database (or objects created by
anyone else for that matter) but they will still be able to create their own
objects.

After spending many hours testing out various permissions I discovered the
same thing. It would be nice if this was explained more clearly in the help
files. Maybe I'm an idiot but to me giving no permissions on <New Object>
would seem to imply that users in that group do not have the ability to
create objects of that type.

This is really the first time I've ever used Access's security model and
there are clearly a lot of things I don't fully understand. One of them is
why does the model not include the ability to stop users from creating
objects of a given type. It seems like you have to go to a lot of trouble to
prevent users from creating their own objects and this shouldn't be the
case. Or am I missing something in Microsoft's logic when they setup up the
security model?

For example, if a user has read design permissions on new queries but does
not have modify design permissions then they would be able to open and view
a query which you created but they wouldn't be able to modify the design of
the query or to delete the query. They would, however, be able to create
their own queries and do whatever they want to those queries because they
would be the owner of those queries which would give them inherent
permissions to those queries.

I'm not suggesting that it's a good idea to let users create their own
objects but if you have no choice, this would do what you want.

Based on other people's suggestions I think I've decided on a route to take
except for in one case. I'm going to start a new thread for that though so
it doesn't got lost in this one.

Brian

 

[TC]

It should be possible IMO to stop people creating new objects - but you
would have to do that by changing their permissions on the relevant
/container/ - not on the <New Object> thingy (or whatever it's called).

For example, creating a new table is actually just, creating a new
"document" in the Tables "container". If you did not have permission to
create new documents in that container, you would not be able to create
new tables.

You should be able to do all this through VBA. Check out:
- the Container and Document objects;
- the Tables Container (for example);
- the User and Permissions properties (as applied to Document objects).

HTH,
TC