[ORACLE] 접속한 유저와 이름이 다른 스키마로 연결하는 방법

새로 생성한 유저이름과 디폴트로 사용하고싶은 스키마의 이름이 다른 경우가 발생할 수 있다.

현재 jpa를 이용하고 있는데, 엔티티클래스에서 스키마를 매핑하는 등 여러 해결법이 존재하는 것 같으나,
나는 db 측면에서 로그인한 유저가 특정 스키마로 연결되도록 설정하고 싶었다.

 

---

 

유저 생성

- cmd 를 이용하여 관리자 계정으로 접속한다

-- 관리자 계정으로 로그인. 비밀번호를 입력하면 ' 다음에 접속됨: ' 이라는 메시지가 뜬다.
C:\Users\myname>sqlplus as sysdba;

-- C## 생략
ALTER SESSION SET "_ORACLE_SCRIPT"=true;

-- 유저 생성
CREATE USER 유저명 IDENTIFIED BY 비밀번호;
-- 기존의 유저 비밀번호를 까먹었을 때 재설정
ALTER USER 유저명 IDENTIFIED BY 신규비밀번호;

 

oracle12c 이후 버전부터는 CDB, PDB   등의 개념으로 인해 생성할 이름 앞에 C##이라는 키워드를 붙여야 한다. 

만일 C## 을 붙이지 않으면 ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다.   와 같은 오류가 발생한다.

C##을 붙이고 싶지 않은 경우 위의 쿼리를 실행해주면 된다.

 

권한 부여

새롭게 생성한 유저에 권한을 부여해야한다.

필요한 권한만 부여하면 되지만, 나는 개발 시 전반적으로 사용할 목적으로 이것저것 여러 권한을 부여하였다.

-- 세션생성, 테이블생성, 뷰 생성, 프로시저생성, 시퀀스생성, 트리거 생성 권한부여
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER TO 유저명;
-- 하나씩 주는 방법
GRANT CREATE SESSION TO 유저명;

-- 부여한 권한 확인
SELECT * FROM DBA_SYS_PRIVS  WHERE GRANTEE = '유저명';

-- 스키마 내의 테이블 dml 권한을 주는 방법
BEGIN
   FOR t IN (SELECT table_name FROM all_tables WHERE owner = '타겟스키마명') LOOP
      EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON 타겟스키마명.' || t.table_name || ' TO 유저명';
   END LOOP;
END;

 

스키마 내의 모든 테이블을 지정하는 방법을 잘 몰라서 다음과 같이 쿼리를 짰다. 한번에 지목하는 방법이 있다면 공유 부탁드리겠습니다ㅜㅜ

위의 쿼리처럼 계정에 바로 권한을 줘도 되고, 만일 ROLE 을 생성해서 관리하고 싶다면 유저명 부분에 원하는 ROLE 을 입력한 뒤, GRANT new_role TO 유저명; 과 같은 방식으로 권한을 줄 수도 있을 것 같다.

dml 권한의 경우 SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = '롤이름'; 으로 롤에 부여된 권한은 확인할 수 있었는데, 유저에게 부여된 권한은 확인하는 방법을 끝내 찾지 못하였다.

 

 

로그인 트리거

여기가 포인트다.

스프링 부트를 통해 오라클에 연결할 때마다 해당 유저 스키마를 변경하는 트리거를 생성해 줘야 한다.

매번 어플리케이션을 실행시킬 때마다, 내가 지정한 유저정보가 유저명과 동일한 스키마가 아닌, 내가 지정한 스키마를 사용하도록 하는 것이다.

CREATE OR REPLACE TRIGGER set_base_schema
AFTER LOGON ON DATABASE
BEGIN
    IF USER = '유저명' THEN
        EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = 타겟스키마';
    END IF;
END;

 

다음과 같은 스키마를 지정하면 해당 계정으로 접속할 때마다 지정한 스키마로 값이 수정될 것이다.

예를 들어, user1 계정으로 접속했을 때, TESTDATA 스키마에 있는 값을 crud 하기 위해 'TESTDATA.table1' 과 같이 스키마명을 따로 명시하지 않아도 된다.