June 7, 2011 at 2:23 pm
I haven't done much with granting or denying permissions to users in SQL Server. Is there a good reference to start with?
My current problem is that I have a user and I'd like to grant access to just one table. When I create the user under [Server] -> Security -> Logins, that user gets public permission to all databases on that server? That's what it looks like, but I'm not sure if that's right.
If I then go into a database and under [Database] -> Security -> Users, add that user and give them db_datareader permission to that database, is that the only database that user can now see?
From what I've read, if I want them to have specific rights I should grant them rights on a view, but how do I deny them rights everywhere else?
Under Securables, I've granted them SELECT permission to this table and denied all other permissions to that table, but do they still have read access to everything else in that database?
Sorry to be asking for something so basic.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 7, 2011 at 2:36 pm
not really a basic question; usually developers are used to having full permissions on everything, and preventing access isn't really part of the picture until later.
SQL is deny by default...so if you do not give them access, they cannot get to it.
a rule of thumb is never give permissions to PUBLIC, and always create a role for the permissions you want; never grant directly to a user.
in your case, you said you want to grant permissions to a single table and that's it.
so, just to confirm, you have a database with potentially thousands of tables, but you need to give a group of people access to exactly one table in that database....
Permissions are always at a per-database level...the only exception are logins tied to the sysadmin server role, who can do anything they want.
so, in your example, if you granted db_datareader, that means they would be able to SELECT from every table in the database. since you said you wanted a single table accessible,t hat's too much.
Here's a role example for you:
--create role for my normal users
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ExposeOneTableToUsers' AND type = 'R')
BEGIN
CREATE ROLE [ExposeOneTableToUsers]
END
--now add permissions for that one table to the role
GRANT SELECT ON dbo.ExposedTable TO [ExposeOneTableToUsers]
--now add a windows group as a 'User', and add them to the role.
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'[NT AUTHORITY\Authenticated Users]')
BEGIN
CREATE USER [NT AUTHORITY\Authenticated Users] FOR LOGIN [NT AUTHORITY\Authenticated Users]
END
--add the windows group to my limited role.
EXEC sp_addrolemember N'[NT AUTHORITY\Authenticated Users]', N'[ExposeOneTableToUsers]';
Lowell
June 7, 2011 at 3:37 pm
Lowell (6/7/2011)
not really a basic question; usually developers are used to having full permissions on everything, and preventing access isn't really part of the picture until later.SQL is deny by default...so if you do not give them access, they cannot get to it.
a rule of thumb is never give permissions to PUBLIC, and always create a role for the permissions you want; never grant directly to a user.
in your case, you said you want to grant permissions to a single table and that's it.
so, just to confirm, you have a database with potentially thousands of tables, but you need to give a group of people access to exactly one table in that database....
Permissions are always at a per-database level...the only exception are logins tied to the sysadmin server role, who can do anything they want.
so, in your example, if you granted db_datareader, that means they would be able to SELECT from every table in the database. since you said you wanted a single table accessible,t hat's too much.
Here's a role example for you:
--create role for my normal users
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ExposeOneTableToUsers' AND type = 'R')
BEGIN
CREATE ROLE [ExposeOneTableToUsers]
END
--now add permissions for that one table to the role
GRANT SELECT ON dbo.ExposedTable TO [ExposeOneTableToUsers]
--now add a windows group as a 'User', and add them to the role.
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'[NT AUTHORITY\Authenticated Users]')
BEGIN
CREATE USER [NT AUTHORITY\Authenticated Users] FOR LOGIN [NT AUTHORITY\Authenticated Users]
END
--add the windows group to my limited role.
EXEC sp_addrolemember N'[NT AUTHORITY\Authenticated Users]', N'[ExposeOneTableToUsers]';
Thanks, I'll take a look at that. It is actually a single person I'm granting access to a single table. In the scenario I described, they shouldn't see any database except the one I specifically added them to?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 7, 2011 at 3:48 pm
Stefan Krzywicki (6/7/2011)
Thanks, I'll take a look at that. It is actually a single person I'm granting access to a single table. In the scenario I described, they shouldn't see any database except the one I specifically added them to?
yes, that is correct, if you don't add the login to a database as a user, they cannot access it. they cannot access anything in the database, until you add them to a role or do an expicit grant to the user, either.
the rule of thumb about adding roles and putting the permissions on that, and not users is still a good practice...persons come and go, but roles define the access for a job.
today you need to add one specific user, but if that person gets promoted, leaves or is replaced by someone else, it's easier to add a persons to a role, than reverse engineer the permissions a user has, and then duplicate them on another user.
Lowell
June 7, 2011 at 5:19 pm
Lowell has explained it well, and I'd second his advice.
Just grant what you need to a role, and add users to a role. Even if it's one person, at some point that will change. Either it will be a different person, or it will be more people that need access.
June 7, 2011 at 5:22 pm
Lowell (6/7/2011)
Stefan Krzywicki (6/7/2011)
Thanks, I'll take a look at that. It is actually a single person I'm granting access to a single table. In the scenario I described, they shouldn't see any database except the one I specifically added them to?
yes, that is correct, if you don't add the login to a database as a user, they cannot access it. they cannot access anything in the database, until you add them to a role or do an expicit grant to the user, either.
the rule of thumb about adding roles and putting the permissions on that, and not users is still a good practice...persons come and go, but roles define the access for a job.
today you need to add one specific user, but if that person gets promoted, leaves or is replaced by someone else, it's easier to add a persons to a role, than reverse engineer the permissions a user has, and then duplicate them on another user.
Thanks. While this is a very specific situation where it is unlikely anyone else will need this specific permission you are correct that I shouldn't code to the situation. And doing it the way you suggest will get me used to doing it the right way for the future.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 7, 2011 at 5:23 pm
Steve Jones - SSC Editor (6/7/2011)
Lowell has explained it well, and I'd second his advice.Just grant what you need to a role, and add users to a role. Even if it's one person, at some point that will change. Either it will be a different person, or it will be more people that need access.
Thanks. The biggest problem I was having is that it looks like you're granting very wide permissions when you add a user.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 7, 2011 at 6:12 pm
Stefan one of the things i love to do is test the permissions... you don't need to login as them or anything.
for example, supposed add a user to a database... you can then do EXECUTE AS USER=?? and see what yo can touch...it's a great way to familiarize yourself with permissions.
USE Sandbox;
CREATE USER Noobie WITHOUT LOGIN;
USE Sandbox;
EXECUTE AS USER = 'Noobie';
--confirm my context has changed: who am i?
SELECT suser_name() --a number, since there's no login...S-1-9-3-2519517614-1132521308-3258241725-2477869342
USE master;
select * from sys.objects --can only see some spt values tables...
USE Sandbox;
select * from sys.objects --works! because being a USER allows you to see sys tables via the PUBLIC role. but wait a second...there's no tables....
--try to select from a table you know exists
select * from dbo.items
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Items', database 'SandBox', schema 'dbo'.
*/
--turn back into myself
REVERT;
Lowell
June 7, 2011 at 7:31 pm
Lowell (6/7/2011)
Stefan one of the things i love to do is test the permissions... you don't need to login as them or anything.for example, supposed add a user to a database... you can then do EXECUTE AS USER=?? and see what yo can touch...it's a great way to familiarize yourself with permissions.
USE Sandbox;
CREATE USER Noobie WITHOUT LOGIN;
USE Sandbox;
EXECUTE AS USER = 'Noobie';
--confirm my context has changed: who am i?
SELECT suser_name() --a number, since there's no login...S-1-9-3-2519517614-1132521308-3258241725-2477869342
USE master;
select * from sys.objects --can only see some spt values tables...
USE Sandbox;
select * from sys.objects --works! because being a USER allows you to see sys tables via the PUBLIC role. but wait a second...there's no tables....
--try to select from a table you know exists
select * from dbo.items
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Items', database 'SandBox', schema 'dbo'.
*/
--turn back into myself
REVERT;
Oh, that's great! Not being able to test was bugging the hell out of me.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply