May 13, 2002 at 7:47 am
Hi ,
Here is what i try to implement:
- A user connects to a server/db via NT Security
- A user can read info -grant select- on a table linked to a default DSN connected with excel/access/other odbc ,etc
- Only via a specific DSN (used by application) can the user update.
Anyone has ideas ? without using the SQL Server db Application login off course.
I was looking at a triger fired on connection which can grant rights on the fly ?
thanks for your ideas.
May 13, 2002 at 8:08 am
Unfortunately Application Roles are the only way to override a users permissions. You cannot change as you suggest with an SP without giving their ID rights to change their permissions and thus you open a secuirty hole. I no of no other options that could work.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 13, 2002 at 8:32 am
Possibly you could roll your own. I use embedded sql logins for most of my apps, downside is that one way or other updating them is a pain. I think you could create a password "service" that stored app name, sql userid, password, etc. User connects to the db with NT rights to run a proc that would return the appropriate login/password for the app to use. Combine that with some type of obfuscation and/or encryption and you have a way to change login, password on the fly...and along with it permissions of course.
Havent tried it yet, just been thinking about it:-)
Andy
May 14, 2002 at 1:02 am
I try our idea ,but this requires that the application fires an sp whith set the security roles. I like this to be handled by the Db. The Application is from a outsourced corp ,and does not use transaction server.
How do initiate a sp when a user connects to a database ? A 'select' triger on sysusers ? in BOL there is no info on a 'select' triger.
is there an other way ? this should be done on db/server level , i'm out of options, any idea whould be welcome.
quote:
Possibly you could roll your own. I use embedded sql logins for most of my apps, downside is that one way or other updating them is a pain. I think you could create a password "service" that stored app name, sql userid, password, etc. User connects to the db with NT rights to run a proc that would return the appropriate login/password for the app to use. Combine that with some type of obfuscation and/or encryption and you have a way to change login, password on the fly...and along with it permissions of course.Havent tried it yet, just been thinking about it:-)
Andy
May 14, 2002 at 4:50 am
No Select trigger exists. As for firing an sp when a user connects, this has to be built into the logic of the application they are using. At this time there is no on user connect event for the DB side (but would be a nice feature).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply