March 22, 2010 at 10:41 am
Hi,
I have created a web page for use by one of our admins to do adhoc queries on our database. The webpage is visible to the public, but not linked to and requires the highest admin rights available within our web app (an ecommerce site).
Now, despite the above restrictions, I want to further restrict what this page can do on the database.
I created a new user who has only db_datareader and db_denydatawriter rights (as well as public of course)
I've denied select rights on many tables that won't be needed.
However, there is a table that includes all the orders and select rights on this table will be needed.
However, there are columns in this table with credit card data (the CC number is encrypted) that I don't want to be accessible.
I have denied select rights to these columns.
However, there are a number of views that use this table, which include the CC data. This data is still visible when the view is queried.
The same goes for some stored procedures.
I could make a list of all the views and stored procedures and deny select or execute rights on them to get around this.
However, any future SP's or views wouldn't be blocked like this - each time a view or SP is created, it would have to be manually blocked.
This is just the sort of thing that gets forgotten about after a while, and so I want something more robust and maintainable.
Is there any way I can deny select on all views (regardless of when they are created) and the same for SP execute rights?
Something along the lines of:
DENY SELECT ON 'All Views' TO [WEB_restricted]
GO
use [Findme]
GO
DENY EXECUTE ON 'All Stored Procedures' TO [WEB_restricted]
Any ideas?
March 22, 2010 at 2:17 pm
Instead of using db_datareader, and then trying to remove all other access - it would be better to create your own role and add the specific rights you want to grant those users. Then add that group to the users.
This will give those users only those rights specifically granted to the role and you won't have to worry about new objects getting additional access.
As for the tables with columns you don't want them to see - create a view without those columns and grant select rights on that view to the role.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 22, 2010 at 3:19 pm
Jeffrey Williams-493691 (3/22/2010)
Instead of using db_datareader, and then trying to remove all other access - it would be better to create your own role and add the specific rights you want to grant those users. Then add that group to the users.This will give those users only those rights specifically granted to the role and you won't have to worry about new objects getting additional access.
As for the tables with columns you don't want them to see - create a view without those columns and grant select rights on that view to the role.
Jeffrey,
I knew there'd be an answer, and I knew someone here would have it!
Just given this a go, and it works perfectly 😀
Thanks!
Adam
March 22, 2010 at 3:26 pm
Jeffrey Williams-493691 (3/22/2010)
Instead of using db_datareader, and then trying to remove all other access - it would be better to create your own role and add the specific rights you want to grant those users. Then add that group to the users.This will give those users only those rights specifically granted to the role and you won't have to worry about new objects getting additional access.
As for the tables with columns you don't want them to see - create a view without those columns and grant select rights on that view to the role.
Agreed - best solution for this scenario.
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
March 22, 2010 at 7:40 pm
You are welcome - and thanks for the feedback, it is appreciated.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply