September 27, 2010 at 5:13 pm
I have a situation where I would like certain users to have read only permissions to tables in a database. I was able to deny ad-hoc writes by adding these users to the db_denydatawriter role. However these users have execute permissions to all the stored procedures - some of which write to tables. The stored procedures and tables are owned by dbo and therefore update tables regardless of whether the user calling the proc is in db_denydatawriter. Other than going through each stored procedure, determining whether the sp writes to tables and if so revoking the EXECUTE permission, is there an easier way to deny writes in the procs? It would be fine if the procs were to fail if it attempts to write also.
September 27, 2010 at 5:25 pm
In your procs, do you have a statement that has "Execute as" near the top?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 27, 2010 at 5:41 pm
Very interesting. I have learned something new.
Even with an explicit deny on the table for update, delete, and insert as well as the denydatawriter - the user that executes the proc can still insert data into a table if that user has execute permission on the proc. That is not good.
I am looking still to find a resolution.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 27, 2010 at 5:58 pm
CirquedeSQLeil (9/27/2010)
Very interesting. I have learned something new.Even with an explicit deny on the table for update, delete, and insert as well as the denydatawriter - the user that executes the proc can still insert data into a table if that user has execute permission on the proc. That is not good.
I am looking still to find a resolution.
It's called "implied permissions". If you grant permission to a sproc, you basically allow the user to perform any action described in the sproc under the permissions the user had who created the sproc (as long as you're not referencing a different db or "cross db ownership chaining" is set to ON).
I actually think this concept is good since you can control how a user can access a table: if the permission to execute a sproc would imply that a user could always access a table it would be a mess. But if the sproc will take care of what as user is allowed to see (e.g. just rows with specific values in a column) you'll have a lot more options to control access to a table.
But if you don't want the user to insert any data into a specific table, you should not grant permission on a sproc that does exactly the same.
@nadabadan: What would be the expected behavior from your point of view? What would happen if the user would have the permission to insert values into a table based on data selected from a table where the user has no access to?
September 27, 2010 at 6:01 pm
LutzM (9/27/2010)
CirquedeSQLeil (9/27/2010)
Very interesting. I have learned something new.Even with an explicit deny on the table for update, delete, and insert as well as the denydatawriter - the user that executes the proc can still insert data into a table if that user has execute permission on the proc. That is not good.
I am looking still to find a resolution.
It's called "implied permissions". If you grant permission to a sproc, you basically allow the user to perform any action described in the sproc under the permissions the user had who created the sproc (as long as you're not referencing a different db or "cross db ownership chaining" is set to ON).
This was what I was thinking and that it was by design - but wanted to be certain. Thanks Lutz
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 27, 2010 at 6:20 pm
LutzM (9/27/2010)
I actually think this concept is good since you can control how a user can access a table: if the permission to execute a sproc would imply that a user could always access a table it would be a mess. But if the sproc will take care of what as user is allowed to see (e.g. just rows with specific values in a column) you'll have a lot more options to control access to a table.
I agree completely. In fact my philosophy is to grant only execute access to most users - the only users who need anything other than execute access are those who need to write SPs that access tables (fixed not temporary tables) other than through other SPs.
And I wish I'd known and understood this mechanism in SQL Server 9 years ago - some build scripts and the whole security design for the databases I inherited about then (which then had no security at all, which amazed me) would have been much simpler if I'd noticed that MS had done it right.
Tom
September 27, 2010 at 6:54 pm
LutzM (9/27/2010)
CirquedeSQLeil (9/27/2010)
Very interesting. I have learned something new.Even with an explicit deny on the table for update, delete, and insert as well as the denydatawriter - the user that executes the proc can still insert data into a table if that user has execute permission on the proc. That is not good.
I am looking still to find a resolution.
It's called "implied permissions". If you grant permission to a sproc, you basically allow the user to perform any action described in the sproc under the permissions the user had who created the sproc (as long as you're not referencing a different db or "cross db ownership chaining" is set to ON).
I actually think this concept is good since you can control how a user can access a table: if the permission to execute a sproc would imply that a user could always access a table it would be a mess. But if the sproc will take care of what as user is allowed to see (e.g. just rows with specific values in a column) you'll have a lot more options to control access to a table.
But if you don't want the user to insert any data into a specific table, you should not grant permission on a sproc that does exactly the same.
@nadabadan: What would be the expected behavior from your point of view? What would happen if the user would have the permission to insert values into a table based on data selected from a table where the user has no access to?
Lutz, it is called ownership chaining and I understand the why and it is behaving as I expected. I am looking for ways to around it. Otherwise, I'd have to look through quite a few sprocs to individually deny EXECUTE permissions. I can search the text of the procs for "UPDATE", "INSERT", "DELETE", but it won't be of much help due to the number of temp tables and table variables that are being updated in the procs.
September 28, 2010 at 4:26 am
Have you looked into DDL triggers yet?
You can use them to rollback transactions (in this case for INSERT statements). The only caveat is that I can't remember whether they can be applied to certain users or if they work for all users.
September 28, 2010 at 6:59 am
How are the users logging on? Using an SQL ID, web application, etc.
And do they belong to a different schema than dbo?
Those details could be important clues.
I'm not any kind of permissions expert, and maybe Books On Line mught be helpful.
I usually use integrated security, and Active Directory groups, and put them in dbreader.
Doing this, I usually have to pretty much explicitly grant everything.
So they are unable to execute sp's usless granted.
Hope this gives you some ideas.
Some good conversation on implied permisions.
Greg E
September 28, 2010 at 12:14 pm
Brandie Tarvin (9/28/2010)
Have you looked into DDL triggers yet?You can use them to rollback transactions (in this case for INSERT statements). The only caveat is that I can't remember whether they can be applied to certain users or if they work for all users.
You can actually have the trigger fire off based on certain conditions including perms of users. I used this kind of method to prevent certain users from performing certain actions.
I hadn't recommended this yet due to the related work involved - each table would need to have a trigger created. It seems like the OP is looking for a method that can be all-encompassing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2010 at 12:23 pm
Jason,
I thought DDL triggers were Server level and Database level only. ??
September 28, 2010 at 12:29 pm
Yes, and I should have clearly stated that you made me think of a trigger for the table.
A trigger at the DB level or the Server level would be too restrictive for this case - IMO. I don't think a DDL trigger would do the job - but I could be wrong.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2010 at 7:21 pm
I will get more detailed in what I want. We have two identical servers ( A and B ). Server A is live and Server B is the failover server. The idea is to use server B as a read-only server. This way we are using both servers instead of having server B do nothing while server A is up. To prevent accidental writes to B, I want to deny write permissions to all users but one which is used to replicate from A to B. I put the the users in db_denydatawriter to prevent ad-hoc query writes. I was wondering if it was possible to prevent writes from sp executions. Other than going sp by sp and denying execution permissions to those sps that actually modify data?
September 28, 2010 at 7:29 pm
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.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2010 at 7:31 pm
Triggers would not necessarily be the right way to go. If server B is a failover server, you'd have to disable the triggers on a failover.
Are you trying to use the failover server as a reporting server while the primary server is runninng?
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply