오라클 사용자 관리하기

오라클 데이터베이스를 설치한 후 자동으로 생성되는 사용자 계정은 다음과 같다.

SYS         :  오라클 슈퍼 사용자로 데이터베이스에서 발생하는 모든 문제를 처리할 수 있는 권한을 가짐.

SYSTEM  :  오라클 데이터베이스를 유지. 보수할 때 사용하는 사용자

                  SYS와 달리 데이터베이스를 생성할 권한을 가지지 않음.

SCOTT     :  샘플 사용자

1. 오라클 사용자 생성하기

오라클 사용자를 생성하기 위해서는 사용자 암호에 관련된 사항, 권한에 관련된 사항, 저장 구조에 대한 사항과 자원 사용에 대한 사항

등 네 가지 사항을 고려해야 한다.

2. 사용자 암호 설정하기

생성하는 오라클 사용자의 암호는 [IDENTIFIED BY] 절에 의해 설정한다. 예를 들어 TEST 사용자를 생성하고 암호를 TEST123

으로 설정해 보자.....

 SQL> CREATE USER TEST IDENTIFIED BY TEST123;

3. 사용자 암호 변경하기

사용자의 암호를 변경하기 위해서는 ALTER USER 문을 사용하고 사용자를 삭제하려면 DROP USER 문을 사용한다.

만일 사용자가 만들어 놓은 모든 테이블, 인덱스, 뷰 등을 함께 삭제하려면 다음과 같이 CASCADE 절을 사용한다.

 SQL> ALTER USER TEST IDEDTIFIED BY TEST456;      //사용자 암호를 변경

 SQL> DROP USER TEST CASCADE;        //사용자를 삭제한다.

4. 사용자 암호 잠그기/만료하기

데이터베이스 사용자의 암호를 보호하기 위해 암호를 강제로 만료(expire)시키거나 사용자 게정의 사용을 제한할 필요가 있다.

사용자 암호를 만료시키거나 잠글 수 있는 권한은 DBA(데이터베이스 관리자)만이 가지고 있으므로 반드시 SYS 사용자 또는

SYSTEM 사용자로 로그인 해야 한다.

사용자 암호를 만료시키기 위한 구문은 다음과 같다.

사용자 암호를 만료시킨 후 사용자가 데이터베이스에 접속을 시도하면 새로운 암호를 요구한다.

 SQL> ALTER USER TEST

          PASSWORD EXPIRE;                 // 사용자 암호 만료시키기

 SQL> CONNECT TEST/TEST123;

          TEST에 대한 암호를 변경합니다.

          새로운 암호 : *****

          새로운 암호를 다시 입력합니다. : ******

          암호가 변경되었습니다.

          연결 되었습니다.

5. 사용자 프로파일 관리

프로파일(profile) 객체를 사용하면 좀더 체계적이고 다양한 옵션을 사용하여 암호에 대한 보안 기능을 강화할수 있다.

프로파일은 CREATE PROFILE 구뭉을 사용하여 작성한다. CREATE PROFILE 구문의 형식은 다음과 같다.

 SQL> CREATE PROFILE 프로파일이름 LIMIT

                    [RESOURCE_PARAMETER 값]

                    [PASWORD_PARAMETER 값];

SQL> CREATE PROFILE TEST_PROFILE LIMIT

                        FAILED_LOGIN_ATTEMPTS 5

                        PASSWORD_LOCK_TIME 7

                        PASSWORD_LIFE_TIME 30

                        PASSWORD_GRACE_TIME 3

위 예제에서 패스워드를 입력할 때 5회(FAILED_LOGIN_ATTEMPTS)를 실패하면 더 이상 입력할 수 없도록 하고,

그 후 7일간(PASSWORD_LOCK_TIME)은 패스워드를 입력할 수 없도록 했다. 또한 패스워드의 유호 일수(PASSWORD_LIFE_TIME)

인 30일이 지나면 3일(PASSWORD_GRACE_TIME) 동안 경고 메시지를 표시하도록 설정 하였다.

생성한 프로파일의 변경은 ALTER PROFILE 문을, 삭제는 DROP PROFILE 문을 사용하며 형식은 다음과 같다.

 ALTER PROFILE 프로파일이름 LIMIT

        [RESOURCE_PARAMETER 값]

        [PASWORD_PARAMETER 값];

 DROP PROFILE 프로파일이름 [CASCADE];

6. 데이터베이스 권한과 역할

오라클 데이터베이스 시스템 권한은 GRANT 문을 사용하여 사용자 또는 데이터베이스 관리자 역할을 부여할 수 있다.

 GRANT [시스템권한 | ALL] TO [사용자 | 역할(ROLE) | PUBLIC]

            WITH ADMIN OPTION ;

GRANT 명령문의 PUBLIC 옵션과 WITH ADMIN OPTION 옵션에 대해 알아보자.

PUBLIC 옵션은 시스템 권한이나 개체 권한을 데이터베이스에 생성되어 있는 모든 사용자에게 부여할 때 사용하는 옵션이다.

사용자에게 권한을 부여할 때 WITH ADMIN OPTION 옵션을 사용하면 사용자가 데이터베이스 관리자가 아님에도 불구하고 자신이

가지는 권한을 다른 사람에게 부여할 수 있다.

(1) SYSTEM 사용자로 접속하고 새로운 사용자(TEST)를 생성하자.

 SQL> CONNECT SYSTEM/MANAGER ;

(2) TEST 사용자에게 CREATE TABLE 권한을 부여하자.

 SQL> GRANT CREATE TABLE TO TEST ;

(3) CREATE SESSION 권한 설정

SQL> GRANT CREATE SESSION TO TEST ;

(4) 시스템 권한의 철회는 REVOKE 문을 사용하여 수행한다.

SQL> REVOKE UPDATE ANY TABLE FROM PUBLIC ;

7. 권한 조회하기

사용자 또는 데이터베이스 역할에 부여된 개체 권한은 데이터 사전 뷰를 사용하여 조회가 가능하다.

데이터 사전 뷰는 데이터베이스만이 조회할 수 있는 뷰와 일반 사용자 개체 권한 뷰 그리고 모든 사용자가 조회할 수 있는

개체 권한 뷰 등이 있다.

(1) DBA_TAB_PRIVS 를 사용한 권한 조회

SQL> SELECT * FROM DBA_TAB_PRIVS WHERE OWNER='SCOTT' ;

(2) DBA_COL_PRIVS 을 사용한 권한 조회

SQL> SELECT * FROM DBA_COL_PRIVS WHERE OWNER='SCOTT' ;

8. 역할

사용자가 데이터베이스에 접속하여 특정 작업을 수행하려면 특정 작업에 대한 권한을 가지고 있어야 한다.

그러나 오라클 데이터베이스 권한은 종류가 다양하므로 각 사용자에게 모두 부여하는 것은 많은 시간이 소요될 뿐만 아니라

사용자가 가지고 있는 권한에 대한 관리가 어렵기 때문에 이를 해결하기 위하여 특정 사용 목적에 따라 권한들을 한데 묶어

놓았는데, 이를 역할(ROLE) 이라 한다.

사용자에게 오라클의 역할을 부여하기 위해서는 GRANT 문을, 철회하기 위해서는 REVOKE 문을 사용한다.

 GRANT 역할(ROLE) TO 사용자 ;            // 사용자 역할을 부여

 SQL> GRANT CONNECT, RESOURCE TO TEST ;

 REVOKE 역할(ROLE) FROM 사용자 ;     // 사용자 역할을 철회

 SQL> REVOKE CONNECT, RESOYRCE FROM TEST ;

오라클 데이터베이스 생성 시 제공되는 역할

CONNECT     :  데이터베이스에 접속할 수 있는 권한들을 가진 역할

                       SQLBSQ 스크립트에 의해 생성됨.

                       포함되는 시스템 권한 : ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LIKE, CREATE SEQUENCE,

                                                       CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW

RESOURCE   :  오라클 데이터베이스의 기본 개체(테이블, 뷰, 인덱스 등)를 생성. 변경. 삭제할 수 있는 권한을 가지는 역할.

                       QLBSQ 스크립트에 의해 생성됨.

                       포함되는 시스템 권한 : CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE

                                                       CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE

DBA              :  오라클 데이터베이스를 관리하기 위해 필요한 권한들을 가진 역할로 124개의 권한이 부여되어 있음.

                       SQL.BSQ 스크립트에 의해 생성됨.

                       WITH ADMIN OPTION 을 가진 모든 시스템 권한들이 포함.

SYSDBA        :  데이터베이스 시작과 종료 및 관리를 하기 위해 필요한 역할.

SYSOPER     :  SYSDBA 권한과 데이터베이스를 생성할 수 있는 역할.

* 오라클 데이터베이스 역할 생성하기

역할의 생성은 CREATE ROLE 문을 사용하고, 형식은 다음과 같다.

CREATE ROLE 역할이름

 [NOT IDENTIFIED | IDENTIFIED BY [암호 | EXTERNALLY] ;

하나의 역할을 생성하기 위해서는 다음 3단계의 작업이 필요하다.

- 역할 생성하기

- 역할에 권한 부여하기

- 사용자에게 역할 지정하기

SQL> CREATE ROLE ROLE_KONG ;          // 역할 생성하기

SQL> GRANT CREATE SESSION TO ROLE_KONG ;      

SQL> GRANT SELECT, UPDATE, DELETE ON EMP TO ROLE_KONG ;       // 역할에 권한 부여하기

SQL> GRANT ROLE_KONG TO KONG ;          // 사용자에게 역할 지정하기

9. 역할 활성/비활성 시키기

오라클 데이터베이스 역할에 의해 부여된 권한들은 SET ROLE 문을 사용자에게 활성 시키거나 비활성시킬수 있다.

SQL> SET ROLE ROLE-KONG ;    // ROLE_KONG 역할 이외에 다른 모든 역할들이 비활성 된다.

SQL> SET ROLE NONE ;             // EMP_MANAGER 역할을 제외한 모든 역할을 활성시킨다.

10. 디폴트 역할 설정하기

디폴트(DEFAULT) 역할은 사용자가 오라클 데이터베이스에 접속할 때 자동으로 활성화되는 역할이다.

ALTER USER 사용자이름 DEFAULT ROLE 역할이름

               ALL [EXCEPT 역할이름]

               NONE ;

SQL> ALTER USER KONG DEFAULT ROLE EMP_MANAGER   // ALTER USER 문을 사용하여 사용자(KONG)에게

                                                                                           EMP_MANAGER 역할을 디폴트 역할로 지정.

11. 역할 조회하기

특정 역할을 부여받은 사용자나 권한 등의 정보를 조회하기 위해서 다음과 같은 데이터 사전 뷰를 사용할 수 있다.

역할 조회에 사용하는 데이터 사전 뷰

DBA_SYS_PRIVS        :  사용자와 역할에 부여된 모든 권한 정보 조회.

USER_ROLE_PRIVS    :  사용자에게 부여된 역할 조회.

ROLES_SYS_PRIVS    :  역할에 부여된 시스템 권한 조회에 사용.

ROLE_TAB_PRIVS      :  역할에 부여된 테이블 개체의 권한 정보 조회.

ROLE_ROLE_PRIVS    :  역할에 부여된 다른 역할 조회.

SESSION_ROLE         :  현재 사용자의 세션에서 활성화되어 있는 역할 조회.

DBA_ROLES              :  데이터베이스 내에 존재하는 모든 역할 조회.

SQL> SELECT * FROM USER_ROLE_PRIVS ;

출처 : http://blog.daum.net/sowebpro/7120916