November 6, 2003 at 8:41 am
We currently run Oracle RDB. In there is a command that lets you find out whether a connection is coming in as read or
read/write. Is there a way to see that through SQL Server EM or a SP that can be run to gather that information. I just need to know on a connection by connection basis if it's coming into the database as read or read/write.
Thank you in advance,
Jennifer
November 6, 2003 at 9:29 am
Every connection has read/write according to permissions. It is not based on the connection, but the login.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 6, 2003 at 3:07 pm
Thanks Steve for the response. I did a lot of reading since I posted this and your answer makes perfect sense. I'm having a difficult time convincing the developers of that though. I suggested creating 2 seperate logins to the database, one with read and one with read/write permissions. You or anyone else would do the same too, right?
November 7, 2003 at 10:29 am
Yes Jennifer, we often have ids that only have read permission, while others can read /write. The best idea is that no one but the DBA has access to the tables and all application access is via stored procedures to which an id (or a role) either has EXEC permission or no permissions. This is the same for Oracle. I know developers don't always like to create and use stored procs, so sometimes you need to present the risks to management and create a policy that says " all new apps will use stored procs for all db access"
Francis
November 10, 2003 at 8:26 am
Two logins is a good approach, though using it forces you to use sql logins and not NT authentication (if you care). Have to be very careful that later on no one changes the permissions on the read only proc out of laziness/lack of knowledge, you wind up with both logins being about to write. Definitely push that they CANNOT rely on the login being read only, just that they should use it that way - in other words, dont let them do a delete and assume that based on the login it will succeed/fail.
Andy
November 10, 2003 at 8:52 am
Thanks fhanlon and Andy for the additional information. Andy, what in your opinion, would be the optimal way to handle this? Using windows authentication and having only one connection to the database?
November 10, 2003 at 8:52 am
Thanks fhanlon and Andy for the additional information. Andy, what in your opinion, would be the optimal way to handle this? Using windows authentication and having only one connection to the database?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply