Changing all Queries to RWOP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James Stephens - 26 Jan 2006 18:12 GMT

I am working on implementing security into a database that is going to go
from a single user to a shared system and I going through the process of
setting up the security correctly (using the FAQ as my quide). From what I
have read about securing the tables properly I need to set all of my queries
run permissions to Owners instead of Users.

Q1. Is there a way to do this with code (I have about 900 queries) and I
would like to find a faster way than manually opening each query and changing
it.

Q2. This may be more appropriate for the security section of the group, but
for queries that run from command buttons where the query is an SQL statement
in the VB code, is there a way to make this run with owners permissions or do
I need to make all of these queries into stored objects.

Thanks for any help and advice on this

Jim

Reply to this Message

Tim Ferguson - 26 Jan 2006 19:17 GMT

"=?Utf-8?B?SmFtZXMgU3RlcGhlbnM=?="
<JamesStephens@discussions.microsoft.com> wrote in
news:9D9DC64B-8B8A-477F-9A9E-99CE2D6D61AE@microsoft.com:

> Q1. Is there a way to do this with code (I have about 900 queries) and
> I would like to find a faster way than manually opening each query and
> changing it.

for each qdf in Querydefs
' shave off the final semicolon
temp = Left(qdf.SQL, Len(qdf.SQL)-1)

' add the RWOP clause
temp = temp & vbNewline & "WITH OWNERACCESS OPTION;"

' save it back
qdf.SQL = temp

next qdf

> Q2. This may be more appropriate for the security section of the
> group, but for queries that run from command buttons where the query
> is an SQL statement in the VB code, is there a way to make this run
> with owners permissions or do I need to make all of these queries into
> stored objects.

No: if the query is created in VBA code, its owner will be the user, not
you. What you can do is change the inline query to reference your RWOP
queries instead.

Hope it helps

Tim F

Reply to this Message

James Stephens - 26 Jan 2006 19:50 GMT

Thanks for the info. On the first part, when I try to run the code I get an
"error 13 type mismatch". Is there something else I need to do or any
references I need to set.

Thanks again

Jim

> "=?Utf-8?B?SmFtZXMgU3RlcGhlbnM=?="
> <JamesStephens@discussions.microsoft.com> wrote in
[quoted text clipped - 29 lines]
>
> Tim F

Reply to this Message

Douglas J. Steele - 26 Jan 2006 23:25 GMT

How have you declared qdf (and temp, for that matter)?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Thanks for the info. On the first part, when I try to run the code I get
> an
[quoted text clipped - 38 lines]
>>
>> Tim F

Reply to this Message

Brendan Reynolds - 27 Jan 2006 12:13 GMT

You might be interested in this link to a previous discussion on the subject
in the Google newsgroup archives ...

http://groups.google.com/group/comp.databases.ms-access/browse_thread/thread/767
92b94e2d7fa56/81d8d55992e1add4#81d8d55992e1add4

Signature

Brendan Reynolds
Access MVP

> Thanks for the info. On the first part, when I try to run the code I get
> an
[quoted text clipped - 38 lines]
>>
>> Tim F

Reply to this Message

Tim Ferguson - 27 Jan 2006 17:28 GMT

> You might be interested in this link to a previous discussion on the
> subject in the Google newsgroup archives ...

Useful link; thanks for that.

Tim F

Reply to this Message