October 31, 2012 at 1:09 pm
The default access of all new employees of our company is db_datareader but how do we deny access to particular column(for ex. SSN) of tables by default?
October 31, 2012 at 1:26 pm
I can think of DENY SELECT but not sure how to implement it. I'd like to accomplish DENY SELECT to everyone except to the members of the approved group.
October 31, 2012 at 9:06 pm
sunny.tjk (10/31/2012)
I can think of DENY SELECT but not sure how to implement it. I'd like to accomplish DENY SELECT to everyone except to the members of the approved group.
Here is one option:
1. Create a new Database Role named db_CompanyNameDefaultReader where CompanyName is your particular company name.
2. Add db_CompanyNameDefaultReader to db_datareader using sys.sp_addrolemember.
3. Add all relevant members of db_datareader to the new role db_CompanyNameDefaultReader again using sys.sp_addrolemember.
4. Remove all people now in db_CompanyNameDefaultReader from db_datareader using sys.sp_droprolemember.
5. Deny select on the columns in question from db_CompanyNameDefaultReader using the following DDL:
DENY SELECT ON OBJECT::[dbo].[TheTableName]([TheColumnName]) TO [db_CompanyNameDefaultReader];
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 1, 2012 at 8:15 am
opc.three (10/31/2012)
sunny.tjk (10/31/2012)
I can think of DENY SELECT but not sure how to implement it. I'd like to accomplish DENY SELECT to everyone except to the members of the approved group.Here is one option:
1. Create a new Database Role named db_CompanyNameDefaultReader where CompanyName is your particular company name.
2. Add db_CompanyNameDefaultReader to db_datareader using sys.sp_addrolemember.
3. Add all relevant members of db_datareader to the new role db_CompanyNameDefaultReader again using sys.sp_addrolemember.
4. Remove all people now in db_CompanyNameDefaultReader from db_datareader using sys.sp_droprolemember.
5. Deny select on the columns in question from db_CompanyNameDefaultReader using the following DDL:
DENY SELECT ON OBJECT::[dbo].[TheTableName]([TheColumnName]) TO [db_CompanyNameDefaultReader];
Great idea opc.Three!!
But will it still work if a new user is added to db_datareader role?
November 1, 2012 at 10:00 am
Thanks. In the approach I detailed, if a new user is added to the db_datareader Fixed Database Role they will have permission to select the restricted column from your table unless they are also a member of the db_CompanyNameDataReader User-defined Database Role in which case the DENY permission for db_CompanyNameDataReader User-defined Database Role will take precedence over the GRANT afforded to the db_datareader Fixed Database Role. In short, it is a best practice to try and make Database Users directly a member of one Role if possible to keep things less confusing to manage.
As another general rule I would also suggest you avoid adding a Database User directly to any of the Fixed Database Roles like db_datareader. I prefer to only allow users to be part of User-defined Database Roles. In the rare case where a Database User or set of Users is justified in having db_datareader permissions I will add the User-defined Database Role they belong to as a member of the db_datareader Fixed Database Role.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 1, 2012 at 1:57 pm
opc.three (10/31/2012)
sunny.tjk (10/31/2012)
I can think of DENY SELECT but not sure how to implement it. I'd like to accomplish DENY SELECT to everyone except to the members of the approved group.Here is one option:
1. Create a new Database Role named db_CompanyNameDefaultReader where CompanyName is your particular company name.
2. Add db_CompanyNameDefaultReader to db_datareader using sys.sp_addrolemember.
3. Add all relevant members of db_datareader to the new role db_CompanyNameDefaultReader again using sys.sp_addrolemember.
4. Remove all people now in db_CompanyNameDefaultReader from db_datareader using sys.sp_droprolemember.
5. Deny select on the columns in question from db_CompanyNameDefaultReader using the following DDL:
DENY SELECT ON OBJECT::[dbo].[TheTableName]([TheColumnName]) TO [db_CompanyNameDefaultReader];
I tried to implement this on my local but I received an error saying "Cannot use the special principal 'db_datareader'." when I executed the following code:
EXEC sp_addrolemember 'SSN_GRANTED', 'db_datareader'
November 1, 2012 at 2:05 pm
sunny.tjk (11/1/2012)
opc.three (10/31/2012)
sunny.tjk (10/31/2012)
I can think of DENY SELECT but not sure how to implement it. I'd like to accomplish DENY SELECT to everyone except to the members of the approved group.Here is one option:
1. Create a new Database Role named db_CompanyNameDefaultReader where CompanyName is your particular company name.
2. Add db_CompanyNameDefaultReader to db_datareader using sys.sp_addrolemember.
3. Add all relevant members of db_datareader to the new role db_CompanyNameDefaultReader again using sys.sp_addrolemember.
4. Remove all people now in db_CompanyNameDefaultReader from db_datareader using sys.sp_droprolemember.
5. Deny select on the columns in question from db_CompanyNameDefaultReader using the following DDL:
DENY SELECT ON OBJECT::[dbo].[TheTableName]([TheColumnName]) TO [db_CompanyNameDefaultReader];I tried to implement this on my local but I received an error saying "Cannot use the special principal 'db_datareader'." when I executed the following code:
EXEC sp_addrolemember 'SSN_GRANTED', 'db_datareader'
You do not want to add db_datareader to SSN_GRANTED, you want the opposite.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 1, 2012 at 3:04 pm
How about creating a view with all columns in the table except those you want to hide. Then give read permission ONLY to those views.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply