[Oracle Admin] Chap 10. Oracle 사용자 관리

Chap 10. Oracle 사용자 관리

  1. Schema 와 User  

- User: 말 그대로 사용자 (ex, scott 같은)

- Schema: 특정 사용자(user) 가 만들어 놓은 모든 Object 의 집합 (table, index, vie constraint,m trigger, dblink, synonym, sequence 등등 모든 것)

1) User 생성하기

- User 생성 순서

* 생성할 사용자의 Default tablespace 결정 후 해당 tablespace 생성

* 생성할 사용자가 사용하는 temporary tablespace 생성

* 사용자 생성

* 적절한 profile 과 privilege (권한), role 등을 생성 후 할당.

- User 생성하기

########## 조건

User 명은 webuser,  패스워드는 webpwd, tablespace 는 TS_WEBHARD 라고 생성해서 모든 table은 여기에 저장되도록 하고, index 는 TS_WEB_IDX 라는 tablespace 라는 곳에다가 저장하기로 한다. 

그리고 temporary tablespace 는 temp_web 이라고 만들기로 한다. 

##########

## Default tablespace 생성

- 현재 tablespace  및 temp tablespace 조회

SYS> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE   MB FILE_NAME

---------- ---------- ---------------------------------------------

USERS    5 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1   90 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX  510 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM  720 /app/oracle/oradata/testdb/system01.dbf

EXAMPLE       313.125 /app/oracle/oradata/testdb/example01.dbf

SYS> select tablespace_name, bytes/1024/1024 MB , file_name from dba_temp_files;

TABLESPACE_NAME       MB FILE_NAME

------------------------------ ---------- ---------------------------------------------

 

TEMP       29 /app/oracle/oradata/testdb/temp01.dbf

- 새로운 tablespace 생성

SYS> create tablespace ts_webhard datafile '/app/oracle/oradata/testdb/ts_webhard.dbf' size 100M;

Tablespace created.

SYS> create tablespace ts_web_idx datafile '/app/oracle/oradata/testdb/ts_web_idx.dbf' size 10M;

 

Tablespace created.

- 다시 조회

SYS> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE   MB FILE_NAME

---------- ---------- ---------------------------------------------

USERS    5 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1   90 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX  510 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM  720 /app/oracle/oradata/testdb/system01.dbf

EXAMPLE       313.125 /app/oracle/oradata/testdb/example01.dbf

TS_WEBHARD  100 /app/oracle/oradata/testdb/ts_webhard.dbf

TS_WEB_IDX   10 /app/oracle/oradata/testdb/ts_web_idx.dbf

## temporary tablespace 생성 및 조회

SYS> create temporary tablespace temp_web tempfile '/app/oracle/oradata/testdb/temp_web.dbf' size 10M;

 

Tablespace created.

SYS> select tablespace_name, bytes/1024/1024 MB, file_name from dba_temp_files;

TABLESPACE_NAME       MB FILE_NAME

------------------------------ ---------- ---------------------------------------------

TEMP       29 /app/oracle/oradata/testdb/temp01.dbf

 

TEMP_WEB       10 /app/oracle/oradata/testdb/temp_web.dbf

## 사용자 생성

SYS> create user webuser identified by webpwd default tablespace ts_webhard temporary tablespace temp_web quota unlimited on ts_webhard quota 0m on system;

 

User created.

===> identified by webpwd                    : 암호를 지정

===> default tablespace ts_webhard       :  default tablespace 지정

===> temporary tablespace temp_web     : temporary tablespace 지정

===> quota unlimited on ts_webhard       : ts_webhard tablespace 에 할당량을 무제한으로 설정

===> quota 0m on system                      : system tablespace 를 사용하지 못하게 설정

## 권한 설정

SYS> grant resource, connect to webuser;

 

Grant succeeded.

## 접속 확인

SYS> conn webuser/webpwd

Connected.

 

WEBUSER> 

2) 사용자 정보 확인 및 수정하기

## 사용자 정보 확인

SYS> select username, default_tablespace, temporary_tablespace from dba_users where username='WEBUSER';

USERNAME       DEFAULT_TABLESPACE      TEMPORARY_TABLESPACE

------------------------------ ------------------------------ ------------------------------

WEBUSER       TS_WEBHARD      TEMP_WEB

## 사용자 정보 수정 (password 변경하기)

SYS> alter user webuser identified by webpwd2;

User altered.

3) Profile 관리하기

- password profile 관련 parameter

 

Parameter	의미
failed_login_attempts	login 시도를 여기서 설정한 횟수만큼 실패할 경우 계정 잠금.    즉, 이 값을 5로 설정했는데 5번 틀렸을 경우 6번째부터는 로그인이 안된다.
password_lock_time	계정이 잠기면 몇일 동안 잠글 것인지를 설정할 수 있다.    단위는 일 수이다. DBA가 이 값과 상관없이 unlock 가능하다.
password_life_time	동일한 암호를 몇 일동안 사용할 수 있는지 설정한다 (단위는 일 수)
password_grace_time	password_life_time 이 만료 되어도 이 parameter 에서 지정된 값 만큼    더 암호를 변경할 기간을 허용한다.
password_reuse_time	동일한 암호를 다시 사용할 수 없도록 설정하는 기간.   즉, 암호를 변경하라고 했는데 사용자가 동일한 암호를 다시 사용하려고   할 수 있기에 같은 암호를 다시 쓸 수 없게 만들어야 할 때 사용하는    파라미터.
password_reuse_max	password_reuse_time 설정을 피해서 동일한 암호를 재사용을 할 경우    최대 사용 가능한 횟수를 지정하는 파라미터이다.
password_verify_function	사용자가 입력한 암호가 정말 암호로 적합한지를 점검한다.

 

- profile 생성하기 실습

### 로그인 시도 5회 실패 시 계정을 5 일 동안 사용 못하게 할 것.

### 계정의 암호는 10일에 한번씩 변경하게 할 것

### 동일한 암호는 10일 동안 사용 못하게 할 것

SYS> create profile sample_prof limit failed_login_attempts 5 password_lock_time 5 password_life_time 10 password_reuse_time 10;

 

Profile created.

- resource profile 관련 parameter

* 이 parameter 를 사용하려면 resource_limit = true 라는 설정이 되어 있어야 한다. 

* startup 할 때 사용되는 parameter file 위에 위 문장을 적어 놓거나 9i 버전 이상에 alter system set resource_limit=true; 를 실행하면 바로 적용이 된다. 

* 아래는 resource profile 관련 설정들이다. 

* CPU_PSER_SESSION : SESSION 이란 1명의 접속을 의미한다. 하나의 SESSION이 CPU 를 연속적으로 사용할 수 있는 최대 시간을 설정한다. 1/100초 단위이다. 

* SESSIONS_PER_USER : 하나의 사용자 계정으로 몇 명의 사용자가 동시에 접속 할 수 있는지 설정하는 파라미터이다. 

* CONNECT_TIME : 하루 동안 DB server 에 접속 할 수 있는 총 시간을 설정한다. 

* IDLE_TIME : 연속적으로 휴면 시간이 여기 값을 넘으면 접속을 해제한다. 이렇게 하는 이유는 보안적인 문제도 있고, 메모리 사용량 때문이기도 하다. 예를 들어, 사용자가 서버에 접속을 하면 server process 가 생성되고 PGA 가 할당이 된다. PGA 를 1M 로 줄 경우 1000 명의 사용자가 서버에 접속해서 아무 일도 안해도 PGA 가 1000M 를 사용하게 되는 것이다. 이런 일을 막기 위해 설정한다. 

* LOGICAL_READS_PER_SESSION : 한 session 에서 사용 가능한 최대 block 수를 설정한다. 

* PRIVATE_SGA : MTS / shared server 일 경우 해당 session 의 SGA 사용량을 bytes 단위로 설정한다. 

* CPU_PER_CALL : 하나의 call 당 cpu 를 점유할 수 있는 시간이며 1/100초 단위이다. 

* LOGICAL_READS_PER_CALL : 하나의 call 당 읽을 수 있는 block 의 개수를 의미.

- resource 관련 profile 만들고 사용자에게 할당하기

## 현재 resource_limit 설정 값 확인

SYS> show parameter resource_limit;

NAME     TYPE VALUE

------------------------------------ ----------- ------------------------------

resource_limit     boolean FALSE

## resource_limit 값 true 로 변경

SYS> alter system set resource_limit=true;

 

System altered.

SYS> show parameter resource_limit;

NAME     TYPE VALUE

------------------------------------ ----------- ------------------------------

 

resource_limit     boolean TRUE

## 아래 조건으로 resource profile 을 생성

## - 1명당 연속적으로 cpu 를 사용할 수 있는 시간을 10초로 제한할 것

## - 하루 중 8시간만 DB 에 접속 가능하게 할 것

## - 10분 동안 사용하지 않으면 강제로 접속을 끊을 것

SYS> create profile re_sample_prof limit    

  2  cpu_per_session 1000

  3  connect_time 480

  4  idle_time 10;

 

Profile created.

## webuser 사용자의 현재 profile 조회

SYS> select username, profile from dba_users where username='WEBUSER';

USERNAME       PROFILE

------------------------------ ------------------------------

 

WEBUSER       DEFAULT

## 처음 생성했던 SAMPLE_PROF 의 내용 보기

SYS> select * from dba_profiles where profile='SAMPLE_PROF';

PROFILE       RESOURCE_NAME     RESOURCE LIMIT

------------- ------------------------------ -------- ----------

SAMPLE_PROF   COMPOSITE_LIMIT     KERNEL   DEFAULT

SAMPLE_PROF   SESSIONS_PER_USER     KERNEL   DEFAULT

SAMPLE_PROF   CPU_PER_SESSION     KERNEL   DEFAULT

SAMPLE_PROF   CPU_PER_CALL     KERNEL   DEFAULT

SAMPLE_PROF   LOGICAL_READS_PER_SESSION      KERNEL   DEFAULT

SAMPLE_PROF   LOGICAL_READS_PER_CALL     KERNEL   DEFAULT

SAMPLE_PROF   IDLE_TIME     KERNEL   DEFAULT

SAMPLE_PROF   CONNECT_TIME     KERNEL   DEFAULT

SAMPLE_PROF   PRIVATE_SGA     KERNEL   DEFAULT

SAMPLE_PROF   FAILED_LOGIN_ATTEMPTS     PASSWORD 5

SAMPLE_PROF   PASSWORD_LIFE_TIME     PASSWORD 10

PROFILE       RESOURCE_NAME     RESOURCE LIMIT

------------- ------------------------------ -------- ----------

SAMPLE_PROF   PASSWORD_REUSE_TIME     PASSWORD 10

SAMPLE_PROF   PASSWORD_REUSE_MAX     PASSWORD DEFAULT

SAMPLE_PROF   PASSWORD_VERIFY_FUNCTION     PASSWORD DEFAULT

SAMPLE_PROF   PASSWORD_LOCK_TIME     PASSWORD 5

SAMPLE_PROF   PASSWORD_GRACE_TIME     PASSWORD DEFAULT

 

16 rows selected.

## 두번째 생성했던 re_sample_prof 내용 보기

SYS> select * from dba_profiles where profile='RE_SAMPLE_PROF';

PROFILE RESOURCE_NAME       RESOURCE LIMIT

--------------- ------------------------------ -------- ----------

RE_SAMPLE_PROF COMPOSITE_LIMIT       KERNEL DEFAULT

RE_SAMPLE_PROF SESSIONS_PER_USER       KERNEL DEFAULT

RE_SAMPLE_PROF CPU_PER_SESSION       KERNEL 1000

RE_SAMPLE_PROF CPU_PER_CALL       KERNEL DEFAULT

RE_SAMPLE_PROF LOGICAL_READS_PER_SESSION      KERNEL DEFAULT

RE_SAMPLE_PROF LOGICAL_READS_PER_CALL       KERNEL DEFAULT

RE_SAMPLE_PROF IDLE_TIME       KERNEL 10

RE_SAMPLE_PROF CONNECT_TIME       KERNEL 480

RE_SAMPLE_PROF PRIVATE_SGA       KERNEL DEFAULT

RE_SAMPLE_PROF FAILED_LOGIN_ATTEMPTS       PASSWORD DEFAULT

RE_SAMPLE_PROF PASSWORD_LIFE_TIME       PASSWORD DEFAULT

PROFILE RESOURCE_NAME       RESOURCE LIMIT

--------------- ------------------------------ -------- ----------

RE_SAMPLE_PROF PASSWORD_REUSE_TIME       PASSWORD DEFAULT

RE_SAMPLE_PROF PASSWORD_REUSE_MAX       PASSWORD DEFAULT

RE_SAMPLE_PROF PASSWORD_VERIFY_FUNCTION       PASSWORD DEFAULT

RE_SAMPLE_PROF PASSWORD_LOCK_TIME       PASSWORD DEFAULT

RE_SAMPLE_PROF PASSWORD_GRACE_TIME       PASSWORD DEFAULT

 

16 rows selected.

## webuser 사용자에게 profile 적용시키고 확인하기

SYS> alter user webuser profile sample_prof;

User altered.

SYS> alter user webuser profile re_sample_prof;

User altered.

SYS> select username, profile from dba_users where username='WEBUSER';

USERNAME       PROFILE

------------------------------ ---------------

 

WEBUSER       RE_SAMPLE_PROF

- 사용 안하는 profile 삭제하기

## profile 은 현재 사용자에게 할당되어 있는 profile 은 기본적으로 삭제가 안되고 cascade 옵션으로 삭제하면 가능하다. 이렇게 삭제하면 해당 사용자는 default profile 을 사용하게 된다. 

SYS> drop profile re_sample_prof;        

drop profile re_sample_prof

*

ERROR at line 1:

ORA-02382: profile RE_SAMPLE_PROF has users assigned, cannot drop without

CASCADE

SYS> drop profile re_sample_prof cascade;

Profile dropped.

SYS> select username, profile from dba_users where username='WEBUSER';

USERNAME       PROFILE

------------------------------ ---------------

WEBUSER       DEFAULT

4) privilege (권한) 관리하기

- profile 이 사용자에게 어떤 것들을 못하게 제한하는 것이라면, privilege 는 사용자에게 어떤 것들을 하게 허락해 주는 것이 목적이다. privilege 는 크게 system 과 object 관련 privilege 가 있다. 

- System privilege

 

대 분 류	privilege	설    명
index	create any index	소유자에 상관없이 모든 테이블에 index 를 생성할 수 있는 권한
	drop any index	소유자에 상관없이 모든 테이블에 index 를 삭제할 수 있는 권한
	alter any index	소유자에 상관없이 모든 테이블에 index 를 수정할 수 있는 권한
table	create table	본인 계정에 테이블을 생성할 수 있는 권한
	create any table	소유자에 상관없이 다른 user 이름으로 테이블을 생성할 수 있는 권한
	alter any table	소유자에 상관없이 모든 테이블의 구조를 수정할 수 있는 권한
	drop any table	소유자에 상관없이 모든 사용자의 테이블을 삭제할 수 있는 권한
	update any table	소유자에 상관없이 모든 사용자의 테이블을 업데이트 할 수 있는 권한
	delete any table	소유자에 상관없이 모든 사용자의 테이블의 데이터를 삭제할 수 있는 권한
	insert any table	소유자에 상관없이 모든 사용자의 테이블에 데이터를 insert 할 수 있는 권한
session	create session	서버에 접속할 수 있는 권한
	alter session	접속 상태에서 환경값을 변경할 수 있는 권한
	restricted session	Restricted 모드로 open 된 DB 에 접속 할 수 있는 권한
tablespace	create tablespace	tablespace 를 만들 수 있는 권한
	alter tablespace	tablepsace 를 수정 할 수 있는 권한
	drop tablespace	tablespace 를 삭제할 수 있는 권한
	unlimited tablespace	tablespace 사용용량을 무제한으로 허용 하는 권한, 즉, quota 옵션 적용을 받지 않게 됨.

 

- Object 관련 privilege 권한 할당 / 해제하기 

Object 관련 privilege 는 주로 DML (select, insert, update, delete) 등을 할 수 있는 권한을 말한다. 

## webuser 사용자에게 scott 의 emp 테이블을 조회할 수 있도록 권한 설정

SYS> grant select on scott.emp to webuser;

 

Grant succeeded.

[oracle@chacha ~]$ sqlplus webuser/webpwd2

WEBUSER> select * from scott.emp;

     EMPNO ENAME      JOB       MGR HIREDATE       SAL COMM  DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

      7369 SMITH      CLERK      7902 17-DEC-80       800      20

      7499 ALLEN      SALESMAN      7698 20-FEB-81      1600  300      30

      7521 WARD       SALESMAN      7698 22-FEB-81      1250  500      30

      7566 JONES      MANAGER      7839 02-APR-81      2975      20

      7654 MARTIN     SALESMAN      7698 28-SEP-81      1250 1400      30

      7698 BLAKE      MANAGER      7839 01-MAY-81      2850      30

      7782 CLARK      MANAGER      7839 09-JUN-81      2450      10

      7788 SCOTT      ANALYST      7566 19-APR-87      3000      20

      7839 KING       PRESIDENT   17-NOV-81      5000      10

      7844 TURNER     SALESMAN      7698 08-SEP-81      1500    0      30

      7876 ADAMS      CLERK      7788 23-MAY-87      1100      20

      7900 JAMES      CLERK      7698 03-DEC-81       950      30

      7902 FORD       ANALYST      7566 03-DEC-81      3000      20

      7934 MILLER     CLERK      7782 23-JAN-82      1300      10

 

14 rows selected.

## webuser 사용자에게 scott 의 emp 테이블에 update 할 수 있는 권한을 생성하되, webuser 가 다른 사용자에게도 이 권한을 줄 수 있도록 설정

SYS> grant update on scott.emp to webuser with grant option;

 

Grant succeeded.

## webuser 가 가진 scott 관련 권한 해제

SYS> revoke select on scott.emp from webuser;

 

Revoke succeeded.

- SYSOPER / SYSDBA privilege

 

Privilege	할 수 있는 일
SYSOPER	Startup / Shutdown
	Alter database mount / open
	Alter database backup control file to ..
	Recover database
	Alter database archivelog
	Restricted session
SYSDBA (모든 것 가능)	SYSOPER privilege with admin option
	create database
	Alter tablespace .. begin backup / end backup
	Recover database until

 

- SYSTEM 관련 권한 할당 / 조회 / 해제하기

### webuser 사용자에게 create table, create session 권한을 할당 / 조회 / 해제

SYS> grant create table, create session to webuser;

Grant succeeded.

SYS> select * from dba_sys_privs where grantee='WEBUSER';

GRANTEE       PRIVILEGE ADM

------------------------------ ---------------------------------------- ---

WEBUSER       UNLIMITED TABLESPACE NO

WEBUSER       CREATE TABLE NO

WEBUSER       CREATE SESSION NO

SYS> revoke create table from webuser;

Revoke succeeded.

SYS> select * from dba_sys_privs where grantee='WEBUSER';

GRANTEE       PRIVILEGE ADM

------------------------------ ---------------------------------------- ---

WEBUSER       UNLIMITED TABLESPACE NO

 

WEBUSER       CREATE SESSION NO

============> 위의 adm 컬럼은 with admin option 여부를 나타낸다. with admin option 이란 권한 위임하는 기능이다. 예를 들어, DBA 가 A 사용자에게 create table 이란 권한을 줄 때 그냥 주면 A 사용자는 create table 을 수행하는 권한만 받게 되지만, grant create table to A with admin option 이렇게 주면 A 사용자는 또 다른 사용자에게 with admin option 과 함께 받은 create table 이라는 권한을 마치 DBA 처럼 할당 / 회수 할 수 있다. 

- with grant option / with admin option 

이 둘의 차이점은 DBA 가 A 사용자에게 with admin option 을 사용하여 권한을 주고 A 사용자가 다시 B 사용자에게 권한을 주었을 때, DBA 가 A 사용자에게서 권한을 해제해도 B 사용자의 권한은 해제가 안되지만, with grant option 은 A 사용자의 권한을 해제하면 자동으로 A 사용자에게서 받은 B 사용자의 권한까지 다 해제된다. 

- SYSDBA 권한을 가지고 있는 계정 조회하기

SYS> grant sysdba to scott;

Grant succeeded.

SYS> select * from v$pwfile_users;

USERNAME       SYSDB SYSOP SYSAS

------------------------------ ----- ----- -----

SYS       TRUE  TRUE  FALSE

SCOTT       TRUE  FALSE FALSE

5) Role 관리하기

Role 이란 권한들의 그룹이라고 생각하면 된다. 일일히 필요한 권한들을 각 사용자에게 각각 할당하기 힘드니 여러가지 권한을들 묶어 놓고 role 하나를 주면 편하다. 

- Role 생성하기 

SYS> create role trole;

Role created.

- Role 에 create session, create table 권한 할당하기

SYS> grant create session, create table to trole;

Grant succeeded.

- webuser 사용자에게 trole 할당하기

SYS> grant trole to webuser;

Grant succeeded.

- 어떤 사용자가 어떤 role 을 사용하는지 확인하기

SYS> select * from dba_role_privs where grantee='WEBUSER';

GRANTEE       GRANTED_ROLE      ADM DEF

------------------------------ ------------------------------ --- ---

WEBUSER       RESOURCE      NO  YES

WEBUSER       TROLE      NO  YES

WEBUSER       CONNECT      NO  YES

- 어떤 Role 에 어떤 권한이 있는지 확인하기

SYS> select * from dba_sys_privs where grantee='CONNECT';

GRANTEE       PRIVILEGE ADM

------------------------------ ---------------------------------------- ---

CONNECT       CREATE SESSION NO

SYS> select * from dba_sys_privs where grantee='RESOURCE';

GRANTEE       PRIVILEGE ADM

------------------------------ ---------------------------------------- ---

RESOURCE       CREATE TRIGGER NO

RESOURCE       CREATE SEQUENCE NO

RESOURCE       CREATE TYPE NO

RESOURCE       CREATE PROCEDURE NO

RESOURCE       CREATE CLUSTER NO

RESOURCE       CREATE OPERATOR NO

RESOURCE       CREATE INDEXTYPE NO

RESOURCE       CREATE TABLE NO

8 rows selected.