[오라클] ROLE 생성부터 부여된 테이블 권한 조회까지 LISTAGG 활용하여 조회하는 VIEW

오라클 권한 관리

보통 프로젝트에서는 ROLE 관리를 잘 해야 DB 운영이 수월해진다.

 

유저, 테이블이 많기 때문에 유저에게 직접 테이블 권한을 할당하기 보다는

ROLE 을 만들고 각 USER 에게 ROLE을 부여한 후,

롤에게 테이블에 대한 권한을 주는게 관리에 용이하다. 

ROLE 과 유저 관계

(1) TBS 생성

(2) USER 생성 (이때 TBS QUOTOS 등 정의해준다.), 권한 부여 

-- 1,2 번은 앞선 게시글에 적어뒀음

 

(3) ROLE 생성 (CREATE ROLE)

create role 롤이름A;

(4) ROLE에 권한 할당 (GRANT)

grant 권한이름X, 권한이름Y, ... to 롤이름A;

--어려우니 코드로 보자면 테이블, 시퀀스 등에 권한을 주면 된다.
--EX) 
GRANT SELECT,INSERT,UPDATE,DELETE ON OWNER.FB_FRMLAND_DDD TO RL_ALL;
GRANT SELECT ON OWNER.FB_FRMLAND_DDD TO RL_SEL;

(5) 사용자에 ROLE 부여 (GRANT)

​사실 4,5 번 순서 바뀌어도 사오간 없음

grant 역할이름A to 사용자이름K;
-- 아래역시 예시
GRANT RL_ALL TO ABALL;
GRANT RL_SEL TO ABSEL;

(6) 사용자에게 부여된 ROLE 확인 (데이터 익셔너리 dba_role_privs 사용)

select * from dba_role_privs
where grantee='사용자이름K';

SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE ='BBDEV' 

-- 조회하면 BBDEV에게 부여된 롤을 확인할 수 있다. 
--반대로 
SELECT * 
  FROM DBA_ROLE_PRIVS
 WHERE GRANTED_ROLE = 'RL_ABALL' ;
 --이렇게 조회하면 이 롤을 가지고 있는 모든 USER 을 조회할 수 있다.

(7) 일반적으로 개발단계에서 롤에 권한을 부여할 때  SYNONYM을 같이 부여한다.

GRANT SELECT,INSERT,UPDATE,DELETE ON IGWOWN.FB_FRMLND_DDD TO RL_ALL;

CREATE OR REPLACE PUBLIC SYNONYM FB_FRMLND_DDD FOR OWNER.FB_FRMLND_DDD ;

 

그리고 중요한거 !!

롤이 테이블에 대해 가지고 있는 권한을 조회하고 싶은데

보통 SELECT,INSERT,UPDATE,DELETE가 따로따로 나와서 가독성이 좋지않다. 

SELECT *
  FROM DBA_TAB_PRIVS
 WHERE OWNER = 'OWNER' ; 

--위처럼 조회하면 진짜 보기 힘들다. 그래서 LISTAGG로 만들어준다! 

SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR
     , LISTAGG(PRIVILEGE,',') WITHIN GROUP(ORDER BY
                                            CASE WHEN PRIVILEGE = 'SELECT' THEN 1
                                                 WHEN PRIVILEGE = 'INSERT' THEN 2
                                                 WHEN PRIVILEGE = 'UPDATE' THEN 3
                                            ELSE 4
                                            END
                                            ) AS "PRIVILEGE"
  FROM DBA_TAB_PRIVS
 WHERE 1=1
   AND TABLE_NAME NOT LIKE 'BIN%'
 GROUP BY GRANTEE, OWNER, TABLE_NAME, GRANTOR
 ORDER BY TABLE_NAME
        , CASE WHEN GRANTEE LIKE '%ALL' THEN 1
          ELSE 2
           END 
        , GRANTEE;

 

위 쿼리로 CREATE VIEW 해주면 가독성 좋은 테이블에 부여된 롤 권한을 보다 쉽게 볼 수 있다 ~

CREATE VIEW VW_DBA_TAB_PRIVS
AS
SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR
     , LISTAGG(PRIVILEGE,',') WITHIN GROUP(ORDER BY
                                            CASE WHEN PRIVILEGE = 'SELECT' THEN 1
                                                 WHEN PRIVILEGE = 'INSERT' THEN 2
                                                 WHEN PRIVILEGE = 'UPDATE' THEN 3
                                            ELSE 4
                                            END
                                            ) AS "PRIVILEGE"
  FROM DBA_TAB_PRIVS
 WHERE 1=1
   AND TABLE_NAME NOT LIKE 'BIN%'
 GROUP BY GRANTEE, OWNER, TABLE_NAME, GRANTOR
 ORDER BY TABLE_NAME
        , CASE WHEN GRANTEE LIKE '%ALL' THEN 1
          ELSE 2
           END 
        , GRANTEE;