June 26, 2010 at 8:04 am
I have created a set of stored procedures that INSERT, UPDATE, DELETE and extract data. I have also created a database role and granted it EXECUTE permissions to these stored procedures. To this database role I have assigned my users.
I soon found that the database role not only needed EXECUTE permission to the stored procedures but also needed the INSERT, UPDATE, DELETE and SELECT permissions to the tables referenced by the stored procedures. Having done this, all works ok however I do not want the users assigned to the database role to be able to connect to the database using something like MS Excel Query and interrogate the database tables. Have I mis-understood the point of using databases roles? How do I allow the users to execute the stored procs but prevent them accessing the data from the tables?
June 26, 2010 at 11:02 am
David-155102 (6/26/2010)
I soon found that the database role not only needed EXECUTE permission to the stored procedures but also needed the INSERT, UPDATE, DELETE and SELECT permissions to the tables referenced by the stored procedures.
it shouldn't, unless you've either used dynamic SQL or the stored procs and the tables are owned (created) by different database users.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2010 at 7:31 am
Hi Gail,
Two of the stored procs use dynamic SQL but all of them were created by the dbo.
June 27, 2010 at 8:21 am
One of the downsides of dynamic SQL is that you have to grant direct permissions to the objects used in the dynamic SQL. Ownership chaining doesn't apply. You can use EXECUTE AS to change the security context of the stored proc and avoid having to grant the calling user permissions to the objects if you wish. It's an additional level of complexity, but it does help.
As for the other procs, the ownership chaining should work. Check the principal_id in sys.objects. Are all the objects in the same schema?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2010 at 11:02 am
I checked the principal ids and found that all of the objects are owned by the same schema, however, I also found that the client application that accesses the database has a load of in-line queries which I suspect is causing the problem.
I am going to convert the in-line queries to use stored procedures and see if the security problem still exists.
Thanks
David
June 28, 2010 at 2:01 pm
David-155102 (6/28/2010)
I also found that the client application that accesses the database has a load of in-line queries which I suspect is causing the problem.
Yup, they'll require direct access to the tables
I am going to convert the in-line queries to use stored procedures and see if the security problem still exists.
Shouldn't, provided you avoid dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2010 at 2:05 am
Changing the in-line queries resolved the problem; all is as expected 🙂
Thanks for your help
June 30, 2010 at 2:19 am
Excellent.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply