오라클 Admin (Constraint, Privilege, User)

1. CONSTRAINT 상태 4가지 가. DISABLE NOVALIDATE : 제약조건의 완전 비활성화, DISABLE의 기본값 나. DISABLE VALIDATE : 해당 테이블을 읽기 전용상태로 변경 다. ENABLE NOVALIDATE : 새로 입력되는 데이터에만 제약조건 활성화 라. ENABLE VALIDATE : DATAFILE에 저장되어 있는 데이터와 새로 입력되는 데이터 모두 제약조건 활성화, ENABLE의 기본값

참고 : 데이터 INSERT 후에 제약조건 활성화 팁

--예외테이블 생성
@?/rdbms/admin/utlexcpt.SQL

-- 데이터 insert 후에 제약조건 활성화
INSERT INTO scott.emp VALUES (.........);
ALTER TABLE scott.emp 
ENABLE VALIDATE CONSTRAINT deptno_fk 
EXCEPTIONS INTO sys.exceptions;

-- 위 제약조건 활성화 명령이 오류가 발생할 경우 아래 명령어를 통해 오류 발생 row 확인 및 처리
SELECT rowind, no FROM scott.emp WHERE ROWID IN (SELECT row_id FROM exceptions);
UPDATE scott.emp SET .......

2. 테이블 생성 시 제약조건 생성

CREATE TABLE hr.employee (
id NUMBER(7)
    CONSTRAINT employee_id_pk PRIMARY KEY
    DEFERRABLE
    USING INDEX TABLESPACE indx,
last_name VARCHAR2(25)
    CONSTRAINT employee_last_name_nn NOT null,
dept_id NUMBER(7))
TABLESPACE users;

참고 : 가. DEFERRABLE : 입력데이터를 모아서 제약조건 검사를 실시한다. ↔ IMMEDIATE : 입력이 발생할 때마다 제약조건 검사를 실시, 기본값 나. 테이블의 테이블스페이스와 인덱스의 테이블스페이스의 분리 – hr.employee의 테이블스페이스 : users – employee_id_pk의 테이블스페이스 : indx

3. 제약조건의 활성화 가. ENABLE NOVALIDATE : 테이블에 Lock이 걸리지 않는다.

ALTER TABLE hr.departments
ENABLE novalidate CONSTRAINT dept_pk;

나. ENABLE VALIDATE : 테이블에 Lock이 걸려 제약조건이 활성화가 되기 전까지 읽기전용상태가 된다.

ALTER TABLE hr.employees
ENABLE VALIDATE CONSTRAINT emp_dept_fk;

4. 제약 조건의 확인 가. USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건 조회 나. USER_CONSTRAINTS　: 유저가 소유한 모든 제약 조건 조회

SELECT SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,
            DECODE(B.CONSTRAINT_TYPE,
                'P','PRIMARY KEY',
                'U','UNIQUE KEY',
                'C','CHECK OR NOT NULL',
                'R','FOREIGN KEY') CONSTRAINT_TYPE,
            A.CONSTRAINT_NAME
     FROM　　USER_CONS_COLUMNS A, USER_CONSTRAINTS B
     WHERE A.TABLE_NAME = UPPER('&table_name')
       AND A.TABLE_NAME = B.TABLE_NAME
       AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
     ORDER BY 1;

5. PROFILE 가. 프로파일이란? : 사용자가 사용가능한 시스템 자원의 한계(RESOURCE profile) 및 암호에 대한 복잡성(PASSWORD profile)을 관리하도록 하기 위한 제한(limit) 집합의 이름 나. 특징 – 프로파일을 생성하여 사용자에게 할당함으로써 사용자의 자원사용 한계설정, 패스워드 관리를 쉽게 해줌 – 활성화 또는 비활성화 가능

-- 사용자별 profile 정보 조회
SELECT username, PROFILE FROM dba_users;

--  resource profile의 파라미터값을 적용하기 위해 설정
ALTER SYSTEM SET resource_limit = TRUE

-- 프로파일 생성
CREATE PROFILE test_profile LIMIT
    FAILED_LOGIN_ATTEMPTS 3        -- 로그인 시도간으 횟수, 지정수치 이후에는 계정이 잠김
    PASSWORD_LOCK_TIME UNLIMITED    -- 로그인 실패시 계정잠그는 기간(일)
    PASSWORD_LIFE_TIME 30        -- 암호를 변경하지 않고 사용할 수 있는 기간(일)
    PASSWORD_REUSE_TIME 30        -- 암호 변경 시 동일 암호를 사용할 수 없는 기간(일)
    PASSWORD_REUSE_MAX 2        -- 동일 암호를 사용가능한 최대 횟수
    PASSWORD_VERIFY_FUNCTION verify_function    -- 암호의 복잡성 만드는 함수 지정
    SESSIONS_PER_USER 2        -- 1명의 USER가 동시 접속할 수 있는 최대 세션수
    CPU_PER_SESSION 10000        -- 최대 사용가능한 CPU 가능 시간(1/100초)
    IDLE_TIME 60            -- 지정 이후 휴지세션 종료
    CONNECT_TIME 480;        -- 접속 가능한 최대시간

-- 생성한 프로파일 정보 확인
SELECT * FROM dba_profiles
WHERE profile='TEST_PROFILE'
ORDER BY resource_type;

-- 프로파일 생성(현재 사용중인 프로파일이 있다면 삭제불가)
DROP PROFILE test_profile;

-- /* 현재사용중인 프로파일이 있다면 사용자로부터 회수하고, 프로파일은 삭제
--     프로파일을 회수당한 사용자는 default profile을 적용받는다 */
DROP PROFILE test_profile cascade;

6. 사용자 생성 및 삭제

CREATE USER test
    IDENTIFIED BY test        -- 패스워드 지정
    DEFAULT TABLESPACE data        -- 기본 테이블스페이스 지정, 테이블 생성 시 별도의 명시가 없으면 
                                                         -- 테이블이 생성되는 위치
    TEMPORARY TABLESPACE temp    -- 기본 임시 테이블스페이스 지정
    QUOTA 10M ON data        -- data 및 users 테이블스페이스에 대한 사용자 할당량
    QUOTA 15M ON users        -- QUOTA UNLIMITED 옵션을 줄 경우 무제한 사용가능, 
                                                 -- QUOTA 0M 의 경우 해당 테이블스페이스에는 테이블 생성 불가능. 
                                                 -- 주로 SYSTEM 테이블 스페이스에 지정
    PASSWORD EXPIRE;            -- 패스워드 사용기간을 만료로 지정(다음번 로그인 시 패스워드 변경)

DROP USER test;        -- 사용자 삭제

DROP USER test CASCADE;    -- 해당 사용자가 만든 모든 object(테이블, trigger, index 등)도 함께 삭제

참고 : DATABASE SCHEMA 특정 사용자가 생성한 모든 Object를 일컫음

7. Privilees(권한) 가. SYSTEM PRIVILEGES – 시스템 권한은 사용자가 데이터베이스에서 특정 작업을 할 수 있도록 하는 권한이다. – 권한 중 ANY 키워드는 모든 스키마에서 권한을 가진다는 것을 의미한다. – 대표적인 시스템 권한 CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX CREATE TABLE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE …. CREATE SESSION, ALTER SESSION, RESTRICTED SESSION CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE, UNLIMITED TABLESPACE(쿼터 할당량을 무시하는 권한)

GRANT CREATE SESSION TO bob;

GRANT CREATE SESSION TO bob WITH ADMIN OPTION;

REVOKE CREATE SESSION FROM bob;

나. OBJECT PRIVILEGES – 객체 권한은 User가 소유하고 있는 특정 객체(테이블, 뷰, 시퀀스, 프로시저 등)를 다른 사용자에게 액세스하거나 조작할 수 있도록 하는 권한이다 – 객체 소유자는 다른 사용자에게 특정 객체권한을 부여할 수 있다. – PUBLIC 권한으로 부여하면, PUBLIC 권한으로 회수해야 한다. – WITH GRANT OPTION은 ROLE 권한을 부여할 때는 사용할 수 없다.

GRANT SELECT, INSERT ON emp TO scott;

GRANT SELECT, INSERT ON emp TO scott WITH GRANT option;

REVOKE SELECT ON emp FROM scott;

참고 : WITH ADMIN OPTION vs WITH GRANT OPTION – WITH ADMIN OPTION : 해당 권한을 다른 사용자에게도 부여할 수 있는 권한, 이 권한을 부여한 관리자가 권한을 회수하더라도 다른 사용자에게 부여된 권한은 회수하지 않는다. 예) A가 B에게 권한을 부여하고, B가 다시 C에게 권한을 부여했을 때, A가 B로부터 권한을 회수한다고 하더라도 B가 C에게 부여한 권한은 회수되지 않는다 – WITH GRANT OPTION : 해당 권한을 다른 사용자에게도 부여할 수 있는 권한, 이 권한을 회수하게 되면 다른 사용자에게 부여된 권한도 연쇄적으로 회수된다. 예) A가 B에게 권한을 부여하고, B가 다시 C에게 권한을 부여했을 때, A가 B로부터 권한을 회수하게되면 B가 C에게 부여한 권한도 같이 회수된다.

객체에 따른 권한 목록 예

객체권한	테이블	뷰	시퀀스	프로시저
ALTER	○		○
DELETE	○	○
EXECUTE				○
INDEX	○
INSERT	○	○
SELECT	○	○	○

8. ROLES 가. 정의 : 롤(role)이란 사용자에게 허가할 수 있는 권한들의 집합을 말한다. 나. 특징 – ROLE를 이용하면 권한 부여와 회수가 용이해진다. – 한 사용자가 여러개의 ROLE를 가질 수 있고, 여러사용자에게 1개의 ROLE를 부여할 수 있다. 다. 미리 정의된 ROLE – CONNECT : 오라클에 접속 할 수 있는 세션 생성, 테이블 생성/조회 등 가장 일반적인 권한들로 이뤄진 롤 – RESOURCE : Sotre Procedure, TRIGGER 등과 같은 PL/SQL을 사용할 수 있는 권한들로 이뤄진 롤 – DBA : 모든 시스템 권한이 부여된 롤, DBA 이외의 사용자에게 부여해서는 안된다.

CREATE ROLE oe_clerk;                -- ROLE 만들기

GRANT CREATE session, CREATE TABLE TO oe_clerk;    -- ROLE에 권한 부여

GRANT oe_clerk TO scott, oe;            -- scott, oe 사용자에게 롤 부여

GRANT hr_clerk TO hr_manager;

GRANT hr_manager TO scott WITH ADMIN OPTION;

REVOKE oe_clerk FROM scott;

REVOKE hr_manager FROM public;

ALTER USER scott
    DEFAULT ROLE hr_clerk, oe_clerk;    -- 사용자에게 많은 롤이 존재할 때, 필요한 롤만 사용하도록 지정

ALTER USER scott DEFAULT ROLE ALL;        -- 모든 롤을 사용하도록 함

ALTER USER scott DEFAULT ROLE ALL EXCEPT hr_clerk;    -- hr_clerk 를 제외하고, 모든 롤 사용

ALTER USER scott DEFAULT ROLE NONE;        -- 모든 롤을 사용하지 않음

DROP ROLE hr_manager;                -- 롤 삭제

참고 : 부여된 권한 살펴보기

--롤에 부여된 권한 조회
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee='CONNECT';  -- CONNECT 대신에 조회하고자 하는 ROLE 입력

-- 사용자에게 부여된 롤 조회
SELECT * FROM dba_role_privs
WHERE grantee = 'SYS';