Oracle 유저(USER)와 권한 및 롤(ROLE)

DCL(Data Control Language)과 유저(계정)

DCL이란 한 마디로, 유저의 권한을 제어할 수 있는 명령어이다. 우리는 DB에 접속했다고 해서 바로 테이블, 뷰, 인덱스 등과 같은 오브젝트(Object)를 생성할 수 없다. 왜냐하면 Oracle에서의 사용자(유저, 계정)가 실행하는 모든 DDL(CREATE, ALTER, DROP, RENAME 등)문장은 그에 해당하는 권한이 있어야만 실행 가능하기 때문이다.

 

아래 표는 Oracle 설치 시 기본적으로 제공하는 유저이다.

유저	역할
SCOTT	Oracle 테스트용 샘플 유저, Default 패스워드: TIGER
SYS	DBA 역할을 부여받은 유저
SYSTEM	DB의 모든 시스템 권한을 부여받은 DBA유저. Oracle설치 시에 패스워드 설정.

Oracle은 유저를 통해 데이터베이스에 접속을 하는 형태이다. 즉, 아이디와 비밀번호 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 된다. 다음은 유저 생성의 권한이 없을때 발생한 에러이다.

 

Oracle CONN SCOTT/TIGER → 연결되었다.

CREATE USER HOON IDENTIFIED BY '패스워드'; → ERROR: 권한이 불충분하다.

현재 SCOTT 유저는 유저를 생성할 권한을 부여받지 못했기 때문에 권한이 불충분하다는 오류가 발생한다. 이 경우엔 Oracle의 DBA 권한을 가지고 있는 SYSTEM 유저로 접속하면 유저 생성 권한(CREATE USER)을 다른 유저에게 부여할 수 있다.  추가로 유저(계정)를 생성했다고 바로 로그인 할 수도 없다. 로그인을 하려면 CREATE SESSION권한을 부여받아야 한다. 권한 부여 명령 쿼리는 GRANT CREATE SESSION TO ‘계정명’;

 

 

다른 USER(계정)가 생성된 테이블에 대한 접근 시 유의점

만약 위 예제에서 생성된 HOON유저가 생성된 MENU 테이블을 SCOTT 유저를 통해서 조회하면 어떻게 될까?

HOON, SCOTT 뿐만 아니라 모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여 받아야 한다. 우리가 남의 집에 방문했을 때 집주인의 허락없이는 집에 들어갈 수 없는 것과 같은 이치이다. 집주인은 오브젝트 권한(SELECT, UPDATE, INSERT, DELETE 등)을 각각 따로 관리한다.

 

① HOON이라는 유저가 생성한 MENU라는 테이블은 HOON(집주인)의 소유임. 따라서 SCOTT 등의 다른 유저들은 집주인(소유자)에게 허락(권한)을 받기 전에는 조회 할 수 없음. 집주인이 GRANT SELECT ON MENU TO SCOTT;로 허락해줘야 조회(SELECT) 가능.

② 또한 SCOTT 유저를 통해서 다른 유저가 소유한 테이블에 접근하려면 객체(테이블)앞에 소유한 유저의 이름을 붙여서 접근해야 함. SELECT * FROM HOON.MENU;

③ 따라서, 유저를 생성하면 기본적으로 create session, create table, create procedure 등의 많은 권한을 부여해줘야 한다.

④ 유저를 삭제할 때의 명령어는 DROP USER ‘유저명’ 이고, CASCADE명령을 주면 해당 유저가 생성한 객체(테이블)를 먼저 삭제한 후 유저(계정)를 삭제한다.

 

롤(ROLE)이란?

롤(Role)은 권한(Privileges)들을 묶어서 관리할 수 있게 도와준다.

 

위에서 얘기했다시피, 오브젝트 권한(SELECT, UPDATE, INSERT, DELETE 등)은 각각 따른 권한으로써 관리된다. 그리고 이 시스템 권한은 100개 이상이기 때문에 일일이 사용자에게 설정하는 것이 너무 복잡하고, 특히 유저로부터 권한을 관리하기가 어려움. 그래서 롤(ROLE)을 통해 권한을 부여하는 방법을 사용한다. ROLE을 생성하고, 해당 ROLE에 각종 권한들을 부여한 후 ROLE을 다른 ROLE이나 유저에게 부여함으로써 빠르고 정확하게 필요한 권한을 부여할 수 있다.

 

① 롤 생성 : create role ‘롤 이름’;

② 롤에 권한 부여 : grant create session, create table to ‘롤 이름’;

③ 롤에 부여된 권한을 유저에게 부여 : grant ‘롤 이름’ to ‘유저 이름’;

 

추가적으로, 현재 사용자(계정)에 대해서 부여된 권한을 조회하기 위해서는 딕셔너리를 통해 검색이 가능하다.

SELECT * FROM USER_TAB_PRIVS_MADE(현재 사용자가 부여한 권한);

SELECT * FROM USER_TAB_PRIVS_RECD(현재 사용자에게 부여된 권한);