Access~CITRIX~SQL 2

[legalmindz28]

Hi - I have created a database in Access with a SQL backend and it is deployed through CITRIX. However, users are not able to connect to the SQL tables because of not having "write permission" to the tables. I am aware that I need to have an ODBC connection added to CITRIX. Is there anyway of having users log into the database and update the tables without granting write permissions to the SQL database?

Thanks!

 

[jrbarnett]

1. create some stored procedures with permissions to manipulate the data (add/edit/delete) as appropriate in SQL.
2. Grant a role in the DB for using this application execute permissions on this SP.
3. Execute these stored procs through an ADO connection in Access.

John

 

[AlexCuse]

You can probably limit the permissions to certain tables. I would write Stored Procedures to do the update, and grant execute permission to only these procs.

But no, the users will need some kind of permission in order to write to the tables.

Another option is to create a login for your app (and use this in any connection strings), and give the app write permission.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jrbarnett]

Alex

Provided the account that owns the table is the same as the owner of the SP's, any SP's that run Insert, Update or Delete operations will run, so there is no need for the account to have access to the tables directly (this works with SQL 2000 and 2005).

As part of a system and data security lockdown, after I implemented this in one of my systems and revoked the old permissions, I found somebody trying to bypass the security system in the old app when "their query didn't work any more." Oops, caught them red handed.

John

 

[AlexCuse]

Yeah that's exactly what I meant. Re-reading the post it wasn't very clear at all though! Good thing you beat me to thepunch



Ignorance of certain subjects is a great part of wisdom