Oracle 실습 : User 생성, 관리, 삭제

* User 조회 

SQL> SELECT username, default_tablespace, temporary_tablespace, account_status, profile

     2> FROM dba_users;

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

- USERNAME : 사용자명

- DEFAULT_TABLESPACE : 기본으로 사용할 tablespace명

- TEMPORARY_TABLESPACE : 사용할 temporary tablespace명

- ACCOUNT_STATUS : 계정의 상태

- PROFILE : 사용 중인 profile명

---

* User 생성

SQL> CREATE USER <user 명>                                 # User를 생성한다.

     2> IDENTIFIED BY <암호>                                                            

     3> DEFAULT TABLESPACE <tablespace 명>        # DEFAULT TABLESPACE : User의 기본 tablespace

     4> TEMPORARY TABLESPACE <tablespace 명> # TEMPORARY TABLESPACE : User의 기본 temporary tablespace

     5> QUOTA <크기> ON <tablespace 명>, ... # QUOTA : tablespace별 허용된 저장량

     6> [ACCOUNT {LOCK / UNLOCk}]              # ACCOUNT : 계정 잠금 설정 

     7> [PROFILE {<profile명> / DEFAULT}]       # PROFILE : 적용될 PROFILE 지정 

 

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

 

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

- 각 사용자의 tablespace별 quota량을 조회한다.

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

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

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

---

* User를 생성하기 전에 반드시 사용할 tablespace를 먼저 생성한다. 

SELECT tablespace_name, bytes, file_name FROM dba_data_files;

CREATE TABLESPACE te
DATAFILE
'/app/ora19c/oradata/disk1/te01.dbf' SIZE 30M, 
'/app/ora19c/oradata/disk2/te02.dbf' SIZE 30M;

CREATE TABLESPACE st
DATAFILE
'/app/ora19c/oradata/disk2/st01.dbf' SIZE 30M, 
'/app/ora19c/oradata/disk1/st02.dbf' SIZE 30M;

CREATE TABLESPACE indx
DATAFILE
'/app/ora19c/oradata/disk1/indx01.dbf' SIZE 20M, 
'/app/ora19c/oradata/disk2/indx02.dbf' SIZE 20M;

SELECT tablespace_name, status, contents, extent_management, segment_space_management
FROM dba_tablespaces;

CREATE USER te
IDENTIFIED BY te
DEFAULT TABLESPACE te
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON te
QUOTA 2M ON st
QUOTA 2M ON indx;

CREATE USER st0
IDENTIFIED BY st0
DEFAULT TABLESPACE st
TEMPORARY TABLESPACE temp;

CREATE USER st1
IDENTIFIED BY st1
DEFAULT TABLESPACE st
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON st
QUOTA 2M ON indx;

CREATE USER st2
IDENTIFIED BY st2
DEFAULT TABLESPACE st
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON st
QUOTA 2M ON indx
ACCOUNT LOCK;

GRANT connect, resource TO te, st1, st2;
// st0은 권한을 할당하지 않는다.

SELECT username, default_tablespace, temporary_tablespace, account_status, profile
FROM dba_users
ORDER BY 1;

st2 계정은 lock이 걸려있다.

SELECT username, tablespace_name, max_bytes, max_blocks FROM dba_ts_quotas
WHERE lower(username) IN ('te', 'st0', 'st1', 'st2')
ORDER BY 1;

st0은 quota가 없다.

SELECT grantee, granted_role
FROM dba_role_privs
WHERE lower(grantee) IN ('te', 'st0', 'st1', 'st2')
ORDER BY 1;

CONNECT 와 RESOURCE는 권한이 아니라 권한의 묶음인 'role'이다.

SELECT grantee, privilege FROM dba_sys_privs
WHERE lowre(grantee) IN ('te', 'st0', 'st1', 'st2')
ORDER BY 1;

// 사용자에게 할당된 system 권한을 조회한다.

그러나 unlimited tablespace 권한은 role에 할당되지 않지만 resource role을 할당하면 자동으로 할당된다. 

버전에 따라 다름으로 확인해야 한다. 

SELECT grantee, owner, table_name, grantor, privilege
FROM dba_tab_privs
WHERE lower(grantee) IN ('te', 'st0', 'st1', 'st2')
ORDER BY 1;

* st2는 st1과 권한이 동일하지만 lock이 설정되어 사용이 불가능하다.

* st0은 lock이 설정되어 있지는 않지만 'CREATE session' 권한이 없어서 접속할 수 없다. 

'CREATE session' 권한은 CONNECT role에 포함되어 있다. 

* 따라서 관리자 계정으로 st0에 'CREATE session' 권한을 할당하고 st2를 OPEN으로 설정한다.

---

* User 삭제

SQL> DROP USER <user명> [CASCADE]

- User를 삭제한다.

- CASCADE : User를 삭제하기 전 스키마의 모든 segment를 먼저 삭제한다. 

---