오라클 User Migration을 위해 User 생성 스크립트 추출하는 방법

Oracle DBA 업무를 하다 보면 특정 DB에서 사용하는 User를 다른 DB에서 동일하게 생성해야 할 경우가 있는데, 그럴땐 Oracle의 DBMS_METADATA 패키지를 활용하면 된다.

쿼리 1: 테이블스페이스 생성

 

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('TABLESPACE', TABLESPACE_NAME)) FROM DBA_SEGMENTS
WHERE OWNER IN ('username')
GROUP BY TABLESPACE_NAME;

이 쿼리는 특정 사용자의 테이블스페이스를 생성하는 SQL 문이다. 
DBA_SEGMENTS에서 해당 사용자의 테이블스페이스에 대한 DDL 문을 생성하기 위해 DBMS_METADATA.GET_DDL 함수를 사용한다. 
해당 쿼리의 결과로 특정 유저가 사용하는 테이블스페이스의 DDL 문을 추출 할 수 있다.

 

쿼리 2: 사용자 생성

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('USER', USERNAME)) FROM DUAL;

이 쿼리는 특정 사용자를 생성하는 SQL 문이다. 해당 사용자에 대한 DDL 문을 생성하기 위해 DBMS_METADATA.GET_DDL 함수를 사용.
결과는 해당 사용자를 생성하는 DDL SQL문을 추출 할 수 있다.

쿼리 3: 역할 수준 권한 부여

SELECT TO_CHAR(DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME)) FROM DBA_USERS
WHERE USERNAME IN ('username');

 

이 쿼리는 특정 사용자 권한을 동일하게 부여하는 GRANT SQL 문을 가져옵니다. 
DBMS_METADATA.GET_GRANTED_DDL 함수를 사용하여 해당 사용자에게 Role을 부여하는 DDL 문을 생성한다.

 

쿼리 4: 시스템 권한 부여

SELECT GRANTEE, PRIVILEGE, 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || ';' AS GRANT_SQL
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN ('username');

 

이 쿼리는 사용자에게 시스템 권한을 부여하는 SQL 문이다. 
DBA_SYS_PRIVS 뷰에서 권한을 받은 사용자, 권한을 추출하고 해당 권한을 부여하는 SQL문을 생성한다.

쿼리 5: 역할 권한 부여

SELECT GRANTEE, GRANTED_ROLE, 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || ';' AS GRANT_SQL
FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN ('username');

 

이 쿼리는 특정 사용자에게 Role을 부여하는 SQL 문을 생성한다. 
DBA_ROLE_PRIVS에서 해당 사용자에게 부여된 Role과 해당 ROle을 부여하는 SQL 문을 생성한다. 

쿼리 6: 테이블 권한 부여

SELECT GRANTEE, OWNER, TABLE_NAME, 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME ||
' TO ' || GRANTEE || ';' AS GRANT_SQL
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN ('username')
AND OWNER = 'SYS';

 

이 쿼리는 특정 사용자에게 테이블 권한을 부여하는 SQL 문이다. 
DBA_TAB_PRIVS에서 해당 사용자에게 부여된 테이블 권한과 함께 테이블에 대한 권한을 부여하는 SQL 문을 생성한다. 

 

 

오라클에는 다양한 User Migration 방법이 있다. 그중에서 직접 DDL을 추출하여 계정을 생성하는 방법을 알아 보았고 Datapump를 사용해서 User의 DDL 만 추출 하는 방법도 사용할 수 있다.