오라클 관리 02 사용자

Oracle 사용자 생성 및 관리 절차 정리

1. Schema와 User의 개념

• User: 데이터베이스에 접근할 수 있는 계정, Oracle 서버에 로그인하여 데이터베이스에서 작업을 수행할 수 있다. scott 사용자로 로그인하면 scott 계정을 통해 데이터를 관리할 수 있다.
• Schema: 특정 사용자가 소유한 모든 데이터베이스 객체들의 집합이다. 사용자 계정에 속한 테이블, 인덱스, 뷰, 제약 조건, 트리거 등이 모두 포함된다. HR 사용자가 소유한 모든 객체들의 집합이 HR schema이다.
• 일반적으로 schema와 username은 혼용되어 사용되며, schema는 데이터를 저장하는 구조를 의미하고, user는 데이터를 다루는 사용자를 의미한다.

 

2. 사용자 생성하기

Oracle에서 새로운 사용자(User)를 생성하는 단계

1. Tablespace 생성: 새로운 사용자를 위한 기본 테이블스페이스(Default Tablespace)와 임시 테이블스페이스(Temporary Tablespace)를 생성해야 한다.
2. 사용자 생성: 테이블스페이스를 생성한 후, 새로운 사용자를 만들고 해당 사용자가 사용할 테이블스페이스와 임시 테이블스페이스를 지정한다.
3. 권한 부여: 사용자가 데이터베이스 작업을 할 수 있도록 필요한 권한을 부여한다.

 

기본 테이블스페이스(Tablespace) 생성

Oracle 서버는 기본적으로 5개의 테이블스페이스를 제공한다. 그러나 실제 운영 환경에서는 서비스별로 독립적인 테이블스페이스를 생성하여 사용자 데이터를 저장하는 것이 권장된다. 테이블스페이스는 각 사용자의 데이터를 저장할 논리적 공간이며, 서비스별로 구분하여 사용할 수 있다.

새로운 업무나 서비스가 생길 경우, 이 서비스와 관련된 많은 데이터 객체(테이블, 인덱스 등)가 생성될 수 있다. 이 경우, 데이터를 효율적으로 관리하기 위해 별도의 테이블스페이스를 만들고, 이 테이블스페이스를 사용하는 사용자를 생성하는 것이 일반적이다.

Temporary Tablespace 생성

사용자는 쿼리 실행 중에 정렬 작업이나 임시 데이터를 저장하기 위해 임시 테이블스페이스(Temporary Tablespace)를 사용한다. 각 사용자는 자신의 임시 테이블스페이스를 지정받게 되며, 이를 통해 성능을 최적화할 수 있다.

사용자 생성

새로운 사용자를 생성할 때는 기본 테이블스페이스와 임시 테이블스페이스를 지정해 줘야 한다. 또한, 사용자의 데이터 저장 용량 제한(quota)을 설정할 수 있다. 권한 부여는 필수적이며, 사용자가 필요한 작업을 수행할 수 있도록 적절한 권한을 설정한다.

Profile과 권한 설정

사용자에게 Profile을 설정하여 암호 정책이나 리소스 제한을 적용하여 사용자의 암호 복잡성, 로그인 실패 횟수, 세션 타임아웃 등을 관리한다. 또한, 권한은 사용자의 역할에 따라 적절하게 부여된다. 기본적인 권한으로는 CONNECT, RESOURCE와 같은 권한을 부여할 수 있다.

 

 

 

 

Oracle에서 사용자 생성 및 테이블스페이스 관리 

요구사항:

• 새로 런칭된 서비스의 데이터를 위한 사용자 및 테이블스페이스 생성.
• 기본 테이블 데이터는 ts_appdata라는 테이블스페이스에 저장.
• 인덱스는 ts_app_idx 테이블스페이스에 저장.
• 임시 테이블스페이스는 temp_app으로 설정.

1. 테이블스페이스 생성

 - 기본 테이블스페이스 (ts_appdata) 생성
데이터 저장을 위한 기본 테이블스페이스 생성:

 

  - 인덱스 테이블스페이스 (ts_app_idx) 생성
인덱스 저장을 위한 테이블스페이스 생성:

 

 

 

 

 

 

2. 임시 테이블스페이스 생성

임시 데이터를 처리하기 위한 테이블스페이스 생성:

 

 

 

 

• create user appuser: appuser라는 이름의 사용자를 생성한다.
• identified by 1234: 생성된 사용자의 비밀번호를 1234로 설정한다.
• default tablespace ts_appdata: 사용자의 기본 테이블스페이스를 ts_appdata로 설정한다. 즉, 이 사용자가 테이블을 생성할 때 기본적으로 이 테이블스페이스가 사용된다.
• temporary tablespace temp_app: 사용자의 임시 테이블스페이스를 temp_app으로 설정한다. 임시 데이터가 필요할 때 이 테이블스페이스가 사용된다.
• quota unlimited on ts_appdata: ts_appdata 테이블스페이스에 대해 사용자가 사용할 수 있는 공간에 제한이 없음을 설정한다.

 

 

3. 사용자 생성

새로운 사용자 appuser를 생성하고 기본 테이블스페이스 및 임시 테이블스페이스를 설정:

 

 

 

4. 권한 부여

appuser에게 데이터베이스 자원에 접근하고 사용할 수 있는 권한을 부여:

 

 

 

appuser 로 접속 완료

 

 

 

 

5. 사용자 정보 확인

(1) default tablespace와 temporary tablespace 정보 확인하기

사용자의 default tablespace와 temporary tablespace 정보를 확인하는 방법이다.

 

 

 

 

6. Profile 관리

Profile은 사용자 계정에 대한 제약 사항을 설정하는 데 사용된다. 비밀번호 만료 주기, 로그인 시도 제한, 계정 잠금 등의 설정을 할 수 있다.

(1) Password 관련 Profile 파라미터

1. Failed_login_attempts: 지정된 횟수만큼 로그인 실패 시 계정을 잠근다.
   • 예: failed_login_attempts = 5 → 로그인 실패 5회 후 계정 잠금.
2. Password_lock_time: 계정 잠금 후 잠금 유지 기간을 설정한다. 단위는 '일'.
   • 예: password_lock_time = 1 → 1일 동안 잠금 상태 유지.
3. Password_life_time: 동일한 암호를 사용할 수 있는 최대 기간을 설정한다. 단위는 '일'.
   • 예: password_life_time = 90 → 암호를 90일간 사용 가능.
4. Password_grace_time: 암호가 만료된 후 암호를 변경할 수 있는 유예 기간을 설정한다.
   • 예: password_grace_time = 10 → 만료 후 10일의 유예 기간 제공.
5. Password_reuse_time: 동일한 암호를 다시 사용하려면 경과해야 하는 최소 기간을 설정한다.
   • 예: password_reuse_time = 365 → 동일한 암호를 다시 사용하려면 1년이 지나야 가능.
6. Password_reuse_max: 동일한 암호를 다시 사용할 수 있는 최대 횟수를 지정한다.
   • 예: password_reuse_max = 3 → 동일한 암호를 최대 3번까지 재사용 가능.
7. Password_verify_function: 사용자가 입력한 암호가 복잡성 규칙을 준수하는지 확인하는 함수이다. Oracle의 기본 함수인 verify_function을 사용할 수 있으며, 사용자 정의 함수를 만들어 적용할 수도 있다.
   • 암호는 최소 4자 이상이어야 한다.
   • 암호는 사용자 계정명과 달라야 한다.
   • 암호는 특수 문자, 알파벳, 숫자를 포함해야 한다.
   • 암호는 이전 암호와 3자 이상 달라야 한다.

 

 

 

 

 

 

 

Password 관련 Profile 생성하기

요구 조건에 따라 sample_prof라는 이름의 password 관련 Profile을 생성하는 예시이다.

조건:

• 조건 1: 로그인 시도 5회 실패 시 계정을 5일 동안 사용할 수 없게 함
• 조건 2: 계정의 암호는 10일에 한 번씩 변경해야 함
• 조건 3: 동일한 암호는 1일 동안 다시 사용할 수 없게 함

CREATE PROFILE sample_prof LIMIT
  FAILED_LOGIN_ATTEMPTS 5            -- 로그인 시도 5회 실패 시
  PASSWORD_LOCK_TIME 5               -- 5일 동안 계정 잠금
  PASSWORD_LIFE_TIME 10              -- 암호는 10일마다 변경
  PASSWORD_REUSE_TIME 1;             -- 동일한 암호는 1일 동안 재사용 불가

유저에게 profile 할당

 

 

 

 

Resource 관련 Profile 생성하기

요구 조건:

• 조건 1: 사용자당 연속적으로 CPU를 사용할 수 있는 시간을 18초로 제한
• 조건 2: 하루 중 8시간만 DB에 접속 가능하게 설정
• 조건 3: 16분 동안 활동이 없으면 강제로 접속을 해제

Resource 관련 Profile을 적용할 수 있도록 시스템 설정

 

 

CREATE PROFILE re_sample_prof LIMIT
  CPU_PER_SESSION 1800              -- 사용자당 연속 CPU 사용 시간 18초 (1800 = 18초 x 100)
  CONNECT_TIME 480                   -- 하루에 최대 8시간(480분) DB 접속 가능
  IDLE_TIME 16;                      -- 16분 동안 활동 없으면 접속 해제

 

사용자에게 할당

 

 

 

 

 

사용자에게 Profile 할당하기

(1) 사용자에게 현재 적용된 Profile 확인

appuser에게 적용된 Profile을 확인하려면 다음과 같이 쿼리를 실행한다.

 

 

(2) Profile 내용 확인

sample_prof Profile 내용 조회

SELECT profile, resource_name, resource, limit
FROM dba_profiles
WHERE profile = 'SAMPLE_PROF';

 

 

 

 

(3) 사용자에게 Profile 적용시키기

사용자에게 sample_prof와 re_sample_prof 적용

 

하나의 사용자에게 두 개의 Profile을 동시에 적용할 수는 없으므로, 필요한 파라미터를 모두 하나의 Profile에 통합해야 한다. ,두 Profile의 내용을 하나로 통합하여 적용하려면 다음과 같은 절차를 따른다.

1. Profile 통합: 필요한 모든 제한을 한 Profile로 통합한다

 

 

 

 

삭제

 

 

 

 

 

Privilege(권한) 관리하기

Oracle에서 권한은 사용자가 특정 작업을 수행할 수 있도록 허용하는 개념이다. Privilege(권한)는 크게 System Privilege와 Object Privilege로 나눌 수 있다.

• System Privilege: 시스템 전체에 영향을 미치는 권한이다.
• Object Privilege: 특정 객체(테이블, 인덱스 등)에 관련된 권한이다.

(1) 주요 System Privilege

(2) System Privilege 부여 및 확인

사용자에게 System Privilege를 부여하거나 제거하는 작업은 GRANT와 REVOKE 명령어를 사용한다.

권한 부여

appuser에게 서버에 접속할 수 있는 권한과 특정 테이블에 데이터를 삽입할 수 있는 권한을 부여

 

 

권한 제거

특정 권한을 제거하려면 REVOKE 명령어를 사용한다.

 

 

 

 

 

(3) Object Privilege 관리

Object Privilege는 테이블, 뷰, 시퀀스, 프로시저 등 특정 객체에 대해 주어지는 권한이다.

• SELECT: 테이블에서 데이터를 조회할 수 있는 권한
• INSERT: 테이블에 데이터를 삽입할 수 있는 권한
• UPDATE: 테이블의 데이터를 수정할 수 있는 권한
• DELETE: 테이블의 데이터를 삭제할 수 있는 권한

Object Privilege 부여

사용자에게 특정 테이블에 대한 Object Privilege를 부여하려면 

 

Object Privilege 제거

특정 테이블에 대한 권한을 제거하려면 

 

 

 

(4) Privilege 조회

사용자가 가진 권한을 조회하려면 DBA_SYS_PRIVS와 DBA_TAB_PRIVS 뷰를 사용한다.

• System Privilege 조회

 

 

 

Object Privilege 조회

 

 

• GRANTEE: 권한을 부여받은 사용자 (APPUSER).
• OWNER: 테이블의 소유자 (SYS).
• TABLE_NAME: 권한이 부여된 테이블의 이름 (EMPLOYEES).
• GRANTOR: 권한을 부여한 사용자 (SYS).
• PRIVILEGE: 부여된 권한의 종류 (SELECT).
• GRA: (없음) - 이 열은 일반적으로 부여된 권한이 있는지 여부를 나타낸다.
• HIE: (없음) - 이 열은 상속 가능 여부를 나타낸다.
• COM: (없음) - 이 열은 권한이 컴포지트인지 여부를 나타낸다.
• TYPE: 권한의 종류 (TABLE).

 

 

 

SYSOPER/SYSDBA Privilege

SYSOPER:

• STARTUP 및 SHUTDOWN: 데이터베이스를 시작하고 종료할 수 있음.
• ALTER DATABASE MOUNT/OPEN: 데이터베이스를 마운트하거나 오픈할 수 있음.
• ALTER DATABASE BACKUP CONTROLFILE TO ...: 컨트롤 파일을 백업할 수 있음.
• RECOVER DATABASE: 데이터베이스 복구 가능.
• ALTER DATABASE ARCHIVELOG: 데이터베이스의 아카이브 로그 모드를 변경할 수 있음.
• RESTRICTED SESSION: 제한된 세션으로 접속 가능.

SYSDBA:

• CREATE DATABASE: 새로운 데이터베이스를 생성할 수 있음.
• ALTER TABLESPACE BEGIN/END BACKUP: 테이블스페이스 백업을 시작/종료할 수 있음.
• RECOVER DATABASE UNTIL ...: 특정 시점까지 데이터베이스를 복구할 수 있음.

 

 

 

SYSTEM 관련 권한 할당 및 해제

 

 

 

 

 

 

Object 관련 Privilege

• 오브젝트 권한은 SELECT, INSERT, UPDATE, DELETE와 같은 특정 테이블에 대한 작업을 할 수 있는 권한이다.

 

Object 권한 할당 및 해제

 

 

 

 

APPUSER 스키마에 있는 apptest 테이블에 대해 HR 사용자에게 UPDATE 권한을 부여하고, 이 권한을 다른 사용자에게도 다시 부여할 수 있는 권한을 추가하는 것이다

 

 

 

with grant option:

•  HR 사용자가 부여받은 UPDATE 권한을 다른 사용자에게 다시 부여할 수 있도록 허용한다. 즉, HR 사용자는 자신이 가진 UPDATE 권한을 다른 사용자에게 전달할 수 있다.
• WITH GRANT OPTION은 오브젝트 권한에서 사용자가 부여받은 권한을 다른 사용자에게 다시 부여할 수 있도록 하는 옵션이다.
  • WITH ADMIN OPTION은 시스템 권한에서 사용되며, 권한 위임이 가능한다.
  • WITH GRANT OPTION으로 부여된 권한을 해제하면 그 권한을 받은 사용자까지 권한이 자동으로 해제된다.

 

 

 

 

 

권한해제

 

 

 

 

 

 

 

 

 

 

■ Role 

  

Oracle에서 Role은 여러 권한을 묶어서 하나의 단위로 관리할 수 있도록 만들어진 기능이다. Role을 사용하면 여러 권한을 일일이 부여하는 번거로움을 덜고, 하나의 Role만 부여함으로써 포함된 모든 권한을 사용자에게 전달할 수 있다. 

 

 

 

(1) Role 생성

(2) Role에 권한 할당

(3) Role을 사용자에게 할당

 

(4) 사용자가 가진 Role 확인

특정 사용자가 가지고 있는 Role을 조회하려면 DBA_ROLE_PRIVS 뷰를 사용할 수 있다.

 

 

 

 

(5) 특정 Role이 가진 권한 확인

특정 Role에 포함된 권한을 확인하려면 DBA_SYS_PRIVS 뷰를 사용한다.

 

• dba_sys_privs 뷰는 데이터베이스 사용자에게 부여된 시스템 권한을 보여주는 테이블이다. 

   

  grantee = 'CONNECT'

  1. GRANTEE: 권한이 부여된 사용자 이름 (CONNECT).
  2. PRIVILEGE: 부여된 권한의 이름:
     • SET CONTAINER: 플러그인 데이터베이스에서 컨테이너를 설정할 수 있는 권한.
     • CREATE SESSION: 데이터베이스에 연결할 수 있는 권한.
  3. ADM: 이 권한이 관리자가 부여한 것인지 여부.
  4. COM: 이 권한이 다른 사용자에게 부여될 수 있는지 여부.

  grantee = 'RESOURCE'

  1. GRANTEE: 권한이 부여된 역할 이름 (RESOURCE).
  2. PRIVILEGE: 부여된 권한의 이름:
     • CREATE TABLE: 테이블을 생성할 수 있는 권한.
     • CREATE OPERATOR: 사용자 정의 연산자를 생성할 수 있는 권한.
     • CREATE TYPE: 사용자 정의 데이터 타입을 생성할 수 있는 권한.
     • CREATE CLUSTER: 클러스터를 생성할 수 있는 권한.
     • CREATE TRIGGER: 트리거를 생성할 수 있는 권한.
     • CREATE INDEX TYPE: 사용자 정의 인덱스 타입을 생성할 수 있는 권한.
     • CREATE PROCEDURE: 저장 프로시저를 생성할 수 있는 권한.
     • CREATE SEQUENCE: 시퀀스를 생성할 수 있는 권한.
  3. ADM: 이 권한이 관리자가 부여한 것인지 여부.
  4. COM: 이 권한이 다른 사용자에게 부여될 수 있는지 여부.