How to allow a user access to only one query of my DB?

[Jamesremec]

Hi everyone.

What I'm trying to do is set up a query so that a user can see only the query and not the table it is extracting data from.

I tried using the security wizard and setting the permissions so that the user has access to the query but not to the table. Problem is, when I try to open the query for the user, I get an error that I can't open it because I don't have access to the table that the query is getting the data from. (Hope that makes sense)

Anyways, I'd greatly appreciate it if someone could help me out with this. Please note that the solution does not need to be "bullet proof."

Thanks,
James

 

[Pat Hartman]

You need to use the WITH OWNERACCESS option in the query. Look it up in help.

 

[ElsVanMiert]

Here is an article from www.unsoftwareag.com:

ACCESS ONLINE ENCYCLOPEDIA Article Code: S4

Security of the backend tables ?
PROBLEM


If you activate the security system on the frontend all database objects are securized.
In a split application all tables from the backend file are linked into the frontend file. It is not possible to link tables from a securized backend file even if you use the same MDW file for backend and frontend.


SOLUTION

You have three possibilities to solve this problem:
Integration with DAO (Opendatabase) (A97 and A2K)
Integration with ADOX (Setpermissions)(only A2K)
Utilization of queries with "OwnerAccess" option

The first two solutions require that you transmit both username and password, therefore the third solution is the most secure.


Creation of a query with "OwnerAccess"

Log in with write rights on the securized table
Create a new query
Activate the query property "OwnerAccess" in the query window
Save the query
This query can be used by all logged-in users.
If you use SQL strings in your VBA code then use the following structure:


SELECT Field1,Field2 FROM tableName WITH OWNERACCESS OPTION

 

[Pat Hartman]

If you use SQL strings in your VBA code then use the following structure:

Owner as in OWNERACCESS refers to who OWNs the querydef object. OWNERACCESS will not work except within a stored querydef.