February 18, 2013 at 8:25 am
Per my company's security policy, I need to revoke permissions to the public and guest roles to all objects. In a user database, I'm running this query to report current permissions:
SELECT
u.name AS [Principal]
, o.name AS [Object]
, p.permission_name AS [Permission]
, p.state_desc AS [State]
, schema_name(o.schema_id) AS [SchemaName]
, u.principal_id
FROM sys.database_principals u
INNER JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
INNER JOIN sys.all_objects o ON p.major_id = o.object_id
WHERE 1 = 1
AND p.state_desc <> 'DENY'
AND u.name IN ('guest', 'public');
The output of this reports that the public role has permissions to 17 INFORMATION_SCHEMA objects so I run queries like the following to revoke these:
USE [master] REVOKE SELECT ON INFORMATION_SCHEMA.[CHECK_CONSTRAINTS] TO public;
USE [master] REVOKE SELECT ON INFORMATION_SCHEMA.[COLUMN_DOMAIN_USAGE] TO public;
--etc.
I've also tried changing the REVOKEs to DENYs.
When I run the finding query again, I get the same results. If I create a SQL user with no permissions and drop it in the database, I can't actually query the view. This fails with a 229 error "SELECT permission was denied":
USE <UserDatabase>
GO
SELECT *
FROM [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS];
I'm about to write up a policy exception for this but wanted to see if anyone had any ideas first. A little history is that this database started out on SQL 7 and has migrated to SQL 2000, 2005, and now 2008 R2 during its lifetime. I'm wondering if there's some junk left over from the SQL 7 days? No other database has this issue for us.
February 18, 2013 at 2:33 pm
What you're experiencing is one of the frustrating pieces of Ownership Chaining. A DENY is not respected when an Ownership Chain is in play because only the highest chain in the link has its permissions checked.
This worked for me:
USE YourDatabase; DENY SELECT ON sys.[check_constraints] TO public;
-- note that this affects all users in all databases who only have public membership into master, i.e. mapped via the guest user
USE master; DENY SELECT ON INFORMATION_SCHEMA.check_constraints TO public;
And this puts the furniture back:
USE YourDatabase; REVOKE SELECT ON sys.[check_constraints] TO public;
USE master; GRANT SELECT ON INFORMATION_SCHEMA.check_constraints TO public;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 19, 2013 at 8:12 am
Great ideas but unfortunately they did not resolve my particular issue. After running with your initial advice with no luck I tried extending your DENY commands to sys.[default_constraints], sys.[key_constraints], sys.[foreign_keys], and dbo.[sysconstraints]. I know this was grasping at straws but my thinking here was the sys schema views replace the old dbo sysconstraints table so why not try to deny on everything related?
In the end, I'm still stuck with these INFORMATION_SCHEMA objects reporting (but in effect not having) SELECT permissions to the public role:
CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
REFERENTIAL_CONSTRAINTS
SCHEMATA
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS
One curious thing is that these three other INFORMATION_SCHEMA objects are reporting correctly for me:
PARAMETERS
ROUTINE_COLUMNS
ROUTINES
February 19, 2013 at 9:06 am
Shawn Smith (2/19/2013)
Great ideas but unfortunately they did not resolve my particular issue. After running with your initial advice with no luck I tried extending your DENY commands to sys.[default_constraints], sys.[key_constraints], sys.[foreign_keys], and dbo.[sysconstraints]. I know this was grasping at straws but my thinking here was the sys schema views replace the old dbo sysconstraints table so why not try to deny on everything related?In the end, I'm still stuck with these INFORMATION_SCHEMA objects reporting (but in effect not having) SELECT permissions to the public role:
CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
REFERENTIAL_CONSTRAINTS
SCHEMATA
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS
One curious thing is that these three other INFORMATION_SCHEMA objects are reporting correctly for me:
PARAMETERS
ROUTINE_COLUMNS
ROUTINES
Not sure what you mean by "no luck."
When I create a new database with a new user with no additional permissions and run the two DENY statements I provided the new user can no longer select from the two views.
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'check_constraints', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Line 4
The SELECT permission was denied on the object 'CHECK_CONSTRAINTS', database 'mssqlsystemresource', schema 'INFORMATION_SCHEMA'.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 19, 2013 at 10:02 am
I mean that my finding query still reports that the public role still has SELECT permissions to the objects which were denied/revoked. In effect, the public role does not actually have these permissions and I have proven this on my system (I get similar errors to you when performing the SELECTs). So, it seems that the query is reporting permissions incorrectly. I don't have this issue with revoking permissions with any other database and can't recreate it with a new database.
February 19, 2013 at 11:05 am
i guess the question is how are you testing the public permissions then?
did you create a new database user , unconnected form any login like this?
are you using execute as? could the user you test against also have sysadmin login priviledges?
--create a test user in our specific database.
CREATE USER [ClarkKent] WITHOUT LOGIN--make sure I'm in the correct database
--test:
EXECUTE AS USER='ClarkKent'
--who am i?
select suser_name()
--what kind of permissions do i have?
--for 2008 and above:
--the auditing snippet below works fine in a
--login trigger,
--database trigger
--or any stored procedure.
SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
CURRENT_USER AS CurrentUser,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
--these params were introduced in SQl2008
ConnectionProperty('net_transport') AS 'net_transport',
ConnectionProperty('protocol_type') AS 'protocol_type',
ConnectionProperty('auth_scheme') AS 'auth_scheme',
ConnectionProperty('local_net_address') AS 'local_net_address',
ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
ConnectionProperty('client_net_address') AS 'client_net_address',
ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
--this database permissions?
SELECT
*
FROM sys.database_permissions permz
INNER JOIN sys.database_principals userz
ON permz.grantee_principal_id = userz.principal_id
WHERE userz.principal_id IN( --the userid and also all the roles i'm directly in.
--that doesn't seem to get the role-within-a-role info those
SELECT
USER_ID() AS id
UNION ALL
SELECT
rolezx.role_principal_id
FROM sys.database_principals userzx
LEFT OUTER JOIN sys.database_role_members rolezx
ON userzx.principal_id = rolezx.member_principal_id
LEFT OUTER JOIN sys.database_principals decripz
ON rolezx.role_principal_id = decripz.principal_id
WHERE userzx.name = USER_NAME())
--do stuff
--change back into superman
REVERT;
Lowell
February 19, 2013 at 11:12 am
I can duplicate what you're seeing with your audit query...it shows public still has GRANT S on sys.check_constraints for me but again in practice users that are only in public cannot select from it, they get permission denied. It's odd, really. Possibly a bug.
edit: had reply screen up for a long while at same time Lowell was replying so didn't see that before replying, reading that now...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 19, 2013 at 12:25 pm
I had tested it by creating a new login and then creating a new user in the database with no permissions (all public permissions except for the ones I'm dealing with have already been revoked):
USE [master];
CREATE LOGIN [ClarkKent2] WITH PASSWORD=N'<SomeSecurePassword>';
USE [MyDb];
CREATE USER [ClarkKent2] FOR LOGIN [ClarkKent2];
Then I connect to a new query window with the new user and execute the following getting a 229 "SELECT permission was denied...":
USE MyDb;
SELECT *
FROM [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS];
I also just took your suggestions and after first granting SELECT permissions to public for sys.database_permissions, sys.database_principals, and sys.database_role_members - your 2nd query returned a single row with a GRANT CONNECT DATABASE for ClarkKent. If I run your query as the ClarkKent2 user I created, I get back the same GRANT CONNECT DATABASE single row result.
If I run the finding query again, I still report the 17 INFORMATION_SCHEMA objects.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply