asked on

Locking down permissions at the object level

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

Scott Pletcher

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.

Som Tripathi

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

lcohan

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

Torrwin

ASKER

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?

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Torrwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcohan

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.

Torrwin

ASKER

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