January 29, 2009 at 5:41 pm
Don't worry about the public role. As long as you don't grant any object access to the public role and don't add it to any of the fixed database roles, public won't have access to tables through the public role.
If you don't want someone to have access to the tables that a stored procedure uses, don't give them execute access on that store procedure and don't give them any access to underlying tables and views.
I think if you read my first post, I never said they can run any DML statement they want if you give the access to a stored procedure. They only have access to the stored procedure, not the tables.
You really need to spend time reading about these subjects in SQL Server 2005 Books Online. All of this is explained there in much greater detail.
January 30, 2009 at 8:00 am
My friend I think I am starting to get a little bit confused here. I have tried creating a single user with no privileges at all, just the public role it gets by default. The only privilege I gave to this user was to execute a dbo.sp_xyz. Now the user can query any table and do inserts and updates from the sp in spite of the fact that he hasn't got access to them. This is what I want to stop. The public role hasn't got any privileges to any objects but this user can still access all my dbo objects throught it. I have trying denying the access by denying the control permission to the public role but this makes that all users can't access any objects from the db.
Hope I explained better there. Thanks again for the replies.
Hey everyone you might check this link out, this is kinda what I am talking about. Thanks again!!
January 30, 2009 at 4:39 pm
All right, let's break this down to the basics...
EXECUTE rights against a stored procedure allows you to execute the stored procedure.
SELECT, INSERT, UPDATE, and DELETE rights allow you to perform those operations against a table and view.
Normally, if you don't have the rights, you can't perform the operation. SQL Server makes you explicitly specify rights.
The exception comes in with respect to ownership chaining. Ownership chaining in SQL Server 2005 works based on schema owners. All objects belong to a schema. For instance:
dbo.SomeProcedure
has dbo as the schema. If one object, like a stored procedure, references another object, the owners of the schemas they belong to are checked. For instance, if dbo.SomeProcedure references dbo.SomeTable, the schemas for both objects are looked at. In this case, both objects are in the dbo schema.
When the schema are owned by the same person, and that's always the case when both objects are in the same schema, then SQL Server will allow access to the second object (in this case dbo.SomeTable) when they come through the first object (dbo.SomeProcedure). But it allows this ONLY when you come through that first object. In other words, if a user executed SomeProcedure, they could perform some operation against SomeTable. However, if they issued a SELECT * FROM dbo.SomeTable, they would get an access denied.
When the schema are different, you must look at the owners. For instance, if it was Schema1.SomeProcedure and Schema2.SomeTable and both Schema1 and Schema2 were owned by dbo, executing Schema1.SomeProcedure would allow access to Schema2.SomeTable. If, however, Schema1 was owned by dbo and Schema2 was owned by AnotherUser, this wouldn't be the case. SQL Server would see that the owners are different. Then SQL Server would require a permission check against Schema2.SomeTable. If the user did not have rights to perform the operation, they would get an Access Denied error.
K. Brian Kelley
@kbriankelley
February 2, 2009 at 5:08 pm
K. Brian Kelley (1/30/2009)
All right, let's break this down to the basics......
When the schema are owned by the same person, and that's always the case when both objects are in the same schema, then SQL Server will allow access to the second object (in this case dbo.SomeTable) when they come through the first object (dbo.SomeProcedure). But it allows this ONLY when you come through that first object. In other words, if a user executed SomeProcedure, they could perform some operation against SomeTable. However, if they issued a SELECT * FROM dbo.SomeTable, they would get an access denied.
...
Thank you very much for the explanation, but this is not the way I want things to work, so how could I solve this? Would I have to create a different schema and access through it to my dbo schema? That would be the best approach I guess eh? But I have all my tables and sps in my dbo schema and that is they way they are working and it would be too hard to do this. I guess that eventually we'll have to get to this, but how could I change this to work temporarily?
Thanks again for the replies.
Any suggestions??????? 🙁
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply