1. USER 와 SCHEMA 알아보기
USER: 오라클 서버에 접속하기 위한 사용자
SCHEMA: 특정 사용자가 만들어 놓은 모든 OBJECT의 집합
(예: SCOTT 사용자가 만든 TABLE, INDEX, VIEW, CONSTRAINT, TRIGGER, DBLINK, SYNONYM, SEQUENCE 등을 다 통틀어서 SCOTT SCHEMA라 한다.)
2. PROFILE 관리하기
사용자 계정의 행동에 제약사항을 두기 위해 사용
(1) PASSWORD PROFILE 관련 파라미터
1) FAILED_LOGIN_ATTEMPTS
로그인 실패시 잠금 횟수 설정
2) PASSWORD_LOCK_TIME
1에서 잠긴 계정을 며칠동안 잠글지 설정
3) PASSWORD_LIFE_TIME
동일한 암호 사용 가능일 수 설정
4) PASSWORD_GRACE_TIME
3에서 만료된 암호를 변경 기간 허용 설정
5) PASSWORD_REUSE_TIME
동일한 암호를 다시 사용할 수 없도록 설정하는 기간
6) PASSWORD_REUSE_MAX
5설정을 피해 재사용 할 경우 최대 사용 가능한 횟수 지정
7) PASSWORD_VERIFY_FUNCTION
암호를 복잡하게 만들기 위해 특정 함수 적용시키기
* 암호는 최소 4글자 이상이어야 한다.
* 암호는 사용자 계정과 달라야 한다.
* 암호는 하나의 특수문자, 알파벳, 숫자가 포함되어야 한다.
* 암호는 이전 암호와 3글자 이상 달라야 한다.
예제1. PASSWORD 관련 PROFILE 생성하기
조건1: 로그인 시도 3회 실패시 계정을 5일간 사용 금지
조건2: 계정의 암호는 15일에 한번씩 변경하게 할 것
조건3: 동일한 암호는 15일 동안 사용 금지
CREATE PROFILE sample_prof LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 5
PASSWORD_LIFE_TIME 15
PASSWORD_REUSE_TIME 15 ;
(2) RESOURCE PROFILE 관련 파라미터
컴퓨터의 자원인 CPU와 메모리 관련된 부분을 제어하는 파라미터
1) CPU_PER_SESSION
하나의 세션이 연속적으로 사용할 수 있는 최대 시간 설정. 1/100초 단위
※ 쿼리 수행시간이 다 다르기 때문에 신중하게 설정할 것
2) SESSIONS_PER_USER
하나의 사용자 계정으로 몇 명의 사용자가 동시 접속할 지 설정
3) CONNECT_TIME
하루 동안 DB서버에 접속을 허락하는 시간. 단위는 분
4) IDLE_TIME
연속적으로 휴면시간이 설정한 시간을 넘으면 접속 해제
5) LOGICAL_READS_PER_SESSION
한 세션에서 사용 가능한 최대 블록 수 지정
6) PRIVATE_SGA
MTS / SHARED SERVER 일 경우 해당 세션의 SGA 사용량을 BYTES 단위로 설정
7) CPU_PER_CALL
하나의 CALL 당 CPU를 점유할 수 있는 시간. 1/100 초 단위
8) LOGICAL_READS_PER_CALL
하나의 CALL 당 읽을 수 있는 BLOCK 의 갯수 지정
예제2. RESOURCE 관련 PROFILE 만들기
ALTER SYSTEM SET RESOURCE_LIMIT = true ;
조건1: 1명당 연속적으로 CPU를 사용할 수 있는 시간을 10초로 제한
조건2: 하루 중 8시간만 DB에 접속 가능하게 할 것
조건3: 10분 동안 사용하지 않으면 강제로 접속 끊을 것
CREATE PROFILE RE_SAMPLE_PROF LIMIT
CPU_PER_SESSION 1000
CONNECT_TIME 480
IDLE_TIME 10 ;
(3) 사용자에게 PROFILE 할당하기
1) 현재 모든 사용자가 적용 받고 있는 PROFILE 확인하기
SELECT username 사용자명, profile 적용프로파일
FROM dba_users
WHERE username = 'SCOTT' ;
사용자명 적용프로파일
------------------------------ ------------------------------
SCOTT DEFAULT
2) 해당 PROFILE 에 어떤 내용이 있는지 확인하기
SELECT *
FROM dba_profiles
WHERE profile = 'SAMPLE_PROF' ;
SELECT *
FROM dba_profiles
WHERE profile = 'RE_SAMPLE_PROF' ;
3) 사용자에게 PROFILE 적용시키고 확인하기
ALTER USER SCOTT PROFILE SAMPLE_PROF ;
ALTER USER SCOTT PROFILE RE_SAMPLE_PROF ;
SELECT username 사용자명, profile 적용프로파일
FROM dba_users
WHERE username = 'SCOTT' ;
여러 개의 프로파일을 적용시킬 수 없으니 처음부터 프로파일을 만들 때 원하는 파라미터를 전부 넣고 한꺼번에 만든 후 적용시켜야 한다.
(4) 사용 안하는 PROFILE 삭제하기
DROP PROFILE re_sample_prof ;
현재 사용자에게 할당되어 있는 PROFILE 은 기본적으로 삭제가 안된다.
그러나 CASCADE 옵션으로 삭제할 수 있다.
삭제하면 DEFAULT PROFILE 을 사용하게 된다.
DROP PROFILE re_sample_prof CASCADE ;
SELECT username 사용자명, profile 적용프로파일
FROM dba_users
WHERE username = 'SCOTT' ;
3. PRIVILEGE (권한) 관리하기
SYSTEM 관련 PRIVILEGE
OBJECT 관련 PRIVILEGE
(1) SYSTEM 관련 주요 PRIVILEGE
INDEX CREATE ANY INDEX 소유자에 상관없이 모든 테이블에 인덱스를 생성할 수 있는 권한
DROP ANY INDEX 소유자에 상관없이 모든 인덱스를 삭제할 수 있는 권한
ALTER ANY INDEX 소유자에 상관없이 모든 인덱스를 수정할 수 있는 권한
TABLE CREATE TABLE 자신 소유의 테이블을 생성할 수 있는 권한
CREATE ANY TABLE 소유자에 상관없이 다른 USER 이름으로 테이블을 생성할 수 있는 권한
ALTER ANY TABLE 소유자에 상관없이 모든 테이블의 구조를 수정할 수 있는 권한
DROP ANY TABLE 소유자에 상관없이 모든 사용자의 테이블을 삭제할 수 있는 권한
UPDATE ANY TABLE 소유자에 상관없이 모든 사용자의 테이블을 업데이트 할 수 있는 권한
DELETE ANY TABLE 소유자에 상관없이 모든 사용자의 테이블의 데이터를 삭제할 수 있는 권한
INSERT ANY TABLE 소유자에 상관없이 모든 사용자의 테이블에 데이터를 삽입할 수 있는 권한
SESSION CREATE SESSION 서버에 접속할 수 있는 권한 (계정 생성 후 꼭 주어야 함)
ALTER SESSION 접속 상태에서 환경값을 변경할 수 있는 권한
RESTRICTED SESSION RESTRICTED 모드로 OPEN 된 DB 에 접속할 수 있는 권한
TABLESPACE CREATE TABLESPACE TABLESPACE 를 만들 수 있는 권한
ALTER TABLESPACE TABLESPACE 를 수정할 수 있는 권한
DROP TABLESPACE TABLESPACE 를 삭제할 수 있는 권한
UNLIMITED TABLESPACE TABLESPACE 사용 용량을 무제한으로 허용하는 권한,
즉, QUOTA 옵션 적용을 받지 않게 됨
(2) SYSOPER / SYSDBA 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
(3) SYSTEM 관련 권한 할당하기 / 해제하기
SCOTT 사용자에게 CREATE TABLE, CREATE SESSION 권한을 할당한다.
GRANT CREATE TABLE, CREATE SESSION TO SCOTT ;
SCOTT 사용자에게 관리자 권한의 CREATE TABLE, CREATE SESSION 권한을 할당한다. (타 유저에게 해당 권한을 부여할 수도, 회수할 수도 있다.)
GRANT CREATE TABLE, CREATE SESSION TO SCOTT WITH ADMIN OPTION ;
SCOTT 사용자에게서 CREATE TABLE 권한을 해제한다.
REVOKE CREATE TABLE FROM SCOTT ;
(4) 사용자가 가지고 있는 권한 조회하기
SELECT *
FROM DBA_SYS_PRIVS
WHERE grantee = 'SCOTT' ;
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT CREATE SYNONYM NO
SCOTT CREATE VIEW NO
SCOTT QUERY REWRITE NO
SCOTT CREATE MATERIALIZED VIEW NO
SCOTT UNLIMITED TABLESPACE NO
SCOTT CREATE PUBLIC SYNONYM NO
SCOTT CREATE SESSION YES
ADM 컬럼은 WITH ADMIN OPTION 여부를 나타낸다.
(5) OBJECT 관련 PRIVILEGE
주로 DML과 연관이 많다. 즉 OBJECT 를 SELECT, INSERT, UPDATE, DELETE 할 수 있는 권한을 말한다.
(6) OBJECT 권한 할당하기 / 해제하기
사용 예1. SCOTT 사용자에게 HR 사용자가 만든 EMPLOYEES 테이블을 SELECT 할 수 있도록 권한을 할당하라.
GRANT SELECT ON HR.EMPLOYEES TO SCOTT ;
사용 예2. SCOTT 사용자에게 HR 사용자가 만든 EMPLOYEES 테이블을 UPDATE 할 수 있도록 권한을 할당하라.
SCOTT 사용자가 이 권한을 다른 사람에게 줄 수 있는 권한도 줄 것.
GRANT UPDATE ON HR.EMPLOYEES TO SCOTT WITH GRANT OPTION ;
사용 예3. SCOTT 사용자가 가진 HR 사용자가 만든 EMPLOYEES 테이블을 SELECT 하는 권한을 회수하라.
REVOKE SELECT ON HR.EMPLOYEES TO SCOTT ;
WITH GRANT OPTION 이란
SYSTEM PRIVILEGE 에서는 WITH ADMIN OPTION 을 사용하였지만
OBJECT PRIVILEGE 에서는 WITH GRANTOPTION 을 사용한다.
WITH GRANT OPTION 은 관리자가 A 사용자에게 부여한 권한을 해제하면,
A 사용자가 타 사용자에게 부여한 권한도 동시에 해제된다.
4. ROLE 관리하기
권한의 그룹으로 ROLE 안에 여러 권한을 넣어두고 사용자에게 ROLE 하나를 주면 그 안의 모든 권한을 받게 된다.
(1) ROLE 생성하기
CREATE ROLE trole ;
(2) ROLE 에 CREATE SESSION, CREATE TABLE 권한 할당하기
GRANT CREATE SESSION, CREATE TABLE TO trole ;
(3) SCOTT 사용자에게 trole 할당하기
GRANT trole TO SCOTT ;
(4) 어떤 사용자가 어떤 ROLE을 사용하는지 확인하기
SELECT *
FROM dba_role_privs
WHERE grantee = 'SCOTT' ;
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SCOTT RESOURCE NO YES
SCOTT TROLE NO YES
SCOTT CONNECT NO YES
(5) 어떤 ROLE에 어떤 권한이 있는지 확인하기
SELECT *
FROM dba_sys_privs
WHERE grantee = 'CONNECT' ;
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
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
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저