Limiting users to views only in database

Question

Limiting users to views only in database

gisles

SSC Eights!

Points: 808
More actions
January 23, 2009 at 2:58 pm

#199066

I would like to do one of the following which ever is the best way to accomplist what I need.
1. Create views in my database and allow users to connect to the database via ODBC and see the view only. I do not want them to see the tables.
2. Create another database and use that to show the views from the other database and have them connect to it via ODBC - that way they are not connection to our 'live' database directly.
Does that make sense? Our goal is to limit the end user connecting via ODBC to only the data within the views we create.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eric SSCommitted Points: 1803 More actions · Answer 1

I would use option 1. I don’t know that option 2 would really offer you any benefits and there are just a few more details to implement and maintain.

You could create a role, grant select permission for each view to that role and then add users to the role. That way you don’t have to manage security directly for each user.

You might also want to put the user views in a schema other than dbo. You can give the role and schema the same name then it is easier to audit and make sure roles have access to everything they should and nothing they shouldn’t.

One thing to look out for is making sure users don’t have any other permissions (either granted to them directly or inherited from role membership) that will allow them to see tables.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

Not to be contrary, but I like option 2. That way, you wouldn't have to worry so much about doing individual grants or accidently doing a grant on a table instead of a view. If the DB's are on the same server and instance, there's virtually no performance hit compared to option 1.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric SSCommitted Points: 1803 More actions · Answer 3

Jeff has a good point with option 2.

Option 1:

Pros: No cross database dependencies

Cons: Potential to give users too much/too little access (could have permission on a table, might not have permission on a view)

Option 2:

Pros: OK for users to see everything since database only has views

Cons: Users need to have access to database with tables

We currently have both models in place where I work and I tend to see the con of #2 being the most problematic issue. What usually happens is a new user requests access and the DBA is not aware of the other dependencies. The user gets added to the view database but can’t access the table database.

One additional consideration might be if you have multiple categories of views, for example views that everyone can see and views that contain sensitive information like salary, social security number, etc that only some people should see. In that case the con of option 1 becomes a business or regulatory requirement and you need to comply. You could create a database for each view category but that might not be practical to administer.

Both options will work just make sure you have a process to manage the cons for the option you select.

gisles SSC Eights! Points: 808 More actions · Answer 4

I was thinking about option 2 - create another database - but have never done that and do not know how to setup the views to be seen in the other database. How would I do that?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

gisles (1/24/2009)
I was thinking about option 2 - create another database - but have never done that and do not know how to setup the views to be seen in the other database. How would I do that?

My appologies... I'm mostly an "internals" type of data troll and I wouldn't be comfortable trying to set up the privs to protect a database from the external world. I'll have to defer to Eric on this one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 6

Risking a pork chop, I'm going to disagree with Jeff on this.

You should look at option 1, but as was mentioned before, create a schema for the views. Also, I'd recommend using a database role to access the views and only those views. Connect the users to the role. In this way, any incoming connections to the role should only ever see the views and they won't even know the tables exist.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

gisles SSC Eights! Points: 808 More actions · Answer 7

Thanks, I'll work with option 1 since that is familiar to me. I wasn't sure how to do the new database option.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

Grant Fritchey (1/26/2009)
Risking a pork chop, I'm going to disagree with Jeff on this.
You should look at option 1, but as was mentioned before, create a schema for the views. Also, I'd recommend using a database role to access the views and only those views. Connect the users to the role. In this way, any incoming connections to the role should only ever see the views and they won't even know the tables exist.

Heh... no... no pork chops for you :P. I don't believe that either option will be a killer. Option 2 was more of a personal preference on my part.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric SSCommitted Points: 1803 More actions · Answer 9

I think option 1 will serve you best but here is some info on how you could do option 2.

Setting this up is not difficult but when an ownership chain is broken the users lose access. It’s not a big deal to fix if you know what to look for.

See ownership chains:

http://msdn.microsoft.com/en-us/library/ms188676.aspx

Onto the story:

If you have 2 databases, ViewsDb and TablesDb and you want Joe User to have access to the views in ViewsDb but not be able to see anything in TablesDb you can:

Create (or alter) the 2 databases and set DB_CHAINING on. This means they can be the source or target in a cross db ownership chain. Make sure both databases have the same owner (run sp_helpdb on the server to see owners).

Create a table in TablesDb, let’s call it dbo.YourTable. In ViewsDb create a view, let’s call it MyView, that uses 3 part naming to access your table, select cols from TablesDb.dbo.YourTable. Note all objects are in the dbo schema in both databases. That schema is owned by dbo, and in the step above we made sure that both databases are owned by the same login.

Joe User needs to be able to connect to both databases. You can add him (and any other users) to both databases or you could have a windows group created and have Joe added to that group then make that group a user in each database. Some people like windows groups because they don’t have to manage logins/users on the server, some hate them for the same reason. No right or wrong here just depends on what is best for your organization. You probably don’t want to rely on the guest user account, in fact you probably want to revoke connect permissions from guest.

Now Joe User can connect to both databases but he can’t see anything in either one.

The most wide open solution is to make Joe part of the db_datareader role in ViewsDb. That way he can run a select against any table or view (right now there aren’t any tables in ViewsDb). I WOULD NOT suggest this though. Some day your security model will become more complex and you will have to change anyway so you might as well set up for that now.

Create a role in ViewsDb, let’s call it SeeAllViews (create role SeeAllViews). Now grant select permission on MyView to SeeAllViews (Grant Select on dbo.MyView To SeeAllViews). All of your security is now managed within that role so if it is right for one user it is right for them all, and if it is wrong you only need to fix it in one place and it is fixed for everyone.

Now you can add Joe User (or the windows group if you went that route) to the SeeAllViews role (exec sp_addrolemember ‘SeeAllViews’,’Joe User’).

You should now be in business. Joe can connect to ViewsDb, run a select against the view but not see the table in TablesDb. 😀