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

by:Scott Pletcher
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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?
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

770 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