PUBLIC server role vs PUBLIC database role

Question

PUBLIC server role vs PUBLIC database role

hurricaneDBA

SSCertifiable

Points: 5027
More actions
April 17, 2018 at 3:30 am

#330392

Dear Everyone
I am conducting a database cleanup exercise to remove all unnecessary privileges from the PUBLIC role per the link:
http://www.sqlservercentral.com/articles/Security/sqlpermissionspublicrole/116/
I have one basic question for the public role per database. If you grant permissions to the public role in the server role will any users / logins being added to the server security be able to access the database where this permission is applied?
Example
I grant select on table1 for the public role in DB1
This public role means the server role or the database public role?
This is my confusion as i dont know if this is implemented on the server PUBLIC role or the database public role.
Thanks
Kal

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Database level permissions cannot be granted to a server role.
Server roles get the server-level permissions (VIEW SERVER STATE, ALTER ANY LOGIN, etc)
Database roles get database level permissions (SELECT, DELETE, etc)

I strongly recommend not granting any permissions to PUBLIC. If you have permissions that all users of a DB should have, create a custom role, grant permissions to that and assign all users to that role

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 2

Hi Gail
So when i check under a database i see the following permissions given to the database public role:

These are the default permissions and you recommend we remove all these permissions?
Please confirm
kal

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I have said, multiple times now, that I do not suggest removing permissions from public. I also don't recommend adding any permissions to public. Rather add permissions to user-defined roles and assign those as necessary.

As I have said, multiple time now, those permissions on public DO NOT allow users to actually see the objects unless they have some other rights granted on that object. The granting of select on sys.objects just means that a user won't get SELECT PERMISSION DENIED when they try to query it. It DOES NOT mean that they can see every object in the database. They cannot.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 4

Noted Gail
I will keep the public role as is and create custom database roles and assign permissions to them accordingly
Have a good day
Kal

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Create yourself a new login, add DB users and grant them absolutely no permissions, and then log in and see how visible the various metadata and security information is. That'll make it easier for you to discuss required changes with the security people, if you can show them what the current state is.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Have you tried creating a new login and testing some of this as Gail has recommended a few times?You really should try it as it's a good way to see the public permissions with the defaults. If you can do SQL logins for a test, you can use the following - change it to windows if you need to:

CREATE LOGIN SomeTestLogin WITH PASSWORD=N'thisisapassword', DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO

EXECUTE AS LOGIN = 'SomeTestLogin' --execute as the new login, default permissions EXEC sp_who2 REVERT;

--execute as yourself EXEC sp_who2 GO

DROP LOGIN SomeTestLogin

sp_who2 is a good one to run back to back with your permissions vs the login with default permissions. Public can execute sp_who2 by default. The new login will only see their own process not all process as a sysadmin would. Just because they can execute that stored procedure or select from a view doesn't mean they see "everything".

Sue

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 7

Dear Sue / Gail
I tested the query and i see now how the permissions work

Out of curiousity i am trying to find all permissions being assigned to the PUBLIC database role and i was using the query below from this site:
https://basitaalishan.com/tag/query-to-find-permission-assigned-to-public-role/

USE [CtxDOHA-CORALSite] -- Specify database name

GO

WITH [PublicRoleDBPermissions]

AS (

SELECT p.[state_desc] AS [PermissionType]

,p.[permission_name] AS [PermissionName]

,USER_NAME(p.[grantee_principal_id]) AS [DatabaseRole]

,CASE p.[class]

WHEN 0

THEN 'Database::' + DB_NAME()

WHEN 1

THEN OBJECT_NAME(major_id)

WHEN 3

THEN 'Schema::' + SCHEMA_NAME(p.[major_id])

END AS [ObjectName]

FROM [sys].[database_permissions] p

WHERE p.[class] IN (0, 1, 3)

AND p.[minor_id] = 0

)

SELECT [PermissionType]

,[PermissionName]

,[DatabaseRole]

,SCHEMA_NAME(o.[schema_id]) AS [ObjectSchema]

,[ObjectName]

,o.[type_desc] AS [ObjectType]

,[PermissionType] + ' ' + [PermissionName] + ' ON ' + QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME([ObjectName]) + ' TO ' + QUOTENAME([DatabaseRole]) AS [GrantPermissionTSQL]

,'REVOKE' + ' ' + [PermissionName] + ' ON ' + QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME([ObjectName]) + ' TO ' + QUOTENAME([DatabaseRole]) AS [RevokePermissionTSQL]

FROM [PublicRoleDBPermissions] p

INNER JOIN [sys].[objects] o

ON o.[name] = p.[ObjectName]

AND OBJECTPROPERTY(o.object_id, 'IsMSShipped') = 0

WHERE [DatabaseRole] = 'Public'

ORDER BY [DatabaseRole]

,[ObjectName]

,[ObjectType]

But for some reason i think the query in the top is misleading as it gives that the PUBLIC role has permissions but when i check on SSMS in the securables section the items arent there
What do you think?
Kal

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 8

I found a query which works from the site:
https://www.databasejournal.com/features/mssql/article.php/1478701/SQL-Permissions-The-Public-Role.htm

select count(*) as PublicPermissionCount from sysprotects P inner join sysusers U on P.Uid = U.UID inner join sysobjects O on P.ID=O.ID where P.uid=0 and o.Type<>'S' and o.Name not like 'sys%' and O.Name not like 'sync%'

I modified it to get the actual object names

select o.name as PublicPermissionCount from sysprotects P inner join sysusers U on P.Uid = U.UID inner join sysobjects O on P.ID=O.ID where P.uid=0 and o.Type<>'S' and o.Name not like 'sys%' and O.Name not like 'sync%'

Hope this helps others like it helped me out
Kal

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

hurricaneDBA - Wednesday, April 18, 2018 7:24 AM
select count(*) as PublicPermissionCount from sysprotects P inner join sysusers U on P.Uid = U.UID inner join sysobjects O on P.ID=O.ID where P.uid=0 and o.Type<>'S' and o.Name not like 'sys%' and O.Name not like 'sync%'

No, please don't use that. It's for SQL 2000 and before (using the old sys tables), and it's going to be missing every new thing added since that.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 10

Thanks Gail
I found a newer version below but the result sets dont match for the master, msdb databases with the old query.
Is that normal?

SELECT DISTINCT rp.name,

ObjectType = rp.type_desc,

PermissionType = pm.class_desc,

pm.permission_name,

pm.state_desc,

ObjectType = CASE

WHEN obj.type_desc IS NULL

OR obj.type_desc = 'SYSTEM_TABLE' THEN

pm.class_desc

ELSE obj.type_desc

END,

s.Name as SchemaName,

[ObjectName] = Isnull(ss.name, Object_name(pm.major_id))

FROM sys.database_principals rp

INNER JOIN sys.database_permissions pm

ON pm.grantee_principal_id = rp.principal_id

LEFT JOIN sys.schemas ss

ON pm.major_id = ss.schema_id

LEFT JOIN sys.objects obj

ON pm.[major_id] = obj.[object_id]

LEFT JOIN sys.schemas s

ON s.schema_id = obj.schema_id

WHERE rp.type_desc = 'DATABASE_ROLE'

AND pm.class_desc <> 'DATABASE'

and obj.type <> 'S'

ORDER BY rp.name,

rp.type_desc,

pm.class_desc

Kal

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

You're going to have to figure out where the differences come from yourself. Look at the queries, look at the source data, trace down where the differences originate. I haven't dug into those system views that much.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass