Frontend/Backend Security

[qwerty906]

Hi all,

I know that this subject has been beat to death but i need some help here. First when you split the db into FE and BE do you do this after you secure the database? Do the permissions flow over after you split it. Cuz i split my db first and then set the permissions on both files. Now i'm having problems with access rights. A majority of the data retrieval is through VBA and SQL statements, I've set the "WITH OWNERACCESS OPTION" but i get the "No permissions on table ****" error. But I've experimented with queries objects which use the same SQL statement and those work fine. Is there some other settings that need to be set for the "WITH OWNERACCESS OPTION" to work in the VBA environment?

Thanks in advace,

 

[Francescina]

I hope this article from the Access Online Encyclopedia (

http://www.unsoftwareag.com)

helps you:
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

 

[qwerty906]

hey thanks for the reply. i've tried the "SELECT Field1,Field2 FROM tableName WITH OWNERACCESS OPTION" syntax with no luck. I looked at the security faq on the MS site and they say it's not possible with VBA to use the "WITH OWNERACCESS OPTION"

http://support.microsoft.com/default.aspx?scid=/support/Access/Content/SECFAQ.asp#_Toc493299671

i managed to set the rights on both front and backend databases. on the front end i've given full administer rights on the tables, and on the backend i set the rights according to the group access level. ideally i wouldn't have set rights instead i'd just use RWOP queries. but since i couldn't get it to work i had to improvise.