[Admin][Oracle] 오라클 사용자의 생성과 권한의 설정

작성자 : 박상수
작성일자 : 2011.01.11
작업환경 : VMware7 [ Oracle Redhat Enterprise 4 u6]

1. 데이터베이스 유저 권한의 종류

시스템 권한 - 데이터베이스 접속, 사용자 생성 및 오브젝트 생성 등의 권한
오브젝트 권한 - 해당 오브젝트에 대한 전반적인 작업을 위한 권한

1-1 시스템 권한
CREATE : 데이터베이스 유저 생성 권한
SELECT ANY TABLE : 모든 유저의 테이블 조회 권한
CREATE ANY TABLE : 모든 유저의 테이블 생성 권한
CREATE SESSION : 데이터베이스 접속 권한
CREATE TABLE : 테이블 생성 권한
CREATE VIEW : 뷰 생성 권한
CREATE PROCEDURE : 프로시저 생성 권한
CREATE SEQUENCE : 시퀀스 생성 권한
SYSDBA : 데이터베이스를 관리하는 최고 권한
SYSOPER : 데이터베이스를 관리하는 권한

오라클을 설치면 기본적으로 생성되는 유저는 SYS, SYSTEM 이고 스키마를 체크하게 되면 HR, SCOTT 유저가 생성 됩니다. HR과 SCOTT는 아래와 같은 락을 해제 해야 합니다.
SQL> alter [hr, scott] identified by [ password ] account unlock;
정상적으로 해제 했을 경우 "OPEN" 상태여야 합니다.
SQL> select username, account_status from dba_users where username='SCOTT'

SYSDBA 또는 SYSOPER 권한을 획득한 유저가 수행 할 수 있는 작업을 정리 하겠습니다.

SYSOPER
데이터베이스 시작, 종료 및 상태 전환
테이블스페이스 및 컨트롤 파일 백업
데이터베이스 아카이브 또는 노아카이브 로그 모드 전환
파라미터 파일 생성
데이터베이스 완전 복구
접속 세션 제한

SYSDBA
SYSOPER가 소유한 모든 권한
데이터베이스 생성 및 삭제
데이터베이스 불완전 복구


1-2 오브젝트 권한
오브젝트 권한은 해당 오브젝트에 특정 SQL을 수행할 수 있게 해주는 권한 입니다. 오브젝트의 소유자는 해당 오브젝트에 대한 모든 권한을 가지게 됩니다. 또한, 소유자는 자신의 오브젝트에 대한 특정 권한을 다른 유저에게 부여 할 수 있습니다.

2. 사용자의 생성과 권한 설정

2-1 사용자 생성 문법

CREATE USER user_name
IDENTIFIED [ BY password  | EXTERNALLY ]
  [DEFAULT TABLESPACE tablespace]
  [TEMPORARY TABLESPACE tablespace]
  [QUOTA { interger [K | M] | UNLIMITIED } ON tablespace]
  [PASSWORD EXPIRE]
  [ACCOUNT { LOCK | UNLOCK}]
  [PROFILE { profile | DEFAULT }]

user_name : 생성 될 사용자 이름
BY password : 사용자가 데이터베이스에 의해 인증되도록 지정하며, 데이터베이스 유저 로그인 시 사용하는 비밀번호 이니다.
EXTERNALLY : 사용자가 운영 체제에 의해서 인증되도록 지정 합니다.
DEFAULT TABLESPACE : 사용자가 스키마를 위한 기본 테이블 스페이스를 지정 합니다.
TEMPORARY TABLESPACE : 사용자가 임시 테이블 스페이스를 지정 합니다.
QUOTA : 사용자가 사용할 테이블 스펭치스의 영역을 할당 합니다.
PASSWORD EXPIRE : 사용자가 SQL*PLUS를 사용하여 데이터베이스에 로그인할 때 암호를 재설정 하도록 합니다.
ACCOUNT LOCK/UNLOCK : 사용자가 계정을 명시적으로 잠그거나 풀 때 사용할 수 있습니다.(기본은 UNLOCK)
PROFILE : 자원 사용을 제어하고 사용자에게 사용되는 암호 제어 처리 방식을 지정하도록 사용됩니다.

2-2 사용자 생성 실습

[설명] 일반 유저로 권한으로 사용자를 만들면 사용자 권한이 없어서 사용자를 생성 할 수 없다고 에러가 나옵니다.
SQL> conn scott/oracle
Connected.
SQL> create user test identified by test;
create user test identified by test
                               *
ERROR at line 1:
ORA-01031: insufficient privileges

[설명] SYS 유저로 접속 하여 테이블을 만들어 보겠습니다. 정상적으로 유저를 생성 하였습니다.
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> create user test identified by test;
User created.

[참고사항] 임시 테이블스페이스를 지정해 주지 않으면 시스템 테이블스페이스가 기본으로 지정 되지만 시스템 테이블스페이스에 단편화가 발생 할 수 있으므로 사용자를 생성할 때 임시 테이블스페이스를 따로 지정해 주는 것이 좋습니다.
또한 DEFAULT TABLESPACE도 사용자를 생성할 때 지정해 주지 않으면 기본적으로 시스템 테이블스페이스가 지정 됩니다. 하지만 사용자를 생성할대 DEFAULT TABLESPACE를 지정을 해서 사용자가 소유한 데이터와 객체들의 저장 공간을 별도로 관리를 하는 것이 좋습니다.


[설명] TEST 유저를 접속 해보겠습니다. 접속할려면 권한을 부여해줘야 합니다.
SQL> conn test/test
ERROR:
ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

[설명] 접속하여 오브젝트의 권한을 부여합니다.
SQL> conn / as sysdba
Connected.
SQL> GRANT connect, resource TO TEST;
Grant succeeded.
SQL> conn test/test
Connected.

3. 사용자의 변경 및 삭제

User를 변경하기 위해서는 ALTER USER 문을 이용해야 합니다.
ALTER USER 문으로 변경가능한 옵션은 아래와 같습니다.
1. 비밀번호
2. DEFAULT TABLESPACE
3. TABLESPACE 분배 할당
4. 운영체제 인증
5. TEMPORARY TABLESPACE
6. Profile 및 Default 역할

3-1 사용자 수정 문법
ALTER user_name
  [IDENTIFIED {BY password | EXTERNALLY }]
  [DEFAULT TABLESPACE tablespace]
  [TEMPORARY TABLESPACE tablespace]
  [PASSWORD EXPIRE]
  [ACCOUNT {LOCK | UNLOCK}]

설명은 2-1을 참조하시기 바랍니다.

3-2 사용자 수정 실습

[설명] SYSTEM 유저로 접속 합니다.
SQL> conn system/oracle
Connected.
SQL> show user
USER is "SYSTEM"

[설명] scott 계정의 암호를 tiger로 변경 합니다.
SQL> ALTER USER scott IDENTIFIED BY tiger;  
User altered.

[설명] 변경된 암호를 확인합니다.
SQL> conn scott/tiger;
Connected.

[설명] 위와 동일한 방법으로 유저의 암호를 변경 할 수 있습니다.
SQL> conn system/oracle;
Connected.
SQL> ALTER USER scott IDENTIFIED BY oracle;
User altered.
SQL> conn scott/oracle
Connected.

3-3 사용자 삭제 실습

[설명] SYS 유저로 접속 합니다. 
SQL> select * from all_users where username='TEST';
USERNAME       USER_ID
------------------------------------------------------------ ----------
CREATED
------------
TEST             64
11-JAN-11

[설명] SYS 유저로 일반 유저를 삭제 처리 합니다. 여기에서 CASCADE 옵션은 사용자 이름과 관련된 모든 데이터베이스 스키마가 데이터 딕셔너리 캐시에서 모두 삭제 되며 모든 스키마 객체들 또한 물리적으로 삭제 됩니다.
SQL> show user
USER is "SYS"
SQL> DROP USER TEST CASCADE;
User dropped.

[설명] 정상적으로 삭제를 완료 하였습니다.
SQL> select * from all_users where username='TEST';
no rows selected