次のSELECT文は、それぞれのロールに付与されたすべての権限を返します。
SELECT CAST(RoleName as CHAR(16)) as RoleName,
CAST(DatabaseName as CHAR(15)) as Databases,
CAST(TableName as CHAR(15)) as TVMs,
CAST(AccessRight as CHAR(5)) as AccRights,
CAST(GrantorName as CHAR(15)) as Grantor
FROM DBC.AllRoleRightsV
WHERE RoleName like 'roles017%'
ORDER BY 1,2,3,5;
結果:
RoleName Databases TVMs AccRights Grantor ---------------- --------------- --------------- --------- --------------- roles017_dbc_r1b roles017_3_db roles017_3_m3 E roles017_3 roles017_r1a roles017_3_db roles017_3_t1 R roles017_3 roles017_r1c roles017_3_db roles017_3_t4 R monthly roles017_r1f roles017_3_db roles017_3_v2 R roles017_3 roles017_r2d roles017_3_db roles017_3_v2 R roles017_3 roles017_r2e roles017_3_db roles017_3_t5 R monthly