사용환경

• Oracle DBMS : 19c (19.3)

A. 시작하기 전에..

최근에 몇 개의 DB서버를 셋팅하면서 DB계정을 생성할 일이 많이 있었는데 루틴하게 수행하던 쿼리에 약간 의문이 생겨서 몇 가지 테스트를 해봤다.

그 내용을 잘 기억하기 위해서 정리해서 적어본다.(누군가한테 도움이 될 수도 있지만 실제론... 이렇게 블로그 포스트로 만들다보면 결국 내 기억에 각인이 되더라..)

B. 루틴한 DB계정 생성에 의문

CREATE USER [username] IDENTIFIED BY [password];
GRANT CONNECT TO [username];
ALTER USER [username] DEFAULT ROLE ALL;

CREATE USER의 경우에는 상세한 설정이 필요한 경우도 있지만... 일단 의문점은 그 아래의 두 쿼리에 갑자기 생겼어서 테스트했던거라서 간소화했다.

어느 순간부터 내 코드스니펫 묶음에 위와 같이 DB계정생성용 쿼리로 만들어져 있었는데, CREATE USER 뒤에 두 개의 쿼리를 실행하지 않으면 어떻게 되는지 궁금하더라.

C. 테스트용 계정생성

CREATE USER testuser1 IDENTIFIED BY test123;

CREATE USER testuser2 IDENTIFIED BY test123;

SELECT username, account_status
  FROM dba_users
 WHERE username LIKE 'TEST%'
;

/*
|USERNAME |ACCOUNT_STATUS|
|---------|--------------|
|TESTUSER1|OPEN          |
|TESTUSER2|OPEN          |
*/

비교를 위해서 testuser1과 testuser2 DB계정을 생성한다.

D. 테스트 계획

1. testuser1만 CONNECT 권한부여 후 비교
2. testuser1만 ALTER USER testuser1 DEFAULT ROLE ALL 수행 후 비교

비교를 위해서 계정 2개를 생성한 것이다.

E. CONNECT 권한비교

GRANT CONNECT TO testuser1;

SELECT *
  FROM dba_role_privs
 WHERE grantee LIKE 'TEST%'
;

/*
|GRANTEE  |GRANTED_ROLE|ADMIN_OPTION|DELEGATE_OPTION|DEFAULT_ROLE|COMMON|INHERITED|
|---------|------------|------------|---------------|------------|------|---------|
|TESTUSER1|CONNECT     |NO          |NO             |YES         |NO    |NO       |
*/

그 뒤에 testuser1에만 CONNECT Role을 부여한 뒤에 두 계정 전부 접속해본다.

• 결과
  • testuser1 : 접속성공
  • testuser2 : ORA-01045: 사용자 TESTUSER2는 CREATE SESSION 권한을 가지고있지 않음; 로그온이 거절되었습니다

F. ALTER ~ 명령어 비교

GRANT CONNECT TO testuser2;

SELECT *
  FROM dba_role_privs
 WHERE grantee LIKE 'TEST%'
;


-- testuser1만 default role all 적용
ALTER USER testuser1 DEFAULT ROLE ALL;

SELECT *
  FROM dba_role_privs
 WHERE grantee LIKE 'TEST%'
;

/*
|GRANTEE  |GRANTED_ROLE|ADMIN_OPTION|DELEGATE_OPTION|DEFAULT_ROLE|COMMON|INHERITED|
|---------|------------|------------|---------------|------------|------|---------|
|TESTUSER1|CONNECT     |NO          |NO             |YES         |NO    |NO       |
|TESTUSER2|CONNECT     |NO          |NO             |YES         |NO    |NO       |
*/

alter user [USERNAME] default role all; 여부와 관계없이 두 계정의 권한상태는 동일했다.

01) 추가 테스트

• 가정 : Oracle DBMS에서 기본 값으로 생성된 Role이라 무조건 부여가 된다.
• 시나리오 : 새로운 Role을 생성해서 비교해보자.

CREATE ROLE ALL_SELECT;

GRANT SELECT ANY TABLE TO ALL_SELECT;

SELECT *
  FROM dba_roles
 WHERE common = 'NO'
;

/*
|ROLE      |ROLE_ID|PASSWORD_REQUIRED|AUTHENTICATION_TYPE|COMMON|ORACLE_MAINTAINED|INHERITED|IMPLICIT|EXTERNAL_NAME|
|----------|-------|-----------------|-------------------|------|-----------------|---------|--------|-------------|
|ALL_SELECT|117    |NO               |NONE               |NO    |N                |NO       |NO      |             |
*/


GRANT all_select TO testuser1;

GRANT all_select TO testuser2;

ALL_SELECT라는 Role을 만들고 모든 테이블을 SELECT 할 수 있는 시스템권한을 부여한 뒤에 두 계정에 Role을 부여했다.

차이점은 testuser1은 default role all이 적용된 상태이고, testuser2은 아니라는 것...

• 결론 : 차이점 없음

SELECT *
  FROM session_privs
;

/*
|PRIVILEGE       |
|----------------|
|SET CONTAINER   |
|SELECT ANY TABLE|
|CREATE SESSION  |
*/

두 계정 전부 로그인 한 뒤에 session_privs를 확인해봤는데, 둘 다 SELECT ANY TABLE이 부여된 상태이다.

그 말은 'alter ~ default role all`과 관련이 없다는 것...

02) 왜 그럴까?

SELECT *
  FROM dba_role_privs
 WHERE grantee LIKE 'TEST%'
;

/*
|GRANTEE  |GRANTED_ROLE|ADMIN_OPTION|DELEGATE_OPTION|DEFAULT_ROLE|COMMON|INHERITED|
|---------|------------|------------|---------------|------------|------|---------|
|TESTUSER1|CONNECT     |NO          |NO             |YES         |NO    |NO       |
|TESTUSER2|CONNECT     |NO          |NO             |YES         |NO    |NO       |
|TESTUSER1|ALL_SELECT  |NO          |NO             |YES         |NO    |NO       |
|TESTUSER2|ALL_SELECT  |NO          |NO             |YES         |NO    |NO       |
*/

확인해보니 dba_role_privs의 default_role 컬럼 값이 YES인 경우에는 alter user [username] default role all 명령어와 관계없이 role 권한이 부여된다.

03) 추가 테스트

ALTER USER testuser2 DEFAULT ROLE ALL EXCEPT all_select;

SELECT *
  FROM dba_role_privs
 WHERE grantee LIKE 'TEST%'
;

/*
|GRANTEE  |GRANTED_ROLE|ADMIN_OPTION|DELEGATE_OPTION|DEFAULT_ROLE|COMMON|INHERITED|
|---------|------------|------------|---------------|------------|------|---------|
|TESTUSER1|CONNECT     |NO          |NO             |YES         |NO    |NO       |
|TESTUSER2|CONNECT     |NO          |NO             |YES         |NO    |NO       |
|TESTUSER1|ALL_SELECT  |NO          |NO             |YES         |NO    |NO       |
|TESTUSER2|ALL_SELECT  |NO          |NO             |NO          |NO    |NO       |
*/

alter user ~ 구분에서 except 옵션을 이용해서 ALL_SELECT라는 role 권한을 default에서 제외처리해봤다.

-- testuser2 계정으로 로그인
SELECT *
  FROM session_roles;
;

/*
|ROLE   |
|-------|
|CONNECT|
*/

SELECT username, granted_role, default_role
  FROM user_role_privs
;

/*
|USERNAME |GRANTED_ROLE|DEFAULT_ROLE|
|---------|------------|------------|
|TESTUSER2|ALL_SELECT  |NO          |
|TESTUSER2|CONNECT     |YES         |
*/

• 결과 : testuser2 계정에 의도한 대로 ALL_SELECT role은 부여되지 않음

04) 추가 Role 적용

SET ROLE all_select;

SELECT *
  FROM session_roles;
;

/*
|ROLE      |
|----------|
|ALL_SELECT|
*/


SELECT *
  FROM session_privs
;

/*
|PRIVILEGE       |
|----------------|
|SELECT ANY TABLE|
*/

set role [ROLE_NAME] 명령어를 사용하면 이미 부여되어있지만 로그인 시에 자동으로 부여되지 않은 Role 권한을 획득할 수 있다.

SET ROLE ALL;

SELECT *
  FROM session_roles;
;

/*
|ROLE      |
|----------|
|CONNECT   |
|ALL_SELECT|
*/

SELECT *
  FROM session_privs
;

/*
|PRIVILEGE       |
|----------------|
|SET CONTAINER   |
|SELECT ANY TABLE|
|CREATE SESSION  |
*/

테스트를 해보니 명시적으로 특정 Role로 set을 수행하면 해당 Role만 적용되버린다. 마치 리눅스에서 su - [USERNAME]처럼..

부여된 전체 Role을 적용하기 위해서는 위처럼 set role ALL을 수행하면 된다.

G. 마치면서..

DBA 업무를 하면서 루틴한 명령어들을 미리 스니펫을 만들어두고 수행하는 경우가 있다.(특히 전 담당자에게 승계받은 스크립트들...)

지금까지 문제가 없었어서 의심하지 않았던 스크립트였지만, 이번에 문서화 작업을 진행하면서 갑자기 의문이 들었던 스크립트 들이 아주 많았다.

그래서 개인적으로 테스트를 진행하면서 정리한 내용이다.

한 번 의심을 가져보고 테스트를 하다보니 다른 내용들도 눈에 많이 들어와서... 앞으로 공부한 내용들을 이런 식으로 정리해보려고 한다.

H. 참고문서

1. https://forums.oracle.com/ords/apexds/post/what-is-alter-user-username-default-role-all-command-for-8618
2. https://sksstar.tistory.com/118