[오라클] 특정 사용자에 주어진 권한 보기

SELECT USERNAME, ROLENAME, PRIVILEGE 
FROM (SELECT DECODE(SA1.GRANTEE#, 1, 'PUBLIC', U1.NAME) username, 
SUBSTR(U2.NAME,1,20) rolename, 
SUBSTR(SPM.NAME,1,27) PRIVILEGE 
FROM SYS.SYSAUTH$ SA1, 
SYS.SYSAUTH$ SA2, 
SYS.USER$ U1, 
SYS.USER$ U2, 
SYS.SYSTEM_PRIVILEGE_MAP SPM 
WHERE SA1.GRANTEE# = U1.USER# 
AND SA1.PRIVILEGE# = U2.USER# 
AND U2.USER# = SA2.GRANTEE# 
AND SA2.PRIVILEGE# = SPM.PRIVILEGE 
UNION 
SELECT U.NAME username, NULL rolename, SUBSTR(SPM.NAME,1,27) privilege 
FROM SYS.SYSTEM_PRIVILEGE_MAP SPM, SYS.SYSAUTH$ SA, SYS.USER$ U 
WHERE SA.GRANTEE#=U.USER# 
AND SA.PRIVILEGE#=SPM.PRIVILEGE) 
WHERE USERNAME = 'KDY';

 

'KDY' ===>> 사용자 ID 이다.

 

결과는 :

USERNAME                       ROLENAME                                 PRIVILEGE                                              
------------------------------ ---------------------------------------- ------------------------------------------------------ 
KDY                            RESOURCE                                 CREATE CLUSTER                                         
KDY                            RESOURCE                                 CREATE INDEXTYPE                                       
KDY                            RESOURCE                                 CREATE OPERATOR                                        
KDY                            RESOURCE                                 CREATE PROCEDURE                                       
KDY                            RESOURCE                                 CREATE SEQUENCE                                        
KDY                            RESOURCE                                 CREATE TABLE                                           
KDY                            RESOURCE                                 CREATE TRIGGER                                         
KDY                            RESOURCE                                 CREATE TYPE                                            
KDY                                                                     CREATE SESSION                                         
KDY                                                                     UNLIMITED TABLESPACE                                  

10 rows selected

 

이런식으로 나온다. 주어진 ROLE 과 PRIVILEGE 가 나온다.

 

=========================================================================

 

부여된 권한 확인

●부여받거나 부여한 권한을 확인하기 위해 Dictionary뷰를 액세스한다.

 

Dictionary뷰                                  설명

ROLE_SYS_PRIVS              role에 부여된 시스템 권한

ROLE_TAB_PRIVS             role에 부여된 테이블 권한

USER_ROLE_PRIVS           사용자가 액세스할 수 있는 role

USER_TAB_PRIVS_MADE  사용자가 부여한 객체 권한

USER_TAB_PRIVS_RECD   사용자에게 부여된 객체 권한

USER_COL_PRIVS_RECD  특정 Column에 대하여 사용자에게  부여된 객체 권한