April 15, 2018 at 12:45 am
Dear Everyone
I was asked by the CISO to perform the following task:
1. Deny access to security catalog views to unprivileged accounts. Grant access to security catalog views to administrators only.
If i am understanding this correctly only sysadmins should be able to view security catalog views which are found here:
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/security-catalog-views-transact-sql?view=sql-server-2017
Do i need to remove permissions from the PUBLIC role?
I dont know where to start here
Any recommendations?
kal
April 15, 2018 at 3:51 am
From that page:
Permissions
The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission.
Unless you have explicitly granted permissions to public, it will have no permissions, and hence removing it would have no effect, and having it there will have no effect.
Maybe start by asking the CISO if he knows what he's even doing, considering some of the previous requests. Or, just what he wants changed from the current behaviour (which is only sysadmins will have access to anything other than their own details unless explicitly granted otherwise)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2018 at 12:52 am
Thanks Gail
So we would look at the public role permissions below:
SELECT sp.state_desc, sp.permission_name, sp.class_desc, sp.major_id, sp.minor_id, e.name
FROM sys.server_permissions AS sp
JOIN sys.server_principals AS l
ON sp.grantee_principal_id = l.principal_id
LEFT JOIN sys.endpoints AS e
ON sp.major_id = e.endpoint_id
WHERE l.name = 'public';
state_desc permission_name class_desc major_id minor_id name
GRANT VIEW ANY DATABASE SERVER 0 0 NULL
GRANT CONNECT ENDPOINT 2 0 TSQL Local Machine
GRANT CONNECT ENDPOINT 3 0 TSQL Named Pipes
GRANT CONNECT ENDPOINT 4 0 TSQL Default TCP
GRANT CONNECT ENDPOINT 5 0 TSQL Default VIA
Do i need to remove the connect to endpoints? I dont see this as a security risk.
Which permissions are usually revoked from the PUBLIC role besides the following as a best practice:
| use master go revoke execute on [sp_MSSetServerProperties] to [public] go revoke execute on [sp_MSsetalertinfo] to [public] go |
Kal
April 16, 2018 at 12:59 am
Wow i forgot to mention that was for the server permissions but for the database permissions it gets really complicated!!
Are there any set permissions to remove from the database permissions? The public role has well over 2097 permissions on SQL Server 2016.
What do you propose?
Kal
April 16, 2018 at 1:37 am
hurricaneDBA - Monday, April 16, 2018 12:52 AM.
Which permissions are usually revoked from the PUBLIC role besides the following as a best practice:
Probably none, because it has no effective permissions. Yes, it's got stuff granted to it, but login with a user that has no roles and no granted permissions and see what you can do and see. It's almost nothing.
Public has, for example, select granted on sys.objects, but if you query it, you'll only see objects to which you have permissions granted, so the user with no roles and no permissions sees an empty resultset. The granted permission just means that the user won't get an error. It doesn't mean they'll see all objects. They won't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2018 at 1:39 am
btw, I see 176 permissions granted to Public in a new SQL 2017 database. Not >2000. Could be that someone's granted extra permissions to public in your DB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2018 at 1:50 am
I ran the following query and i ask if you can also to find the database permissions granted to the public role and see the result below:
USE master;
GO
SELECT sp.state_desc, sp.permission_name, SCHEMA_NAME(o.schema_id) AS 'Schema', o.name
FROM sys.database_permissions sp
LEFT JOIN sys.all_objects o
ON sp.major_id = o.object_id
JOIN sys.database_principals u
ON sp.grantee_principal_id = u.principal_id
WHERE u.name = 'public'
AND o.name IS NOT NULL
ORDER BY o.name
Some of the results which could be troublesome:
GRANT EXECUTE sys sp_srvrolepermission
GRANT EXECUTE sys sp_sqlagent_start_job
GRANT EXECUTE sys sp_sqlagent_stop_job
GRANT EXECUTE sys sp_revokedbaccess
etc
This is a default 2016 SP1 CU7 installation so nothing was added at all.
It seems weird all these items were added.
Right?
Kal
April 16, 2018 at 1:59 am
hurricaneDBA - Monday, April 16, 2018 1:50 AMSome of the results which could be troublesome:GRANT EXECUTE sys sp_srvrolepermission
GRANT EXECUTE sys sp_sqlagent_start_job
GRANT EXECUTE sys sp_sqlagent_stop_job
GRANT EXECUTE sys sp_revokedbaccess
Create yourself a user with no permissions and see what happens when you actually run those.
If you're looking at database permissions in master, yes there will be more, but again the metadata is protected, you can only access something that you have additional rights to.
The 176 was for a user database
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2018 at 2:28 am
Ahhhh yes i didnt read the 'USER' database; anyways if i run it against one of the production databases i get:
GRANT EXECUTE dbo sp_alterdiagram
GRANT EXECUTE dbo sp_creatediagram
GRANT EXECUTE dbo sp_dropdiagram
GRANT EXECUTE dbo sp_helpdiagramdefinition
GRANT EXECUTE dbo sp_helpdiagrams
GRANT EXECUTE dbo sp_renamediagram
As these are only database specific its ok to keep them. Correct?
Kal
April 16, 2018 at 2:56 am
Gail
I found this in another database:
GRANT SELECT public dbo objects USER_TABLE GRANT
Having select on objects isnt safe as the user can see all objects. Correct so this one we should remove?
Right?
Kal
April 16, 2018 at 7:41 am
That's not the system view sys.objects. Whatever that it, it's in the dbo schema and it's a user table. So, no idea, it's something of yours.Check with your developers, see what it is decide on permissions based on whatever it is used for.
And, once more with feeling, even if select is granted on sys.objects (as in the system view) and it is granted by default, you can only access something that you have additional rights to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply