오라클 유저 관리, 유저 생성과 관리 실습, 권한 관리, 롤 관리

일일 정리

오라클 유저 관리, 유저 생성과 관리 실습, 권한 관리, 롤 관리

mysecurity 2025. 4. 22. 00:22

CREATE USER aaron // 사용자 만듦
IDENTIFIED BY soccer // 비번 지정함
DEFAULT TABLESPACE data // 기본테이블스페이스 data임
TEMPORARY TABLESPACE temp // 임시테이블스페이스 temp임
QUOTA 15M ON data // data의 15m할당함
QUOTA 10M ON users // users의 10m할당함
PASSWORD EXPIRE; // 비번 만료시켜버림. 비번 변경 전까지 못쓰게 하는데 암튼 안씀

- CREATE USER aaron

: aaron 유저를 생성한다.

- IDENTIFIED BY soccer

: 패스워드를 soccer로 지정한다.

- DEFAULT TABLESPACE data

: 기본 테이블 스페이스를 data로 지정한다.

- DEFAULT TEMPORARY TABLESPACE temp

: 임시 테이블 스페이스를 temp로 지정한다.

- QUOTA 15M ON data

: data 테이블 스페이스의 공간 중 15m을 할당한다.

- QUOTA 10M ON users

: users 테이블 스페이스의 공간 중 10m을 할당한다.

- PASSWORD EXPIRE;

: 패스워드를 만료시킨다. 패스워드 변경 전까지 계정을 사용할 수 없다.

- ACCOUNT LOCK/UNLOCK

: 유저 계정을 명시적으로 잠그거나 잠금 해제한다.

● 테이블 스페이스 유저 할당량 변경

 ALTER USER aaron // 아론 유저 변경한다이
 QUOTA 0 ON USERS; // users에 대한 할당을 0으로 설정해버림

- arron 유저의 USERS 테이블에 대한 할당 공간을 0으로 변경한다.

- 오라클은 설정이 바뀌어도 기존의 데이터는 변하지 않으므로 기존의 데이터는 그대로 둔 채 앞으로는 USERS 테이블을 사용할 수 없다.

● 사용자 삭제

DROP USER aaron; 
DROP USER aaron CASCADE;

- 사용자 삭제 명령어이나 현실적으로 쓰이는 경우는 없다.

● 사용자 정보 얻기

DBA_USERS 딕셔너리: 모든 유저에 대한 모든 정보들이 저장되어 있다.

DBA_TS_QUOTAS 딕셔너리: 모든 유저의 테이블 스페이스마다 어느 정도의 쿼터가 할당되어 있는지 블록단위로 저장되어 있다.

2. 유저 생성과 관리 실습

2-1 User 조회

● 사용 명령어

SQL> SELECT username, default_tablespace, temporary_tablespace, 
 2   account_status, profile
 3   FROM dba_users;

- 유저의 이름과 각 유저의 여러 설정 사항을 조회한다.

- USERNAME : 사용자 명

- DEFAULT_TABLESPACE : 기본으로 사용할 테이블 스페이스명

- TEMPORARY_TABLESPACE : 사용할 템포러리 테이블 스페이스명

- ACCOUNT_STATUS : 계정의 상태

- PROFILE : 사용 중인 프로파일(정책) 명

● 실습

- dba_users 테이블의 구조를 확인한다.

- 유저 별 테이블스페이스 관련 정보를 확인한다.

- EXPIRED & LOCKED : 패스워드가 만료되어 락이 걸린 상태이다.

- DEFAULT : DEFAULT 라는 이름의 프로파일(정책)에 적용받는다.

2-2 User 생성

● 사용 명령어

SQL> CREATE USER <user 명> 
 2   IDENTIFIED BY <암호>
 3   DEFAULT TABLESPACE <tablespace 명>
 4   TEMPORARY TABLESPACE <tablespace 명>
 5   QUOTA <크기> ON <tablespace 명>, ...... 
 6   [ACCOUNT {LOCK / UNLOCK}]
 7   [PROFILE {<profile명> / DEFAULT}]

- 유저를 생성한다.

- DEFAULT TABLESPACE : 유저의 기본 테이블 스페이스

- TEMPORARY TABLESPACE : 유저의 기본 템포러리 테이블 스페이스

- QUOTA : 테이블 스페이스 별 할당된 저장량

- ACCOUNT : 계정 잠금 설정

- PROFILE : 적용될 프로파일 지정

- CREATE 문장은 ALTER 문장과 형식이 동일하다.

 SQL> SELECT username, tablespace_name, max_bytes, max_blocks FROM dba_ts_quotas;

- 각 사용자의 테이블 스페이스 별 쿼타량을 조회한다.

- MAX_BYTES : 할당된 쿼타량 (byte단위)

- MAX_BLOCKS : 할당된 쿼타량 (block개수)

- 쿼타가 unlimited로 지정되어 제한하지 않는 경우 -1로 표시된다.

● 실습

- 테이블 스페이스 정보를 확인한다.

- 유저를 생성하기 전 반드시 사용할 테이블 스페이스를 먼저 생성한다.

- te와 st 동시 실행 시 속도가 느려지는 것을 방지하기 위해 te는 disk1부터, st는 disk2부터 사용하도록 설정한다.

- 생성된 테이블 스페이스를 확인한다.

- 유저 생성 후 connect, resource 롤을 지정한다.

: connect : DB에 접속할 수 있는 모든 권한이 지정되어 있다.

: resource: create 할 수 있는 모든 권한이 지정되어 있다.

- 쿼타 지정 시 반드시 인덱스를 함께 지정한다.

- st0 사용자는 쿼타 설정을 하지 않는다.

- st2 사용자는 어카운트 설정을 LOCK으로 지정한다.

- st0 사용자는 권한을 할당하지 않는다.

- st2 계정은 락이 걸린 것을 확인한다.

- st0 계정은 쿼타가 없음을 확인한다. dba_ts_quotas는 쿼타가 설정된 유저만 표시된다.

- 누구에게 어떤 롤이 할당됐는지 확인한다.

- grantee는 할당받는 사용자를 나타낸다.

- 이후 사용자에게 할당된 시스템 권한 및 오브젝트 권한은 없음을 확인한다.

- CONN은 현재 접속을 먼저 끊고 접속 시도를 하므로 접속 실패 시 아무도 접속 상태가 아니게 된다.

- st0은 'CREATE SESSION' 권한이 없어 접속할 수 없다. 이는 CONNECT 롤에 포함되어 있다.

- st0에게 다이렉트로 CREATE SESSION 권한을 할당한다. 딱 접속까지만 가능하다.

- st2를 언락한다.

- st0과 st2 모두 접속이 가능함을 확인한다.

2-3 Quota와 User

● 사용 명령어

SQL> SELECT u.username, u.default_tablespace, max_bytes, max_blocks
 2   FROM dba_users u, dba_ts_quotas q
 3   WHERE u.username = q.username
 4   AND u.default_tablespace = q.tablespace_name;

- 사용자의 디폴트 테이블 스페이스 별 쿼타량을 조회한다.

- 일반 사용자는 접근할 수 없는 딕셔너리임에 유의한다.

SQL> ALTER USER <user 명> 
 2   QUOTA <크기> ON <tablespace 명>;

- 유저의 쿼타량을 조정한다.

● 실습

- 유저 별 쿼타 할당 정보를 확인한다.

- 쿼타가 할당된 te 계정은 정상적인 테이블 생성이 가능하다.

- 쿼타가 할당되지 않은 st0 계정은 세그먼트 생성을 할 수 없다.

- 관리자 권한으로 st0에게 다이렉트로 create table 권한을 설정한다.

- st0 계정으로 테이블이 정상적으로 만들어 진다.

: 매뉴얼 상 st0은 쿼타 할당을 받지 않았으므로 테이블을 만들기 위한 블록을 할당받지 못해 테이블을 만들 수 없다. 이는 테이블이 생성될 때 블록을 할당받는 것이 아니라 인서트 작업 시 할당받는다는 것을 나타낸다.

: 인서트 후 롤백 진행 시 할당받은 공간은 그대로 남아있다.

- st0 계정에 디폴트 테이블 스페이스에 대한 쿼타를 할당한 후 인서트 작업이 가능하다.

2-4 User 삭제

실제로 오라클에서 유저를 삭제하는 경우는 없다.

● 사용 명령어

SQL> DROP USER <user명> [CASCADE]

- 유저를 삭제한다.

- CASCADE : 유저를 삭제하기 전 스키마의 모든 세그먼트를 먼저 삭제한다.

● 실습

- st0 스키마에 테이블이 존재하므로 st0 계정 삭제는 현재 불가능하다.

- 테이블 삭제 후 st0 계정 삭제가 가능하다.

- cascade 옵션 사용 시 세그먼트 존재 유무와 관계없이 삭제가 가능하다.

- st0, te 계정이 삭제된 것을 확인한다.

3. 권한 관리

3-1 권한 관리

● 오라클 사용자 권한의 두 유형

- 시스템 권한 (시스템 프리빌리지)

: 테이블을 만들거나 접속할 수 있는 권한으로, 주체는 있으나 대상은 없다?

: 관리자가 주는 권한이며, 누가 줬는지는 관리하지 않는다.

- 객체 권한 (오브젝트 프리빌리지)

: 보안 모델과 관련된 것은 모두 이것으로, 특정 테이블, 뷰, 시퀀스 등에서 특정 작업을 수행할 수 있는 권한이다.

: 권한 소유자 혹은 권한을 위임받은 자가 주는 권한이며, 누가 줬는지 관리한다.

: 권한을 받은 측 = grantee, 권한을 주는 측 = grantor

● 시스템 권한

- 100개 이상의 시스템 프리빌리지가 존재한다.

- 권한에 any 키워드가 있다면 마치 관리자와 같이 다른 사용자의 스키마를 조작할 수 있다. 때문에 일반 사용자에게는 잘 주지 않는다.

- GRANT 명령은 사용자에게 권한을 추가한다.

- REVOKE 명령은 사용자로부터 권한을 삭제한다.

범주	예제
INDEX	CREATE ANY INDEX ALTER ANY INDEX DROP ANY INDEX
TABLE	CREATE TABLE CREATE ANY TABLE ALTER ANY TABLE DROP ANY TABLE SELECT ANY TABLE UPDATE ANY TABLE DELETE ANY TABLE
SESSION	CREATE SESSION ALTER SESSION RESTRICTED SESSION
TABLESPACE	CREATE TABLESPACE ALTER TABLESPACE DROP TABLESPACE UNLIMITED TABLESPACE

- creat index 라는 권한은 없다.

- alter session : 다른 사람의 세션을 끊어버릴 수 있는 위험한 권한이다.

- restricted session : DB를 리스트릭트 모드로 바꾼다. 이 경우 해당 권한 소유자와 관리자만이 접근 가능하다.

- create tablespace : 테이블 스페이스를 생성한다. 일반 사용자에게 주지 않는 권한이다.

- unlimited tablespace : 모든 테이블 스페이스를 마음대로 쿼타 할당과 관계없이 마음대로 사용 가능하다.

● 시스템 권한 부여

GRANT CREATE SESSION TO emi;

GRANT CREATE SESSION TO emi WITH ADMIN OPTION;

- GRANT 명령을 사용하여 시스템 권한을 부여한다.

- with admin option :권한을 부여받은 사람이 그 권한이나 롤을 다른 사용자나 롤에 부여할 수 있다.

- 쉼표를 이용해 여러 권한 혹은 여러 사용자에게 설정 가능하다.

● SYSDBA 및 SYSOPER 권한

관리자 권한인 sysdba와 sysoper의 권한은 다음과 같다.

범주	예제
SYSOPER	STARTUP SHUTDOWN ALTER DATABASE OPEN \| MOUNT ALTER DATABASE BACKUP CONTROLFILE TO RECOVER DATABASE ALTER DATABASE ARCHIVELOG RESTRICTED SESSION
SYSDBA	SYSOPER PRIVILEGES WITH ADMIN OPTION CREATE DATABASE ALTER TABLESPACE BEGIN / END BACKUP RESTRICTED SESSION RECOVER DATABASE UNTIL

- sys의 권한 : sysdba, system의 권한 : sysoper

- sysdba 권한은 sysoper의 상위 권한이다.

- sysdba는 sys밖에 사용할 수 없으나 sysdba 권한을 다른 사용자에게 위임할 수는 있다.

: 권한을 위임받은 사용자는 '사용자계정/사용자패스워드 as sysdba를 통해 sys로 접속할 수 있다.

● 시스템 권한 취소

REVOKE CREATE TABLE FROM EMI

- REVOKE 명령을 사용하여 사용자의 시스템 권한을 제거한다.

- 시스템 권한에 대해 ADMIN OPTION 권한이 있는 사용자는 시스템 권한 취소가 가능하다.

- GRANT를 통해 부여받은 권한만 취소가 가능하다.

- 시스템 권한은 그랜터를 신경쓰지 않으므로 Emi가 Jeff의 권한을 취소할 수 있다.

● 객체 권한

객체 권한은 특정 테이블, 뷰, 시퀀스 등의 객체에서 특정 작업을 수행할 수 있는 권한이다.

각 객체에 적용 가능한 권한은 다음과 같다.

객체 권한	테이블	뷰	시퀀스	프로시저
ALTER	O	O	O	O
DELETE	O	O
EXECUTE				O
INDEX	O
INSERT	O	O
REFERENCES	O
SELECT	O	O	O
UPDATE	O	O

● 객체 권한 부여

GRANT를 통해 객체 권한을 부여하며 그랜티가 다른 사용자에게 다시 객체 권한을 부여하기 위해서는 객체가 그랜티의 스키마에 있거나 그랜티가 GRAND OPTION 권한을 가져야 한다.

GRANT EXECUTE ON dbms_output TO jeff;

- dbms_output에 대해 실행할 수 있는 권한을 부여한다.

GRANT UPDATE ON emi.customers TO jeff WITH GRANT OPTION;

- emi.customers에 대해 업데이트할 수 있는 권한을 부여한다. 권한부여 권한 또한 부여한다.

- emi.customers 형식으로 작성한 것으로 이 문장의 그랜터가 emi는 아님을 짐작할 수 있다.

● 객체 권한 취소

REVOKE를 통해 객체 권한을 취소하며, 권한을 부여한 사용자만이 해당 권한을 지울 수 있다.

즉, 시스템 권한과 달리 그랜터를 확인한다.

REVOKE SELECT ON emi.orders FROM jeff;

- jeff에게서 emi.orders를 조회할 권한을 삭제한다.

- Emi는 Jeff나 Bob의 권한을 삭제할 수 없다.

- Bob 또한 Emi의 권한을 직접 삭제할 수 없으므로 Jeff의 권한을 삭제함으로써 Emi의 권한을 삭제한다.(cascade)

● 권한 정보 얻기

다음 뷰를 질의하여 권한에 대한 정보를 얻을 수 있다.

- DBA_SYS_PRIVS

: 누가 어떤 시스템 권한을 가지고 있는지 알 수 있다.

- SESSION_PRIVS

: 누가 어떤 세션에 어떤 권한을 가지고 있는지 알 수 있다.

- DBA_TAB_PRIVS

: 누가 어떤 객체 권한을 가지고 있는지 알 수 있다.

- DBA_COL_PRIVS

: 누가 어떤 컬럼에 어떤 권한을 가지고 있는지 알 수 있다.

: 기본적으로 테이블에서 특정 컬럼 만을 따로 검색할 수는 없으므로 특정 컬럼만 볼 수 있는 뷰를 생성한 후 해당 뷰에 대한 권한을 설정하여 컬럼에 대한 권한 부여를 할 수 있다.

4. 롤 관리

4-1 롤 관리

● 롤(role)

롤이란 각종 권한들의 집합이며 롤에 롤을 부여할 수도 있다.

- 테이블 생성 권한은 A 유저만이 가지고 있으므로 A는 특정 한 사람이어서는 안된다.

- 오라클에서 A 유저는 직위나 직책으로, 여러 사람이 사용 가능하다.

● 롤의 이점

- 권한 관리 용이성

: 권한을 쉽고 다이나믹하게 관리할 수 있다.

- 동적 권한 관리

: 사용자마다 권한을 선택하여 사용하도록 설정할 수 있다.

- 권한의 선택적 가용성

: 롤의 일시적인 활성화 및 비활성화가 가능하다.

- 운영체제를 통한 부여

: 운영체제 명령이나 유틸리티를 통해 데이터베이스에서 유저에게 롤을 할당할 수 있다.

● 롤 생성

보통은 다음과 같은 문장으로 롤을 생성한다.

CREATE ROLE oe_clerk;

- oe_clerk 라는 롤을 생성한다.

굳이 원하는 경우 다음과 같이 패스워드를 지정할 수 있으나 보통 만들지 않는다.

CREATE ROLE hr_clerk
IDENTIFIED BY bonus;

- bonus가 패스워드인 hr_clerk 롤을 생성한다.

● 미리 정의된 롤

오라클 사에서 고객들에게 주는 예제로, 롤을 어떻게 생성하는지 예시를 들기 위해 미리 정의되어 있는 롤이다.

미리 정의된 롤은 다음과 같다.

롤 이름	설명
CONNECT, RESOURCE, DBA	DB 접속, create, 관리자 권한
EXP_FULL_DATABASE	데이터베이스 익스포트 권한
IMP_FULL_DATABASE	데이터베이스 임포트 권한
DELETE_CATALOG_ROLE	데이터 딕셔너리 테이블에 대한 delete 권한
EXECUTE_CATALOG_ROLE	데이터 딕셔너리 테이블에 대한 excute 권한
SELECT_CATALOG_ROLE	데이터 딕셔너리 테이블에 대한 select 권한

● 롤 수정

ALTER ROLE을 통해 인증 방식을 수정할 수 있으며 이를 위해 ADMIN 옵션 혹은 ALTER ANY ROLE 권한이 필요하다.

ALTER ROLE oe_clerk IDENTIFIED BY order;

ALTER ROLE hr_clerk IDENTIFIED EXTERNALLY;

ALTER ROLE hr_manager NOT IDENTIFIED;

- oe_clerk 롤의 패스워드를 order로 지정한다.

- hr_clerk 롤을 활성화하기 위해서는 외부 서비스에서 사용자를 인증해야 하도록 한다.

- hr_manager 롤의 패스워드를 없앤다.

● 롤 할당

GRANT를 통해 유저 혹은 롤에게 롤을 부여한다.

GRANT oe_clerk TO scott;

GRANT hr_clerk TO hr_manager;

GRANT hr_manager TO scott WITH ADMIN OPTION;

- ADMIN OPTION을 통해 scott 유저가 hr_manager 롤을 다른 유저 혹은 롤에 부여할 수 있다.

- 한 유저에게 여러 롤을 부여하는 것은 좋지 않으므로 롤에 롤을 부여하여 최종적으로 한번 유저에게 롤을 부여하도록 한다.

● 디폴트 롤 설정

디폴트 롤은 유저 로그인 시 기본적으로 활성화되는 롤이다.

기본적으로 유저에게 할당된 모든 롤은 로그인 시 활성화되며, 특정 롤들만 활성화/비활성화시킬 수 있다.

ALTER USER scott DEFAULT ROLE hr_clerk, oe_clerk;

- hr_clerk, oe_clerk 롤만 기본 활성화된다.

ALTER USER scott DEFAULT ROLE ALL;

- 할당된 모든 롤이 기본 활성화된다. 이것이 기본값이다.

ALTER USER scott DEFAULT ROLE ALL EXCEPT hr_clerk;

- hr_clerk 롤만 비활성화된다.

ALTER USER scott DEFAULT ROLE NONE;

- 모든 롤을 비활성화한다. 이 경우 최소한 create session 권한은 있어야 작업이 가능하다.

● 롤 활성화 및 비활성화

SET ROLE 명령을 통해 롤을 활성화/비활성화 시킬 수 있다.

SET ROLE은 롤 추가의 개념이 아닌 설정한 롤만 활성화시키는 개념이므로 필요한 롤은 모두 작성해야 하며, 패스워드가 필요한 롤의 경우 SET ROLE 문장에 패스워드가 포함되어 있어야 한다.

한 유저를 쓰는 사람들이 서로 다른 권한을 써야 할 때 사용한다.

SET ROLE hr_clerk;

SET ROLE oe_clerk IDENTIFIED BY order;

SET ROLE ALL EXCEPT oe_clerk;

- hr_clerk을 제외한 모든 롤을 비활성화한다.

- 패스워드가 order인 oe_clerk을 활성화한다.

- oe_clerk을 제외한 모든 롤을 활성화한다.

● 사용자의 롤 취소

사용자의 롤을 취소하기 위해서는 ADMIN OPTION 또는 GRANT ANY ROLE 권한이 필요하다.

REVOKE oe_clerk FROM scott;

REVOKE hr_manager FROM PUBLIC;

- to/from 뒤에 쓸 수 있는 것은 사용자명, 롤, 퍼블릭이 있다.

- 어떤 권한을 퍼블릭에 주면 모든 사용자가 쓸 수 있지만, 모든 사용자에게 권한을 준것과 같다는 것은 아니다. 때문에 퍼블릭에 준 권한을 일부 사용자로부터 리보크하는 것은 불가능하다.

● 롤 삭제

롤 삭제는 부여된 모든 사용자와 롤에서 롤을 제거하며 데이터베이스에서 완전히 롤을 제거한다. ADMIN OPTION 또는 DROP ANY ROLE 권한이 필요하다.

DROP ROLE hr_manager;

- hr_manager 롤을 삭제한다.

● 롤 생성 지침

롤 생성 지침은 다음과 같다.

① 각 단위 업무 별 필요한 권한을 생성하고 묶어 응용 프로그램 롤, 즉 업무 롤에 할당한다.

② 업무 롤을 묶어 직위 별로 필요한 롤, 즉 직무(사용자) 롤에 할당한다.

③ 직무 롤을 묶어 각 사용자에게 할당한다.

● 롤 정보 얻기

다음 뷰를 질의하여 롤에 대한 정보를 얻을 수 있다.

- DBA_ROLES

: 데이터베이스의 모든 롤을 확인할 수 있다.

- DBA_ROLES_PRIVS

: 유저, 롤, 퍼블릭에 할당된 롤을 확인할 수 있다.
- ROLE_ROLE_PRIVS

: 롤에 할당된 롤을 확인할 수 있다.

- DBA_SYS_PRIVS

: 유저, 롤, 퍼블릭에 할당된 시스템 권한을 확인할 수 있다.
- ROLE_SYS_PRIVS

: 롤에 할당된 시스템 권한을 확인할 수 있다.

- DBA_TAB_PRIVS

: 유저, 롤, 퍼블릭에 할당된 객체 권한을 확인할 수 있다.
- ROLE_TAB_PRIVS

: 롤에 할당된 객체 권한을 확인할 수 있다.

'일일 정리' 카테고리의 다른 글

DB 패스워드 파일, Table 관리 (0)	2025.04.23
권한과 Role, 패스워드 보안 및 리소스 관리, Profile 관리 (0)	2025.04.22
오라클 자동 실행, Tablespace와 data file (0)	2025.04.19
Redo log file, Redo log file 관리, 오라클 Network 설정과 접속, 테이블 스페이스 (0)	2025.04.17
오라클 서버 설치, 오라클 구성 요소, 오라클 STARTUP과 SHUTDOWN, Parameter file 관리, Control file 관리 (0)	2025.04.17

현재글오라클 유저 관리, 유저 생성과 관리 실습, 권한 관리, 롤 관리

mysecurity 님의 블로그

mysecurity 님의 블로그 입니다.

Today :
Yesterday :

일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

mysecurity 님의 블로그