Solved

Locking down permissions at the object level

Posted on 2010-11-30
8
763 Views
Last Modified: 2012-05-10
Hello,

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?

Thanks,
-Torrwin
0
Comment
Question by:Torrwin
[X]
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
8 Comments
 
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.
0
 
LVL 8

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
0
 
LVL 40

Expert Comment

by:lcohan
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]
GO
create schema New_User
GO
ALTER AUTHORIZATION ON SCHEMA::[New_User] TO [your_login]
GO
create view New_User.v_clients as select * from dbo.clients
GO
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 13

Author Comment

by:Torrwin
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?
0
 
LVL 40

Accepted Solution

by:
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.
0
 
LVL 13

Assisted Solution

by:Torrwin
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.
0
 
LVL 40

Expert Comment

by:lcohan
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.
0
 
LVL 13

Author Closing Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

737 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