Query Security

[RI]

All my queries are RWOP and I have granted read data
permission to the distribution workgroup. The problem is
that the forms based on these queries are now "not
updateable". When I grant "update" and other permissions
the forms work fine, however a person with MS Access can
use the distibution workgroup to edit the contents of the
queries and this is not desireable. What do I need to do?

thank you

 

[Joan Wild]

All my queries are RWOP and I have granted read data
permission to the distribution workgroup. The problem is
that the forms based on these queries are now "not
updateable". When I grant "update" and other permissions
the forms work fine, however a person with MS Access can
use the distibution workgroup to edit the contents of the
queries and this is not desireable. What do I need to do?

And yet it's desireable for them to update via a form? Either you want
users to be able to update the data or not. Could you elaborate some more?

 

[Guest]

-----Original Message-----
do?

And yet it's desireable for them to update via a form? Either you want
users to be able to update the data or not. Could you elaborate some more?

Thanks For Helping------------ I don't want anyone to
update via the queries themselves. For example ,In a query
that counts the number of times a demo copy opens and then
limits people to opening the database a certain number of
times, I can't have people resetting the counter. The
counter would not be visible by form, but if people were
able to change data directly in the query they would
bypass the security.

I have secured my tables and access them with RWOP
queries. I should only have to give "read data" permision
to the runtime workgroup in order to use forms with these
queries. It is true that when I do this---1.The forms
based on these queries open.--- and 2. The queries are not
updateable when then are opened directly(which is good).
The problem is that the queries and forms based on
them become unupdateable. If I add "update,add,delete
data" to the runtime workgroup permissions, then---1. The
forms work wee and are updateable but the runtime
workgroup has the ability to chang data when they open the
queries directly. What is the fix and why are there no
permission settings for modules. thank you

 

[TC]

You can easily make a single field in a query non-updatable, by making that
field an expression. For example, in the following queries, the fields with
alias Blah will never be updatable:

SELECT ..., MyTextField & "" AS [Blah], ...
SELECT ..., MyNumberField + 0 AS [Blah], ...
SELECT ..., MyYesNoField OR FALSE AS [Blah], ...
etc.

Does that help?
TC

 

[Guest]

-----Original Message-----
You can easily make a single field in a query non- updatable, by making that
field an expression. For example, in the following queries, the fields with
alias Blah will never be updatable:

SELECT ..., MyTextField & "" AS [Blah], ...
SELECT ..., MyNumberField + 0 AS [Blah], ...
SELECT ..., MyYesNoField OR FALSE AS [Blah], ...
etc.

Does that help?
TC

I appreciate your help but that is not the fix. RWOP
queries are supposed to enable the runtime workgroup to
use tables and queries without giving the runtime
workgroup other permissions except - read data. If you
gave them more then read data permissions they would be
able to open your queries directly and corrupt your data.
The queries should not have explicit permission for the
runtime workgroup to update them but because they are
running with the owners permissions they should be able to
do so

 

[Joan Wild]

I appreciate your help but that is not the fix. RWOP
queries are supposed to enable the runtime workgroup to
use tables and queries without giving the runtime
workgroup other permissions except - read data.

That isn't true. RWOP queries still require the necessary permissions on
the *query*. Absolutely no permissions are needed on the tables, not even
read data. You misunderstand how RWOP works.

If you
gave them more then read data permissions they would be
able to open your queries directly and corrupt your data.

How are users getting to the database window to even open the queries? You
can do alot to lock down these settings - hide the db window, create your
own menubars/toolbars that restrict the user. Look at the settings in
Tools, Startup.

Bottom line is that you cannot completely lock out users. Access security
is good for keeping the idle curious out, but determined users can bypass
it.

 

[RI]

-----Original Message-----


That isn't true. RWOP queries still require the necessary permissions on
the *query*. Absolutely no permissions are needed on the tables, not even
read data. You misunderstand how RWOP works.
data.

How are users getting to the database window to even open the queries? You
can do alot to lock down these settings - hide the db window, create your
own menubars/toolbars that restrict the user. Look at the settings in
Tools, Startup.

Bottom line is that you cannot completely lock out users. Access security
is good for keeping the idle curious out, but determined users can bypass
it.


--
Joan Wild
Microsoft Access MVP


.

I appreciate all the help and I think I came up some
thoughts. As far as modules, you can lock users out by
using a password. The only fix that I can think of for the
queries is to give them only read data, and
programatically change the user permissions to
update,etc.... when they open a form. This way they will
have full permissions when the work from the form, but if
they open the queries directly, the will only have
restricted permissions. Do you know of a way to do this?

 

[Pieter Wijnen]

You could also dynamically create the queries on opening of the form & Add
With OWNER permissions etc
The best way as suggested is to disallow users the DB window ie disable
special keys and/or create a autokeys macro
to alter the behaviour of F11 + create your own toolbars

Pieter

 

[RI]

Thanks but to create queries i would have to give the
users workgroup permissions to the underlying tables. I
have used allowbypasskey but i am looking for some direct
query security

 

[Rick Brandt]

You could also dynamically create the queries on opening of the form & Add
With OWNER permissions etc
The best way as suggested is to disallow users the DB window ie disable
special keys and/or create a autokeys macro
to alter the behaviour of F11 + create your own toolbars

If a person with limited permissions creates a query via code then that query
will have the same limited permissions because the user executing the code will
be the owner.

 

[TC]

WITH OWNER ACCESS OPTION is only relevant when one user (say 'A') runs a
query that is owned by some >other< user 'B'. But, when a user runs code
that creates a query, that query is owned by the user who ran that code. So,
WITH OWNER ACCESS OPTION is irrelevant (and has no affect) for queries that
are created programatically.

HTH,
TC

 

[TC]

WITH OWNER ACCESS OPTION is only relevant when one user (say 'A') runs a
query that is owned by some >other< user 'B'. But, when a user runs code
that creates a query, that query is owned by the user who ran that code.

So, WITH OWNER ACCESS OPTION is irrelevant (and has no affect) for queries
that are created programatically.

Unless, of course, a high-permission user runs the code that creates the
query, then low-permission users are allowed to log-in and run that query
afterwards. But that is not what you're suggesting here - >each user<
dynamically creating, then deleting the query.

HTH,
TC

(snip)

 

[Jeff Conrad]

WITH OWNER ACCESS OPTION is only relevant when one user

Unless, of course, a high-permission user runs the code
that creates the query, then low-permission users are
allowed to log-in and run that query afterwards. But that
is not what you're suggesting here - >each user<
dynamically creating, then deleting the query.

TC,

Pure curiosity here (serious):

What would happen if you created a temporary workspace of
a "high-permissions" user, created the query in code with
the Owner Access Option while in that workspace, saved the
query, and then closed the temporary workspace? Would
any "low-level" user be able to run this query? Would that
query show in the Permissions area to be owned by
the "high-level" user?

Thanks,

 

[TC]

TC,

Pure curiosity here (serious):

What would happen if you created a temporary workspace of
a "high-permissions" user, created the query in code with
the Owner Access Option while in that workspace, saved the
query, and then closed the temporary workspace? Would
any "low-level" user be able to run this query? Would that
query show in the Permissions area to be owned by
the "high-level" user?

Hi Jeff

That is a very interesting question. I don't know the answer off the top of
my head. I do remember trying to programatically create a new database that
was owned by a user other-than the user running the code in question. I
don't remember the details of how I went about this - but it would cerainly
have involved creating a temporary workspace for the "other" user. I think
that I got mixed results: some of the objects in the new database were owned
by the expected "other" user, but some of them weren't.

The question really is, if you create a query off a workspace established
for some other user, is the new query owned by that other user, or is it
owned by the user actually running the code? The ownership of the query
would be established at the time that Jet created it. There is no way (IMO)
that closing the temporary workspace would change the query ownership.

Jeff, I'll definitely follow this up & post back to this thread in due
course. However, that might not be for a few days, because I just delivered
my only working PC to a local firm called "Doctor PC", for emergency
surgery! (I'm posting from an internet cafe PC that doesn't have Access.)

Cheers,
TC
(off for the day)

 

[Jeff Conrad]

Hi Jeff

That is a very interesting question. I don't know the answer off the top of
my head. I do remember trying to programatically create a new database that
was owned by a user other-than the user running the code in question. I
don't remember the details of how I went about this - but it would cerainly
have involved creating a temporary workspace for the "other" user. I think
that I got mixed results: some of the objects in the new database were owned
by the expected "other" user, but some of them weren't.

The question really is, if you create a query off a workspace established
for some other user, is the new query owned by that other user, or is it
owned by the user actually running the code? The ownership of the query
would be established at the time that Jet created it. There is no way (IMO)
that closing the temporary workspace would change the query ownership.

You've understood my question perfectly.
Would it be owned by the person running the code
or by the temporary workspace "user"?

Jeff, I'll definitely follow this up & post back to this thread in due
course. However, that might not be for a few days, because I just delivered
my only working PC to a local firm called "Doctor PC", for emergency
surgery! (I'm posting from an internet cafe PC that doesn't have Access.)

No problem, I was just curious about this.
Hope your PC comes back to life!

 

[test]

You've understood my question perfectly.
Would it be owned by the person running the code
or by the temporary workspace "user"?
Access.)

No problem, I was just curious about this.
Hope your PC comes back to life!

Hi Jeff

So far I've been unable to create a new query >at all< in the current
database, off a workspace for a different user. Something like:

set ws = dbengine.createworkspace ("", "agent99", "s3cr3t")
set db = ws.opendatabase (dbengine(0)(0).name) ' current db.
db.createquerydef ( ... )

The createquerydef fails with various "insufficient permission" errors;
including one referring to "MSysTables" - wha?

But, as I'm writing this, I realize that I created the new user from code
(not from the UI), so he wouldn't be a member of the Users group yet.

Will try again & post back in due course.

Cheers,
TC

 

[Jeff Conrad]

Hi Jeff

So far I've been unable to create a new query >at all< in
the current database, off a workspace for a different
user. Something like:

set ws = dbengine.createworkspace
("", "agent99", "s3cr3t")
set db = ws.opendatabase (dbengine(0)(0).name) '
current db.
db.createquerydef ( ... )

The createquerydef fails with various "insufficient
permission" errors; including one referring
to "MSysTables" - wha?

But, as I'm writing this, I realize that I created the
new user from code (not from the UI), so he wouldn't be a
member of the Users group yet.

Will try again & post back in due course.

Cheers,
TC

Humm... interesting TC.
I'm surprised you received an "insufficient permission"
error. I look forward to hearing the results of your
further test.

 

[TC]

Humm... interesting TC.
I'm surprised you received an "insufficient permission"
error. I look forward to hearing the results of your
further test.

Well, I added my test user to the Users group & now it all works fine. The
result, in a nutshell, is that the created query is owned by the workspace
user - not the user who is running the code.

For example, if Admin creates a workspace for Fred, re-opens the database
from that workspace, & creates a query using that database reference, the
created query is owned by Fred - not Admin.

So, I imagine that a low-permission user >could< create an RWOP query that
gave him access to tables that he normally could not access - if he knew the
username/password of a higher-permission user who did have access to those
tables.

Cheers!
TC

 

[Jeff Conrad]

Well, I added my test user to the Users group & now it all works fine. The

result, in a nutshell, is that the created query is owned by the workspace
user - not the user who is running the code.

For example, if Admin creates a workspace for Fred, re-opens the database
from that workspace, & creates a query using that database reference, the
created query is owned by Fred - not Admin.

So, I imagine that a low-permission user >could< create an RWOP query that
gave him access to tables that he normally could not access - if he knew the
username/password of a higher-permission user who did have access to those
tables.

Cheers!
TC

Hi TC!

Well this is very interesting now isn't it?!
I suspected that might be the result of the test, but wasn't sure.
I suppose this could be of value in certain circumstances if the developer needed to temporarily
grant some "extra" permissions to a *low-level* user without them even knowing what is going on
behind the scenes.
Humm...very, very interesting.
Thanks for expanding my knowledge on this!