Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Access question Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 1:09 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:50 AM
Points: 299, Visits: 1,129
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?
Post #1379499
Posted Wednesday, October 31, 2012 1:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:50 AM
Points: 299, Visits: 1,129
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.
Post #1379502
Posted Wednesday, October 31, 2012 9:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:02 AM
Points: 7,135, Visits: 12,744
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
Post #1379592
Posted Thursday, November 1, 2012 8:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:50 AM
Points: 299, Visits: 1,129
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?
Post #1379835
Posted Thursday, November 1, 2012 10:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:02 AM
Points: 7,135, Visits: 12,744
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
Post #1379907
Posted Thursday, November 1, 2012 1:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:50 AM
Points: 299, Visits: 1,129
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'
Post #1380018
Posted Thursday, November 1, 2012 2:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:02 AM
Points: 7,135, Visits: 12,744
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
Post #1380022
Posted Thursday, November 1, 2012 3:04 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:58 PM
Points: 367, Visits: 615
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.
Post #1380047
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse