Owner Access Option in DAO or ADO

In SQL, I can run an action query on a dataset "WITH OWNERACCESS OPTION" to get around security limitations on a user (skip example if you know what i'm talking about).

Example:
User belongs to Sales group... sales group does NOT have DELETE permissions on tblInvoices. The user cannot, therefore, delete any invoices. However, if I create a delete button and attach a query to it, tagging the "WITH OWNERACCESS OPTION" onto the query, this user can now delete a record.

If I have a need to use "RunCommand acCmdDeleteRecord" instead of a query (i have my reasons - not worth going into right now), how can I still get around the security limitation. Is there an equivelent "...OPTION" in the DAO/ADO 'world'?

ic
icAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JimMorganCommented:
You can use 'With OwnerAccess Option' with SQL Select statements but not with any other types of SQL code queries.

To do anything else you need to change the permissions on the fly for the user to do this without using a query.  If you forget to change the premissions back, you could get into trouble.

There is an out, which the same one we are using.  We just lock out all tables for any users.  Then we create table queries which so nothing more than select all the fields from each table.  Then a 'RunCommand acCmdDeleteRecord' based on the select record of one of these table queries, will work as the user has the owner access options for the query.

A user can not make their own queries on the tables but they can make a query based on the table query.  So if the user has no way to get to the database window to create their own query and no way to create code modules, you are protected.

Jim
0
icAuthor Commented:
How do I change permissions "on the fly" for a given user?

ic
0
icAuthor Commented:
Let's say my code looks like:

 If [criterion = true] then
     msgbox "You have already entered this record"
     RunCommand acCmdDeleteRecord
 End If

I get an error because the user cannot delete in the current table being accessed.

I'm looking for something to the effect of:

     RunCommand acCmdDeleteRecord, With OwnerAccess Option

or:

     CurrentUser.Permissions = Owner
     RunCommand acCmdDeleteRecord
     [set permissions back to normal]

These are hypothetical and will not work in VBA, of course, but is there a syntax would go something like this? This is the HOW TO? I am asking for. (Thanks for your help so far (!) by the way).

IC
0
JimMorganCommented:
I was afraid that if I brought changing the user permissions on the fly that you would want to know how.  I don't have the information at hand today but I would recommend using the table queries for the recordsets.  This will allow you to do the RunCommand acCmdDeleteRecord without resetting the permissions.

There is a bit more to it than adding one line of code.  You will have to write a code module which verifies who the user is, the permissions that they have, store away the current permissions, make the change.  Then another code module which verifies the same thing but retries the old permissions ans sets them back.

If each user has their own front end, it will be easier to do this.  If they share a front end, I don't even want to think about the situation.

The most difficult thing to think about is what if they permissions were changed and the program aborted or the system turned off before the permissions were changed back?

Jim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
icAuthor Commented:
Ah, okay, so there's some considerable work involved. Perhaps it's not the way. Just as an FYI, I have some code that loops through and tells the user if s/he has entered the item twice (invoice subform) - I'm not sure why I don't have a "No Duplicates" setting in the invoice details table; I thought there was a reason, but I can't remember anymore, maybe I'll just go with that instead.

Now that I'm also hip to using temp tables, I can grant necessary permissions on the temp tables. Been good chattin' with ya.

ic
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.