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
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 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 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

729 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