Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Locking down permissions at the object level

Posted on 2010-11-30
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 70

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 40

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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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 40

Accepted Solution

lcohan earned 2000 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 40

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

618 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