October 1, 2014 at 5:31 am
Hi,
A few questions.
1. Is it possible to grant select on all tables in a schema but also restrict access to all views in the same schema?
2. If I want to restrict the same user from having any access to a schema do I explicitly have to deny each right in turn or is there a way of doing it for all rights in the schema?
e.g
DENY ALTER ON SCHEMA::[Process] TO [RestrictedUser]
DENY CONTROL ON SCHEMA::[Process] TO [RestrictedUser]
DENY DELETE ON SCHEMA::[Process] TO [RestrictedUser]
DENY EXECUTE ON SCHEMA::[Process] TO [RestrictedUser]
DENY INSERT ON SCHEMA::[Process] TO [RestrictedUser]
etc....
3. I also want to stop this person querying tables such as INFORMATIONAL_SCHEMA, I have restricted access using DENY but they are still able to select from this view.
Thanks in advance.
October 1, 2014 at 6:45 am
Hi,
1. Yes you can grant select to tables and deny to views in the same schema. The way I would do it is to add the user to the db_datareader role and then create a new custom role, deny SELECT to the views and add the user to it.
2. Yes you can deny SELECT to a whole schema. Why would you want to deny access to any schema? What would the point be in adding the user to the database?
3. The user is a member of the public group which would grant them access to the INFORMATION_SCHEMA schema.
October 1, 2014 at 7:21 am
Hi,
Thanks for the response.
#1 - Ok so if I deny access to all the current views via this new role and then someone creates a view in future I will have to update the role? Was looking something that doesn't need maintaining - looks as if this is not possible.
#2 - There are about 10 schemas in total, I have given select access on a number of them so I'm thinking I don't have to do anything as far as the others are concerned as DENY is implicit?
#3 - Thought that was the case. Don't really want to change the public role just yet until I have a better understanding of the users who rely on it. Only been here a week.
DBA From The Cold (10/1/2014)
Hi,1. Yes you can grant select to tables and deny to views in the same schema. The way I would do it is to add the user to the db_datareader role and then create a new custom role, deny SELECT to the views and add the user to it.
2. Yes you can deny SELECT to a whole schema. Why would you want to deny access to any schema? What would the point be in adding the user to the database?
3. The user is a member of the public group which would grant them access to the INFORMATION_SCHEMA schema.
October 1, 2014 at 7:21 am
These are all rather strange requirements which make me think this is likely school work. Things like denying access to views but allowing access to tables just doesn't seem to have any kind of real world usage.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 1, 2014 at 7:47 am
School was over thirty years ago...
I agree, they are strange requirements but in thirty years of working I've come to conclusion that in your second week at a new company it's best to just do as they ask.
Thanks for your input.
October 1, 2014 at 8:13 am
thedavegray (10/1/2014)
School was over thirty years ago...I agree, they are strange requirements but in thirty years of working I've come to conclusion that in your second week at a new company it's best to just do as they ask.
Thanks for your input.
heh I know what you mean. I have been in at least one place where the intentionally gave me totally bizarre stuff like this to see how I would respond. I didn't find out about until I asked to understand the business logic around what I was being asked to do. I posed my question purely as in informative one. "I will be happy to do this but I can't think of a situation where this would be beneficial. Can you help me understand the situation this is correcting so I know I am fixing?" Something long those lines. Then they admitted it was mostly a test to see if I had clue what I was doing beyond the interview. I am not saying that is what is happening here but it is something I experienced once. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 1, 2014 at 9:11 am
heh I know what you mean. I have been in at least one place where the intentionally gave me totally bizarre stuff like this to see how I would respond.
Hmm, hope not - you could be right though. 😀
Just had a look at some of the views and it seems like they are pulling data from various schemas and also different databases in the instance and also from linked servers. I will politely ask the reason behind this request though.
The other thing they have asked to do is to lock down the informational_schema, I've denied select on this however it does not seem to stop the user selecting from it. I did read that it was controlled by the public role however I cannot see any reference to this schema at the database level.
October 1, 2014 at 9:55 am
thedavegray (10/1/2014)
heh I know what you mean. I have been in at least one place where the intentionally gave me totally bizarre stuff like this to see how I would respond.
Hmm, hope not - you could be right though. 😀
Just had a look at some of the views and it seems like they are pulling data from various schemas and also different databases in the instance and also from linked servers. I will politely ask the reason behind this request though.
The other thing they have asked to do is to lock down the informational_schema, I've denied select on this however it does not seem to stop the user selecting from it. I did read that it was controlled by the public role however I cannot see any reference to this schema at the database level.
I was wondering if perhaps the views were pulling external data. In this case it would make sense to restrict access there. But denying access to InformationSchema is a bit strange. How would a normal user ever be able to query that stuff directly? If they are supposed to working with sql they kind of need that in order to do their job. It is difficult to write decent queries if you can't at least view the table definitions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply