Locking down permissions at the object level

Posted on 2010-11-30
Last Modified: 2012-05-10

I've been on an Oracle DB project for awhile, and i've gotten a little rusty on SQL Server.  I have a user who wants to consume an existing SQL Server 2008 database in his BI application.  So, i've creating a login for him for ODBC access.  

I would like to make it so he can only see specific Views and/or Synonyms created just for his use, and nothing else.  What should I configure to accomplish this?  Would a schema work best, and if so how do I assign the objects to his schema?

I've tried giving him the deny data reader/writer permission and then granting him specific object permissions on the views/synonyms, but instead of seeing those specific objects all he can see is the information_schema.* and sys.* objects.  Do I need to specifically deny permission to these?

Thoughts and/or best practices?

Question by:Torrwin
LVL 69

Expert Comment

ID: 34240333
DENY will override normal SELECT permissions, so you'll have to undo the DENY at the read level.

You can just GRANT him access only to the specific objects he needs to see; he won't be able to see the others under normal conditions.

Or you can explicitly DENY him access to the other objects individually.

Expert Comment

by:Som Tripathi
ID: 34240343
Remove the User from Deny read/write permission.

Just grant specific permssion to those objects with -

GRANT SELECT ON View_or_table_name to User_Name
LVL 39

Expert Comment

ID: 34240383
Best way to handle security in the context you mentioned " he can only see specific Views and/or Synonyms created just for his use, and nothing else" is to create a new schema in your database and add that user with public access only to that database default schema the one that you just created. All objects "created just for his use" MUST be created on that schema and that should restrict access for that user to that schema alone. Now if the user needs to read/write data from other schema I.E dbo he must get that grant and exec rights as well.

Something like below where your_login user will need to be granted select right on table clients in dbo schema as well and is new_user schema owner so he can access the view created and any other object created on schema New_user

USE [database]
create schema New_User
create view New_User.v_clients as select * from dbo.clients
LVL 13

Author Comment

ID: 34242350
Now if the user needs to read/write data from other schema I.E dbo he must get that grant and exec rights as well.

If I grant the SELECT permission on a table/view from dbo, then that original table/view shows up in their list of ODBC tables.  

How can I reference the data without giving them access to view it directly?
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

LVL 39

Accepted Solution

lcohan earned 500 total points
ID: 34252829
I think that could be done thrugh a standard database role and add the login to that role. Honestly I didn't tried that yet and not sure why you need to hide the table from user if all they have is select rights against it thrugh the view from the schema he/she ows. You could go more granular and I believe that should do it . I mean you could grant rights at the table.column(s) level from DBO schema that will be used in the New_User schema.view. Keep in mind that only SELECT, REFERENCES, and UPDATE permissions can be granted on a column.
It may be work but this way it should work and if this still doesn't there's a "VIEW DEFINITION" Object permission  that you could try to revoke to hide the table from ODBC.
LVL 13

Assisted Solution

Torrwin earned 0 total points
ID: 34258530
I think that could be done thrugh a standard database role and add the login to that role.

I had actually just tried that, and it seems to be working great.  Using a role allows the user to see the views I want, but not the tables the views rely upon.  The reason for this is usually due to data of a sensitive nature.  For example, payroll/HR information.  In my case, I don't want to have to teach the end user how to navigate my database schema.  Using views I can display the data very similarly to the UI they are used to, and still allow them to create their own reports by connecting via ODBC.

For future readers, you can limit access the INFORMATION_SCHEMA.* and SYS.* views by revoking their SELECT permissions on the "public" role of the master db.
LVL 39

Expert Comment

ID: 34260665
And further to that you could limit the default unwanted access from the fixed db_datareader and db_datawriter roles by writing dynamic sql in a cursor based on following statements
exec sp_helpuser 'db_datareader'  or exec sp_helpuser 'db_datawriter'  to restrict access through standard database role only.
LVL 13

Author Closing Comment

ID: 34289833
Accepting my comment as part of the solution as well because it contains the information on hiding the default views.

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now