public Role에 부여된 권한 확인

public role을 확인 하는 이유

Login을 생성하고 데이터베이스에 User를 생성하게 되면 기본적으로 public Role을 가지게 됩니다.

생각보다 public Role에 많은 권한들이 부여가 되어 있고 기본적으로 주어지는 Role이다 보니 의도치 않게 권한을 주게 되는 경우가 생깁니다.

 

public role에 속한 권한 확인

public role에 어떠한 권한들이 들어가는지 확인을 해보도록 하겠습니다.

DB > Security > Roles > Database Roles > public 속성을 보면 아래와 같이 많은 권한들이 부여된 것을 알 수 있습니다.

보안상 위험한 권한들은 제거를 해야 합니다.

스크립트를 통해 조회가 가능하고 해당 쿼리의 정보를 이용하여 특정 오브젝트의 권한이 부여되었는지 확인하고 제거를 할 수 있습니다.

SELECT
obj.name AS [ObjectName],
schema_name(obj.schema_id) AS [ObjectSchema],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN 'R' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN 'A' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [ObjectClass],
obj.type AS [ObjectType],
0 AS [IsTableType]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.all_objects AS obj ON obj.object_id = prmssn.major_id and prmssn.class = 1
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(grantee_principal.name='public')
UNION
SELECT
obj.name AS [ObjectName],
null AS [ObjectSchema],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN 'R' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN 'A' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [ObjectClass],
null AS [ObjectType],
0 AS [IsTableType]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.schemas AS obj ON obj.schema_id = prmssn.major_id and prmssn.class = 3
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(grantee_principal.name='public')
UNION
SELECT
obj.name AS [ObjectName],
null AS [ObjectSchema],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN 'R' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN 'A' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [ObjectClass],
null AS [ObjectType],
0 AS [IsTableType]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.database_principals AS obj ON obj.principal_id = prmssn.major_id and prmssn.class = 4
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(grantee_principal.name='public')
UNION
SELECT
obj.name AS [ObjectName],
null AS [ObjectSchema],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN 'R' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN 'A' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [ObjectClass],
null AS [ObjectType],
0 AS [IsTableType]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.assemblies AS obj ON obj.assembly_id = prmssn.major_id and prmssn.class = 5
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(grantee_principal.name='public')
UNION
SELECT
obj.name AS [ObjectName],
SCHEMA_NAME(obj.schema_id) AS [ObjectSchema],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN 'R' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN 'A' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [ObjectClass],
null AS [ObjectType],
obj.is_table_type AS [IsTableType]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.types AS obj ON obj.user_type_id = prmssn.major_id and prmssn.class = 6
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(grantee_principal.name='public')
UNION
SELECT
obj.name AS [ObjectName],
null AS [ObjectSchema],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN 'R' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN 'A' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [ObjectClass],
null AS [ObjectType],
0 AS [IsTableType]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.fulltext_catalogs AS obj ON obj.fulltext_catalog_id = prmssn.major_id and prmssn.class = 23
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id