오라클 데이터베이스 관리자 기초- (9)사용자 관리

* 사용자 관리

사용자 및 보안

인증 방식

데이터베이스를 엑세스 하는 사용자는 인증 방식 중 하나를 통해 인증 됨

- 패스워드 인증(ID와 패스워드 입력 후 사용)

- OS 인증

 

계정 locking(잠금)

인증받지 못 한 사용자가 로그인 하는 것을 막기 위한 것. 예를들면 인증서 같은 경우 5번 이상 틀리면 lock이걸린다.

 

default 테이블 스페이스

사용자가 생성한 세그먼트가 저장되는 위치

 

temp 테이블 스페이스

정렬과 관련된 명령이 일어나면 사용할때 사용되는 위치를 정의

 

테이블 스페이스 quotas(할당량)

사용자에게 할당된 물리적 저장 공간 관리

 

Role privileges(롤 권한)

롤 사용을 통해 간접적으로 사용자에게 권한 부여

 

Direct privilges(직접 권한)

사용자가 수행하는 작업을 제어하는데 사용. 가급적 롤 권한을 사용해야 하며 직접 권한의 사용은 피해야 한다.

 

Resource limits(자원 제한)

사용자가 너무 많은 리소스를 먹지 않도록 사용하는 자원의 사용을 제한

 

오라클에서의 스키마

- 오라클에서의 스키마는 user와 같은 의미이다.

- 정확히는 user가 가지고 있는 오브젝트의 모음

- 사용자가 생성될 때 해당 스키마도 생성

- 한 사용자는 한 스키마에만 연관 가능

- 사용자 이름과 스키마는 종종 교환되어 사용됨.

 

스키마(user) 객체

사용자가 소유할 수 있는 객체중 일부

- 오라클은 객체를 소유하고 있기 때문에 권한이 필요가 없다.

- 주인없는 테이블은 존재할 수 없다.

- 소유자를 변경할 수 없다.

- 초기에는 user가 사람일꺼라 생각했지만 현재는 그렇기않기 때문에 필드에서 관리하는 방법은 이론과 많이 다르다.

 

사용자 생성 점검 목록

- 사용자가 객체를 저장해야 할 테이블 스페이스 식별

- 테이블 스페이스의 할당량 결정(통계정보를 담당하는 관계자들이 결정)

- 기본 테이블 스페이스 및 임시 테이블 스페이스 할당

// 여기까지 사용자 생성하기 전에 해야하는 일로 문서화 되어야 한다.

- 사용자 생성

- 사용자에게 권한 및 롤을 부여(권한을 묶은 것이 롤, 되도록이면 롤을 부여한다)

 

새로운 사용자 생성 : 데이터 베이스 인증

CREATE USER aaron
IDENTIFIED BY soccer
DEFAULT TABLESPACE data
TEMPORARY TABLESPACE temp
QUOTA 15M ON data
QUOTA 10M ON users
PASSWORD EXPIRE; 

// user : 사용자 이름

by soccer : 암호

default 또는 temporary 테이블스페이스 : 사용자의 기본 테이블스페이스나 임시 테이블스페이스 식별

quota : 사용자가 소유한 객체에 허용되는 최대 공간을 정의

password expire : 사용자가 로그인할때 비밀번호 재설정

 

테이블스페이스의 사용자 할당량 변경(quota)

- 이미 만들어진 테이블 스페이스 내의 정보는 그대로 사용할 수 있다.

 

사용자 삭제

- 스키마에 객체가 포함되어 있으면 CACCADE절을 사용하여 스키마의 모든 객체를 삭제

- 오라클에 현재 연결되어 있는 사용자는 삭제할 수 없다.

- 그렇지만 실무에서 데이터베이스에서 사용자를 지우는 경우는 일반적으로 없다.

 

[실습]User 조회

<사용 명령어>

SELECT username, default_tablespace, temporary_tablespace,
account_status, profile
FROM dba_users;

- User의 이름과 각 user의 여러 설정 사항을 조회한다.
- USERNAME : 사용자명
- DEFAULT_TABLESPACE : 기본으로 사용할 tablespace명
- TEMPORARY_TABLESPACE : 사용할 temporary tablespace명
- ACCOUNT_STATUS : 계정의 상태
- PROFILE : 사용 중인 profile명

DESC dba_users;
//USER_ID의 타입이 NUMBER로 되어있는 것은 UID로 구분하기 때문

SELECT username, default_tablespace, temporary_tablespace, account_status, profile
FROM dba_users
ORDER BY 1;
// 이전 실습에서 sql 명령을 통해서 생성된 DB이므로 예제용 스키마가 없다.
locked : 잠겨있어서 못 씀
expired : 계정 만료

 

[실습]User 생성

<사용 명령어>

CREATE USER 
IDENTIFIED BY <암호>
DEFAULT TABLESPACE 
TEMPORARY TABLESPACE 
QUOTA <크기> ON , ......
[ACCOUNT {LOCK / UNLOCK}]
[PROFILE {<profile명> / DEFAULT}]

- User를 생성한다.

- DEFAULT TABLESPACE : User의 기본 tablespace
- TEMPORARY TABLESPACE : User의 기본 temporary tablespace
- QUOTA : tablespace별 허용된 저장량
- ACCOUNT : 계정 잠금 설정
- PROFILE : 적용될 PROFILE 지정
- CREATE 문장은 ALTER 문장과 형식이 동일하다.

 

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로 표시된다.

SELECT tablespace_name, bytes, file_name FROM dba_data_files;
// 쓸 수 있는 테이블 스페이스 전무.(bst15는 무시)

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

 

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

 

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

//user를 생성하기 전에 반드시 사용할 테이블 스페이스 먼저 생성. te, st, indx를 만들어주는 과정

SELECT tablespace_name, bytes, file_name FROM dba_data_files;
// te, st, indx 테이블 스페이스가 만들어 짐

SELECT tablespace_name, status, contents,
extent_management, segment_space_management
FROM dba_tablespaces;
// 설정하지 않은 부분은 자동으로 설정

CREATE USER te 
IDENTIFIED BY te
DEFAULT TABLESPACE te
TEMPORARY TABLESPACE tmp
QUOTA UNLIMITED ON te
QUOTA 2M ON st
QUOTA 2M ON indx;
// te에 대해선 제한이 없고 st와 indx는 2메가만 사용 가능 

CREATE USER st0 
IDENTIFIED BY st0
DEFAULT TABLESPACE st
TEMPORARY TABLESPACE tmp;
// st0 사용자는 quota 대한 설정을 하지 않음.

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

CREATE USER st2
IDENTIFIED BY st2
DEFAULT TABLESPACE st
TEMPORARY TABLESPACE tmp
QUOTA UNLIMITED ON st
QUOTA 2M ON indx
ACCOUNT LOCK;
// 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;
// -1제한이 없음을 의미. st0은 quota가 없어서 출력되지 않는다.

SELECT grantee, granted_role FROM dba_role_privs
WHERE lower(grantee) IN ('te', 'st0', 'st1', 'st2')
ORDER BY 1;
// st0는 롤을 grant 권한을 못 받았기 때문에 출력되지 않는다.

SELECT grantee, privilege FROM dba_sys_privs 
WHERE lower(grantee) IN ('te', 'st0', 'st1', 'st2')
ORDER BY 1;
//사용자에게 할당된 system 권한을 조회. 롤을 부여한 것이지 권한이 부여된 것이 아니기 때문에 아무것도 출력되지 않는다. 

CONN st1/st1

CONN st2/st2
//lock 이 설정되어 사용이 불가능 하다.

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

CONN / as sysdba

GRANT create session TO st0;

// st0에 CREATE session 권한을 할당한다.

 

ALTER USER st2

ACCOUNT UNLOCK;
//st2를 OPEN으로 설정한다. 

 

CONN st0/st0

CONN st2/st2

// st0과 st2 계정 모두 접속이 되는 것을 확인

 

[실습]Quota와 User

<사용 명령어>

SELECT u.username, u.default_tablespace, max_bytes, max_blocks
FROM dba_users u, dba_ts_quotas q
WHERE u.username = q.username
AND u.default_tablespace = q.tablespace_name;
- 사용자의 DEFAULT TABLESPACE별 quota량을 조회한다.

ALTER USER 
QUOTA <크기> ON ;
- User의 quota 량을 조정한다.

SELECT username, tablespace_name, max_bytes, max_blocks FROM dba_ts_quotas
WHERE lower(username) IN ('te', 'st0', 'st1', 'st2')
ORDER BY 1;
// quota량 조회. st0는 quota가 없어서 출력이 되지 않는다. quotr가 설정된 user만 표시

SELECT u.username, u.default_tablespace, max_bytes, max_blocks
FROM dba_users u, dba_ts_quotas q
WHERE u.username = q.username
AND u.default_tablespace = q.tablespace_name
AND lower(u.username) IN ('te', 'st0', 'st1', 'st2');
// 사용자들의 default 테이블 스페이스별 quota량이 unlimited로 지정된 것을 확인

CONN te/te

CREATE TABLE te (no NUMBER);
// quota가 할당된 사용자는 segment 생성에 문제가 없다.

SELECT table_name, tablespace_name FROM user_tables;
// 디폴트 테이블스페이스가 te기 때문에 te에 생성

CONN st0/st0

CREATE TABLE st0 (no NUMBER);
// st0에게는 table을 생성 할 수 있는 권한이 없다.

CONN / as sysdba

SELECT grantee, privilege FROM dba_sys_privs
WHERE grantee = 'ST0';

// st0의 시스템 권한 조회. CREATE SESSION만 존재하는 것을 확인

GRANT create table TO st0;
// st0에게 테이블 만들수 있는 권한 부여

SELECT grantee, privilege FROM dba_sys_privs
WHERE grantee = 'ST0';
// st0의 시스템 권한을 조회. CREATE TABLE이 추가된 것을 확인

CONN st0/st0

CREATE TABLE st0 (no NUMBER); 
// 테이블을 만들때가 아닌 첫번째 데이터를 입력할때 공간을 할당하므로 문제없이 만들어 진다. 

INSERT INTO st0 VALUES (1);
// st0가 default 테이블 스페이스에 quota가 없어서 입력되지 않는다

CONN / as sysdba

SELECT username, default_tablespace FROM dba_users
WHERE username='ST0';
// DEFAULT TABLESPACE 설정은 사용 권한이나 사용량에 대한 설정이 아니다.

ALTER USER st0
QUOTA UNLIMITED ON st;
//st tablespace에 대해 quota를 설정한다.

SELECT username, tablespace_name, max_bytes, max_blocks FROM dba_ts_quotas
WHERE username = 'ST0';
// st0에게 unlimit quota가 할당되었다.

CONN st0/st0

INSERT INTO st0 VALUES (1);
// quota 량이 할당됐기 때문에 정상적으로 입력 됨

 

[실습]user 삭제

<명령어 조회>

DROP USER <user명> [CASCADE]
- User를 삭제한다.
- CASCADE : User를 삭제하기 전 스키마의 모든 segment를 먼저 삭제한다.

CONN / as sysdba

 

SELECT owner, table_name FROM dba_tables
WHERE owner = 'ST0';

 

DROP USER st0;

// st0 스키마는 table이 존재함으로 삭제되지 않는다.

 

DROP TABLE st0.st0;

DROP USER st0;

// table을 삭제하고 user를 삭제

DROP USER te CASCADE;

// cascade 옵션은 segment 존재 유무와는 상관없이 user를 삭제한다.

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

// te, st0 계정이 삭제 된 것을 확인

 

* 용어 및 개념정리

- oracle은 임의적 접근 제어를 사용한다.