Solved

CommServe SQL Server security settings (revoke public role)

  • 18 January 2022
  • 2 replies
  • 615 views

Badge +5

Hi Experts,

 

Our customer has conducted a security check, and there are points related to SQL Server on CommServe.
The issue is that the role of the SQL Server DBA account is set to Public.
224 public sp_MSalreadyhavegeneration
224 public sp_MSwritemergeperfcounter
224 public sp_replsetsyncstatus
224 public sp_replshowcmds
224 public sp_publishdb
224 public sp_addqueued_artinfo
224 public sp_replcounters
...and many more

 

Regarding CommServe security, I looked at the CommServe Hardening.pdf whitepaper document, but could not find anything related to public roles.
(Title: CommServe Component Security)


Is there any problem if I revoke this public role?
Or should these be kept public?

 

Best Regards
Kim KK

icon

Best answer by Prashanth Jayaram 19 January 2022, 07:45

View original

2 replies

Userlevel 1
Badge +1

Hi Kim KK,

 

Please see the details below:

“Public” Role in SQL Server is a unique role given by default to all logins. You cannot remove a login's membership of this role because this behavior is built into SQL Server. 

 

NOTE:

  1. Please be sure to document it before revoking the server level and database level permission.
  2. Get the list verified by the security team to test by revoking the permission. 

 

Additional details:

 

When a SQL Server login is created, the public role is assigned to the login and cannot be revoked. After creating the login, if that server principal is not granted or denied specific permissions on any securable object, the login will automatically inherit the permissions granted to the public role. 

 

--Server level

SELECT sp.state_desc as "Permission State", sp.permission_name as "Permission", 

sl.name "Principal Name",sp.class_desc AS "Object Class", ep.name "End Point"

FROM sys.server_permissions AS sp

JOIN sys.server_principals AS sl

ON sp.grantee_principal_id = sl.principal_id

LEFT JOIN sys.endpoints AS ep

ON sp.major_id = ep.endpoint_id WHERE sl.name = 'public';

 

--database level

 

 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 and USER_NAME(p.[grantee_principal_id])='public'

 

Sample code to revoke the SELECT permission

REVOKE SELECT on sys.columns TO public

 

Badge +5

Hi Prashanth,

Thank you very much for your kind and detailed explanation.

 

I am new to SQL Server, and it seems that I did not understand the role properly.

I will share your comments with the customer and partner SE, and discuss how the security measures might be appropriate.

 

Best Regards

Kim KK

Reply