September 28, 2010 at 9:13 pm
CirquedeSQLeil (9/28/2010)
You will need to go proc by proc and deny execute to the users.Another option will be to modify the app so that read type connections are the only connections to the standby server. By that I mean you would need to have only the pieces of the app select from the standby server and the writes will write to the main server.
The apps will be issuing read-only queries to the standby. But I want to add another layer of security against writes to the standby at the database level. What I ended up doing was going proc by proc ( very tedious ) and denying execute permissions. If anyone has a better solution, I'm still interested purely for my own edification.
September 29, 2010 at 4:21 am
If you're replicating over to Server B, then don't replicate the procs in question. Script them out someplace else so you can easily run them on Server B if and when you cut things over (Replication requires a manual cut over, it's not automatic).
That way the read only users can't get around the Read Only perms by executing procs. How can you execute something that doesn't exist? @=)
September 29, 2010 at 8:54 am
nadabadan (9/28/2010)
CirquedeSQLeil (9/28/2010)
You will need to go proc by proc and deny execute to the users.Another option will be to modify the app so that read type connections are the only connections to the standby server. By that I mean you would need to have only the pieces of the app select from the standby server and the writes will write to the main server.
The apps will be issuing read-only queries to the standby. But I want to add another layer of security against writes to the standby at the database level. What I ended up doing was going proc by proc ( very tedious ) and denying execute permissions. If anyone has a better solution, I'm still interested purely for my own edification.
Just want to be sure that you understand you will have to reset those permissions should you have to failover to this server and it has to become the production server.
September 29, 2010 at 5:46 pm
Lynn Pettis (9/29/2010)
nadabadan (9/28/2010)
CirquedeSQLeil (9/28/2010)
You will need to go proc by proc and deny execute to the users.Another option will be to modify the app so that read type connections are the only connections to the standby server. By that I mean you would need to have only the pieces of the app select from the standby server and the writes will write to the main server.
The apps will be issuing read-only queries to the standby. But I want to add another layer of security against writes to the standby at the database level. What I ended up doing was going proc by proc ( very tedious ) and denying execute permissions. If anyone has a better solution, I'm still interested purely for my own edification.
Just want to be sure that you understand you will have to reset those permissions should you have to failover to this server and it has to become the production server.
I thought it was a given. I have a script ready in case of failover. 🙂
September 29, 2010 at 5:49 pm
Brandie Tarvin (9/29/2010)
If you're replicating over to Server B, then don't replicate the procs in question. Script them out someplace else so you can easily run them on Server B if and when you cut things over (Replication requires a manual cut over, it's not automatic).That way the read only users can't get around the Read Only perms by executing procs. How can you execute something that doesn't exist? @=)
The sps are already on server B. A and B are identical and we are replicating from A to B. Good idea though.
October 1, 2010 at 5:35 am
nadabadan (9/29/2010)
Brandie Tarvin (9/29/2010)
If you're replicating over to Server B, then don't replicate the procs in question. Script them out someplace else so you can easily run them on Server B if and when you cut things over (Replication requires a manual cut over, it's not automatic).That way the read only users can't get around the Read Only perms by executing procs. How can you execute something that doesn't exist? @=)
The sps are already on server B. A and B are identical and we are replicating from A to B. Good idea though.
You could try removing the SPs off of Server B and make sure they don't replicate.
October 1, 2010 at 5:47 am
Hi
My 2 cents...
A different appoach here - A procedure checks for the users rights on the tables for performing the required actions. This common procedure is called from inside all the (required) procedures or called from the application (if possible) before the "actual" procedure is called. Only if the common procedure returns a success value which denotes that the user has rights on the table is the "actual procedure" called.
"Keep Trying"
October 1, 2010 at 10:38 am
That would be a beautiful solution for an active database. (EDIT: Depending on how it's implemented. Incorrectly done, it could just cause pain ... )
If I'm reading the first post correctly, though, he wants them to be able to have those rights if he fails over the database. He just doesn't want them to be able to do it in the read only version.
October 5, 2010 at 7:03 am
Brandie
I did not get that. Fail over as in failing over the database ? The first pos does not seem to mention that. I have not read all the posts so if its mentioned in some of the later posts i dont know.
"Keep Trying"
October 5, 2010 at 10:48 am
ChiragNS (10/5/2010)
BrandieI did not get that. Fail over as in failing over the database ? The first pos does not seem to mention that. I have not read all the posts so if its mentioned in some of the later posts i dont know.
Second page, the OP mentions that he's set up his two servers with Replication with Server B being read only until he needs it as a failover server.
October 6, 2010 at 5:59 am
Thanks Brandie.
"Keep Trying"
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply