[ORACLE] 계정 생성 후 권한 부여

계정 생성

CREATE USER USER_NAME IDENTIFIED BY 1234;
GRANT CONNECT, RESOURCE, DBA TO USER_NAME;

SELECT * FROM ALL_USERS WHERE USERNAME = 'USER_NAME';

• USER_NAME라는 사용자 계정을 비밀번호 1234로 생성
• 새로 생성한 사용자에게 CONNECT, RESOURCE, DBA 권한을 부여
• USER_NAME 사용자 계정이 정상적으로 생성되었는지 확인

TEST_SCHEMA 모든 권한 부여

BEGIN
   FOR rec IN (SELECT 'GRANT ALL ON TEST_SCHEMA.' || object_name || ' TO USER_NAME' AS grant_stmt
               FROM all_objects
               WHERE owner = 'TEST_SCHEMA'
               AND object_type IN ('TABLE', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'VIEW'))
   LOOP
      EXECUTE IMMEDIATE rec.grant_stmt;
   END LOOP;
END;
/

• FOR rec IN (...)
  TEST_SCHEMA 소유자의 모든 객체(테이블, 시퀀스, 프로시저, 함수, 패키지, 뷰)에 대해 반복

• EXECUTE IMMEDIATE rec.grant_stmt;
  각 객체에 대해 USER_NAME 사용자에게 모든 권한(GRANT ALL)을 부여하는 동적 SQL 문을 실행

TEST_SCHEMA 모든 권한 취소

BEGIN
   -- 기존 권한 취소
   FOR rec IN (SELECT 'REVOKE ALL ON TEST_SCHEMA.' || object_name || ' FROM USER_NAME' AS revoke_stmt
               FROM all_objects
               WHERE owner = 'TEST_SCHEMA'
               AND object_type IN ('TABLE', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'VIEW'))
   LOOP
      BEGIN
         EXECUTE IMMEDIATE rec.revoke_stmt;
      EXCEPTION
         WHEN OTHERS THEN
            NULL; -- 예외 처리: 권한이 없는 경우 무시
      END;
   END LOOP;
END;
/

• BEGIN ... EXCEPTION ... END;
  각 객체에 대해 USER_NAME 사용자로부터 모든 권한(REVOKE ALL)을 취소하는 동적 SQL 문을 실행 (예외가 발생하면 무시)

TEST_SCHEMA 테이블의 CRUD 권한 부여

BEGIN
   FOR rec IN (SELECT 'GRANT INSERT, SELECT, UPDATE, DELETE ON TEST_SCHEMA.' || object_name || ' TO USER_NAME' AS grant_stmt
               FROM all_objects
               WHERE owner = 'TEST_SCHEMA'
               AND object_type IN ('TABLE'))
   LOOP
      EXECUTE IMMEDIATE rec.grant_stmt;
   END LOOP;
END;
/

TEST_SCHEMA 테이블의 CRUD 권한 취소

BEGIN
   FOR rec IN (SELECT 'REVOKE ALL ON TEST_SCHEMA.' || object_name || ' FROM USER_NAME' AS revoke_stmt
               FROM all_objects
               WHERE owner = 'TEST_SCHEMA'
               AND object_type IN ('TABLE'))
   LOOP
      EXECUTE IMMEDIATE rec.revoke_stmt;
   END LOOP;
END;
/

새로만든 계정에 TEST_SCHEMA SYNONYM 주기

BEGIN
   FOR rec IN (SELECT table_name FROM all_tables WHERE owner = 'TEST_SCHEMA') LOOP
      EXECUTE IMMEDIATE 'CREATE SYNONYM USER_NAME.' || rec.table_name || ' FOR TEST_SCHEMA.' || rec.table_name;
   END LOOP;
END;
/

• EXECUTE IMMEDIATE 'CREATE SYNONYM ...'
  각 테이블에 대해 USER_NAME 사용자 스키마 내에 동의어(SYNONYM)를 생성하여 테이블을 참조할 수 있게 함

권한 확인

SELECT 
    grantee, 
    owner, 
    table_name, 
    privilege 
FROM 
    dba_tab_privs
WHERE 
    grantee = 'USER_NAME'
    AND owner = 'TEST_SCHEMA';

• USER_NAME 사용자가 TEST_SCHEMA의 어떤 테이블에 어떤 권한을 가지고 있는지 확인