Protecting tables/queries from changes not in forms

[Guest]

Hello,
I am new to user-level security but in desperate need of assistance. I have
a split FE/BE database for about 25 users. I would like to prevent against
users needing to login, and used Joan Wild's awesome site to actually protect
the back-end, which was further than any of my other attempts at security.
However, I don't seem to have all of my needs answered:

I am the only one who needs the ability to change the backend, and I can log
in using my secure MDW to get this.

All users who open the database should be able to make changes to the data,
as all changes that are made in forms are time/namestamped.

What I need to do is prevent against users making changes directly to the
tables/queries, by forcing them to use forms. I have a lot of forms & combo
boxes that use SQL rather than saved queries to define the recordsource, and
I understand that this can be an issue since the user is not the owner?

Simple solutions will likely not address my issues, since some users of the
database are much more advanced than I am and would like to use another
database to tap in to the backend of my database and pull data directly--I am
willing to design a query that they can specify criteria from, but I need to
make sure that they are not going to be able to update any of this data
through the query or the tables, since they'll know the structure...

Any suggestions would be sooooo appreciated. TIA!

 

[Joan Wild]

What I need to do is prevent against users making changes directly to the
tables/queries, by forcing them to use forms.

You're going to need to lock down the frontend and make the tables/queries difficult to get to.

Create custom menus/toolbars for use throughout your application.
Create a startup form (a main menu form if you have one) that is opened on startup.
Use the features in Tools, Startup to
set the startup form
set your default menu (the custom one you made)
disable all the checkboxes about allowing built in menus, toolbars, changes etc.
hide the db window (ensure the custom menu you create does not include the Windows, Unhide item)
uncheck the allow special keys (this will disable the F11 key, among others)

If you need to bypass these startup features, you can hold the shift key down while you open the db. If you feel that your users may use this to bypass your settings, you can disable the shift key bypass - there's an example in help for doing this(look for AllowBypassKey) or at
http://www.mvps.org/access/modules/mdl0011.htm
and
http://www.mvps.org/access/general/gen0040.htm

You can also create a MDE from your database, which will prevent changes to forms, reports and modules (If you do this, be certain to keep your original mdb in case you need to make changes).

I have a lot of forms & combo
boxes that use SQL rather than saved queries to define the recordsource, and
I understand that this can be an issue since the user is not the owner?

Simple solutions will likely not address my issues, since some users of the
database are much more advanced than I am and would like to use another
database to tap in to the backend of my database and pull data directly--I am
willing to design a query that they can specify criteria from, but I need to
make sure that they are not going to be able to update any of this data
through the query or the tables, since they'll know the structure...

Again you can create a RWOP query for each table. Give only read data permission on the RWOP query, and deny all permissions on the tables. They'll be able to create any queries/forms/reports based on the queries. Since they have only read data permission, they won't be able to do anything else. I would suggest that you give them a separate frontend for the purposes of this adhoq querying.

You can read more about RWOP queries at www.jmwild.com/RWOP.htm

 

[Nando]

Again you can create a RWOP query for each table. Give only read data
permission on the RWOP query, and deny all permissions on the tables.
They'll be able to create any queries/forms/reports based on the queries.
Since they have only read data permission, they won't be able to do
anything else. I would suggest that you give them a separate frontend for
the purposes of this adhoq querying.

You can read more about RWOP queries at www.jmwild.com/RWOP.htm

Joan, I was going to post the same problem until I saw this post. I read and
I have a question: Can I have a RWOP query like this:

"SELECT * FROM [Table1] WHERE IsQueryEnable() = True;"

where IsQueryEnable() is a function that returns 'True' or 'False',
indicating whether to allow the query to run or not. This is to avoid the
user actually opens the query directly through the Access interface (an MDE
frontend). Of course that I would have to initialize the internal global
variable of the function to 'True' when the form is open, and to 'False'
when the form is unloaded.

Would this be a good approach to fully deny access to the data, besides the
VBA code? Thanks!

 

[Joan Wild]

Joan, I was going to post the same problem until I saw this post. I read and
I have a question: Can I have a RWOP query like this:

"SELECT * FROM [Table1] WHERE IsQueryEnable() = True;"

Do you mean this is the SQL statement of a saved query? If so, it isn't a RWOP query. The SQL of a saved RWOP query would be
SELECT * FROM [Table1] WHERE IsQueryEnable()=True WITH OWNERACCESS OPTION;

That needs to be a saved query. Using just the SQL statement as the source of a form/report/control won't work, as the 'owner' running the SQL statement presumably wouldn't have permissions to Table1.

where IsQueryEnable() is a function that returns 'True' or 'False',
indicating whether to allow the query to run or not. This is to avoid the
user actually opens the query directly through the Access interface (an MDE
frontend). Of course that I would have to initialize the internal global
variable of the function to 'True' when the form is open, and to 'False'
when the form is unloaded.

I'm not sure I see the point. Either the user has read permission on the query or they don't. If they don't, then they won't be able to open it - the criteria seems pointless. It they do have permission to open it, then the criteria won't stop them from opening the query via the db window. Once it's set to true, they'll still be able to open it directly. You should be able to manage what you need by setting the permissions on the query, right?

You can do other things to lock down the interface, so they can't get to the db window.

 

[Nando]

Do you mean this is the SQL statement of a saved query? If so, it isn't a
RWOP query. The SQL of a saved RWOP query would be SELECT * FROM [Table1]
WHERE IsQueryEnable()=True WITH OWNERACCESS OPTION;

Yep that's it! An RWOP query with a custom function.

I'm not sure I see the point. Either the user has read permission on the
query or they don't. If they don't, then they won't be able to open it -
the criteria seems pointless. It they do have permission to open it, then
the criteria won't stop them from opening the query via the db window.
Once it's set to true, they'll still be able to open it directly. You
should be able to manage what you need by setting the permissions on the
query, right?

What I'm trying to avoid is a user having access to the query's data through
the Access database window. I want the user to have access to the data only
from the form. So RWOP techniques will stop them from using the table
object, and the custom function will stop them from using the query object.

That's why I came up with the query above, because I can make the function
returns True or False by coding (kinda of locking/unlocking the query). Hope
you understand, or perhaps I'm missing something.

 

[Andy]

Pardon my intrusion,

But what happens when the user opens the BE and creates their own query?
If you don't implement ULS, you have no way to prevent this, and they
probably won't use your function.

Just my $.02 worth, but I would listen to what Joan Wild says (and
suggests)...it's pretty much the gospel.

Andy

Do you mean this is the SQL statement of a saved query? If so, it isn't a
RWOP query. The SQL of a saved RWOP query would be SELECT * FROM [Table1]
WHERE IsQueryEnable()=True WITH OWNERACCESS OPTION;

Yep that's it! An RWOP query with a custom function.

I'm not sure I see the point. Either the user has read permission on the
query or they don't. If they don't, then they won't be able to open it -
the criteria seems pointless. It they do have permission to open it, then
the criteria won't stop them from opening the query via the db window.
Once it's set to true, they'll still be able to open it directly. You
should be able to manage what you need by setting the permissions on the
query, right?

What I'm trying to avoid is a user having access to the query's data through
the Access database window. I want the user to have access to the data only
from the form. So RWOP techniques will stop them from using the table
object, and the custom function will stop them from using the query object.

That's why I came up with the query above, because I can make the function
returns True or False by coding (kinda of locking/unlocking the query). Hope
you understand, or perhaps I'm missing something.

 

[Nando]

Pardon my intrusion,

Hi Andy, all feedback is welcomed.

But what happens when the user opens the BE and creates their own query?
If you don't implement ULS, you have no way to prevent this, and they
probably won't use your function.

Hi Andy, ULS will be implemented. The user will not have access at all to
Table1. The user will only access the data through the a query object that
has been set as RWOP under a compiled MDE file.

The query contains a function that returns True or False depending on
whether some code is previously executed. I'm thinking of this, because I
don't want the user to access neither the table or the query objects
manually. I just want to have access to this particular data set only
through code (VBA). So with the function embedded into the query I beleive I
can disallow the query to return records when executed manually.

I need to know if my approach is valid or whether there is something I'm
missing or something else I could do instead to accomplish the same goal.

 

[jacksonmacd]

Hi Andy, all feedback is welcomed.


Hi Andy, ULS will be implemented. The user will not have access at all to
Table1. The user will only access the data through the a query object that
has been set as RWOP under a compiled MDE file.

The query contains a function that returns True or False depending on
whether some code is previously executed. I'm thinking of this, because I
don't want the user to access neither the table or the query objects
manually. I just want to have access to this particular data set only
through code (VBA). So with the function embedded into the query I beleive I
can disallow the query to return records when executed manually.

I need to know if my approach is valid or whether there is something I'm
missing or something else I could do instead to accomplish the same goal.

Sounds like it would work, but only experimentation will show for
sure. Perhaps your function could include some aspect of whether the
"target" form has Focus or not. If the form is active, then the custom
function returns True. If the user has left the form, then the custom
function returns False. Otherwise, what's to stop the user from
opening your form (thus enabling the query), then opening the query
directly?

Post back with results...

 

[Joan Wild]

What I'm trying to avoid is a user having access to the query's data through
the Access database window. I want the user to have access to the data only
from the form. So RWOP techniques will stop them from using the table
object, and the custom function will stop them from using the query object.

That's why I came up with the query above, because I can make the function
returns True or False by coding (kinda of locking/unlocking the query). Hope
you understand, or perhaps I'm missing something.

I don't think I do understand, probably because I don't understand what your function does. If a user is supposed to be able to open your form (and therefore the query), the function has to return true. It would also return true if the query is opened via the db window, and the user could open it there as well? It depends on what the function tests - more info needed.

If you hide the db window and disable the shiftkey bypass, the user won't be able to get to the db window. Even if they did, what is wrong with them being able to open a query (which presumably they have only read data permission on)?

 

[Nando]

I don't think I do understand, probably because I don't understand what
your function does. If a user is supposed to be able to open your form
(and therefore the query), the function has to return true. It would also
return true if the query is opened via the db window, and the user could
open it there as well? It depends on what the function tests - more info
needed.

Joan, my objective is to disallow direct access to the records contained
into a table object called Table1. So I created a RWOP query. The RWOP query
is the source for a form. However, I just want to limit the users to access
data only from the form. So I'll have to work something out so that users
can't see the data when they play around and decide to executed the RWOP
object directly. They must only have access to the data through the form (no
queries or tables).

The solution I'm proposing to discuss is to embed a function within an RWOP
query so I can decide when the query returns records. So if we create a
module with the following code:

Option Explicit
Public blnQueryStatus as boolean
Public Function IsQueryEnabled ( ) as boolean
IsQueryEnabled = blnQueryStatus
End Function

And then I create my RWOP query object containing the following code:

SELECT * FROM [Table1] WHERE IsQueryEnabled( ) = True WITH OWNERACCESS
OPTION;

Then on my form I can have the following code on the Open event:
blnQueryStatus = True

And then this other code under the Unload event:
blnQueryStatus = False

So the query (which the form is based on) will return records only when is
called from the form, but not when it is called directly.

This would be all in a FE file, so tables are linked tables, the file is
compiled as MDE, and distributed to users.

Is my approach/strategy correct? or does Access provide any other means to
only allow access to the data through the form (disallowing direct access to
the table and to query)? Hope it is clear now. So what do you think of all
this?

 

[Nando]

Sounds like it would work, but only experimentation will show for
sure. Perhaps your function could include some aspect of whether the
"target" form has Focus or not. If the form is active, then the custom
function returns True. If the user has left the form, then the custom
function returns False. Otherwise, what's to stop the user from
opening your form (thus enabling the query), then opening the query
directly?

Post back with results...

Thanks Jackson, Yes, in theory it should work. I'm trying to see if Access
provided another ways to do this. So I'm awaiting on everyone's feedback or
ideas. I'm already working on this approach in a much more complex way. In
some cases I don't even need a form, and I just want to lock a table and the
RWOP query, and have access through the data just through VB code (again no
forms).

However, I'm finding out that Access becomes quite unstable (crashing) when
using custom VBA functions within queries. This is when I'm passing some
query variables as parameters to these functions. I will sure post back
results.

 

[Joan Wild]

Joan, my objective is to disallow direct access to the records contained
into a table object called Table1. So I created a RWOP query. The RWOP query
is the source for a form. However, I just want to limit the users to access
data only from the form. So I'll have to work something out so that users
can't see the data when they play around and decide to executed the RWOP
object directly. They must only have access to the data through the form (no
queries or tables).

I understand that.

Then on my form I can have the following code on the Open event:
blnQueryStatus = True

And then this other code under the Unload event:
blnQueryStatus = False

So the query (which the form is based on) will return records only when is
called from the form, but not when it is called directly.

I think the open event is too late, the recordsource has already been called at that point. Perhaps the Load event, however you can't cancel that.

Is my approach/strategy correct? or does Access provide any other means to
only allow access to the data through the form (disallowing direct access to
the table and to query)? Hope it is clear now. So what do you think of all
this?

I would lock down the FE:

Create custom menus/toolbars for use throughout your application.
Create a startup form (a main menu form if you have one) that is opened on startup.
Use the features in Tools, Startup to
set the startup form
set your default menu (the custom one you made)
disable all the checkboxes about allowing built in menus, toolbars, changes etc.
hide the db window (ensure the custom menu you create does not include the Windows, Unhide item)
Uncheck the allow special keys (this will disable the F11 key, among others)

Disable the shift key bypass - there's an example in help for doing this(look for AllowBypassKey) or at
http://www.mvps.org/access/modules/mdl0011.htm
and
http://www.mvps.org/access/general/gen0040.htm

Create a MDE as you've planned.

That should be enough to keep them from seeing the db window, let alone opening a query.

 

[Guest]

Thank you, that makes a lot of sense. I already have the custom menu (print,
quit--what else could they need?) and deploy all MDE-FEs with all options
disabled and shift key disallowed, with an admin button that is only visible
when the user's NT username is mine, that shows the DB window--this has been
enough up to this point.

Now I just need a few dozen hours to re-base everything on queries rather
than tables...and here I thought that that phrase I kept seeing "WITH OWNER
ACCESS" was useless, so I've been manually erasing it from every single combo
box in my database(s) for the past year. =)

 

[Joan Wild]

Well just to be clear, your combobox rowsource should say

SELECT * FROM <name of query>;

The SQL of <name of query> (which is a saved query that users have read data permissions on) should say
SELECT * FROM <name of table> WITH OWNERACCESS OPTION;