8/17 데이터베이스 보안, 권한, 롤, WITH GRANT OPTION, REVOKE, 동의어, Trigger

-- PROCEDURE 생성 과제
-- 1. 파라메타 :  (p_empno, p_ename, p_job, p_mgr, p_sal, p_deptno )
-- 2. emp TBL에  Insert_emp Procedure 
-- 3. v_job =  'MANAGER' -> v_comm  := 1000;
--              아니면                    150; 
-- 4. Insert -> emp 
-- 5. 입사일은 현재일자

CREATE OR REPLACE PROCEDURE Insert_emp
    (p_empno IN emp.empno%TYPE, 
    p_ename IN emp.ename%TYPE,
    p_job IN emp.job%TYPE,
    p_mgr IN emp.mgr%TYPE,
    p_sal IN emp.sal%TYPE,
    p_deptno IN emp.deptno%TYPE
    )
IS
    v_job emp.job%TYPE;
    v_comm emp.comm%TYPE;
BEGIN
        IF v_job = 'MANAGER' THEN  v_comm := 1000;
        ELSE     v_comm := 150;
        END IF;
        INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
        VALUES (p_empno, p_ename, p_job, p_mgr, SYSDATE, p_sal, v_comm, p_deptno);
        COMMIT;
END;

실행

데이터가 들어감.

 

데이터베이스 보안 
1. 다중 사용자 환경(multi-user environment)
 1) 사용자는 자신이 생성한 객체에 대해 소유권을 가지고 데이터에 대한 조작이나 조회 가능
 2) 다른 사용자가 소유한 객체는 소유자로부터 접근 권한을 부여받지 않는 접근 불가
 3) 다중 사용자 환경에서는 데이터베이스 관리자의 암호를 철저하게 관리
2. 중앙 집중적인 데이터 관리
3. 시스템 보안
 1) 데이터베이스 관리자는 사용자 계정, 암호 관리, 사용자별 허용 가능한 디스크공간 할당
 2) 시스템 관리 차원에서 데이터베이스 자체에 대한 접근 권한을 관리
4. 데이터 보안
 1) 사용자별로 객체를 조작하기 위한 동작 관리
 2) 데이터베이스 객체에 대한 접근 권한을 관리

권한(Privilege) 부여
1. 정의 : 사용자가 데이터베이스 시스템을 관리하거나 객체를 이용할 수 있는 권리
2. 유형 : 
 1) 시스템 권한 : 시스템 차원의 자원 관리나 사용자 스키마 객체 관리 등과 같은 데이터베이스 관리 작업을 할 수 있는 권한
  (1) 데이터베이스 관리자가 가지는 시스템 권한
  CREATE USER : 사용자를 생성할 수 있는 권한
  DROP USER : 사용자를 삭제할 수 있는 권한
  DROP ANY TABLE : 임의의 테이블을 삭제할 수 있는 권한
  QUERY REWRITE : 함수 기반 인덱스를 생성하기 위한 권한
  (2) 일반사용자가 가지는 시스템 권한
  CREATE SESSION : DB에 접속할 수 있는 권한
  CREATE TABLE : 사용자 스키마에서 테이블을 생성할 수 있는 권한
  CREATE SEQUENCE : 사용자 스키마에서 시퀀스를 생성할 수 있는 권한
  CREATE VIEW : 사용자 스키마에서 뷰를 생성할 수 있는 권한
  CREATE PROCEDURE : 사용자 스키마에서 프로시저, 함수, 패키지를 생성할 수 있는 권한
 2) 객체 권한 : 테이블, 뷰, 시퀸스, 함수 등과 같은 객체를 조작할 수 있는 권한

 롤(role)
 개념 : 다수 사용자와 다양한 권한을 효과적으로 관리하기 위하여 서로 관련된 권한을 그룹화한 개념
      일반 사용자가 데이터베이스를 이용하기 위한 공통적인 권한(데이터베이스 접속권한, 테이블 생성, 수정, 삭제, 조회 권한, 뷰 생성 권한)을 그룹화
 1. CONNECT 롤
  1) 사용자가 데이터베이스에 접속하여 세션을 생성할 수 있는 권한
  2) 테이블 또는 뷰와 같은 객체를 생성할 수 있는 권한
 2. RESOURCE 롤
  1) 사용자에게 자신의 테이블, 시퀀스, 프로시져, 트리거 객체 생성 할 수 있는 권한
  2) 사용자 생성시 : CONNECT 롤과 RESOURCE 롤을 부여
 3.  DBA 롤
  1) 시스템 자원의 무제한적인 사용이나 시스템 관리에 필요한 모든 권한
  2) DBA 권한을 다른 사람에게 부여할 수 있음
  3) 모든 사용자 소유의 CONNECT, RESOURCE, DBA 권한을 포함한 모든 권한을 부여 및 철회

 

 

--1. Admin 사용자 생성 / 권한 부여
--[system에서 실행]
-- 1) usertest01 생성
CREATE USER usertest01 IDENTIFIED BY tiger;
-- 2) usertest02 생성
CREATE USER usertest02 IDENTIFIED BY tiger;
-- 3) usertest03 생성
CREATE USER usertest03 IDENTIFIED BY tiger;
-- 4) usertest04 생성
CREATE USER usertest04 IDENTIFIED BY tiger;

--2. session 권한 부여
GRANT CREATE SESSION TO usertest01;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO usertest02;
-- 현장에서 DBA가 개발자에게 권한 부여할 때는 이렇게 함
GRANT CONNECT, RESOURCE TO usertest03;
GRANT CONNECT, RESOURCE TO usertest04;

--3. sampleTBL 테이블 생성
--[usertest01에서 실행] insufficient privileges : 권한이 없다. 
CREATE TABLE sampleTBL(
     memo varchar2(50)
     );
-- [usertest02에서 실행] no privileges on tablespace 'SYSTEM' : 테이블 스페이스에 대한 권한이 없다
CREATE TABLE sampleTBL(
     memo varchar2(50)
     );
--[usertest03에서 실행] 실행 성공 
CREATE TABLE sampleTBL(
     memo varchar2(50)
     );

--4. TBL Read 권한 주고받기
--[scott에서 실행] 실행 성공
select * from scott.emp;
--[usertest04에서 실행] 권한 없으므로 볼 수 없다.
select * from scott.emp;
--[scott에서 실행] scott에 있는 student TBL를 Read하는 권한 usertest04에게 주기
GRANT SELECT ON scott.student TO usertest04;
--[usertest04에서 실행] read 권한을 부여받았기 때문에 볼 수 있다.
select * from scott.student;
--[usertest04에서 실행] insufficient privileges : update 권한은 부여받지 않았기 때문에 볼 수 없다.
UPDATE scott.student SET userid = 'kkk' WHERE studno = 10101;

--5. 권한 부여를 할 수 있는 권한을 부여함. : WITH GRANT OPTION
--[usertest04에서 실행] scott에 있는 student TBL에 Read하는 권한을 usertest04에서 03에게 주기
-- 권한 없어서 안됨.
GRANT SELECT ON scott.student TO usertest03;
--[scott에서 실행] scott이 user04에게 권한 부여를 할 수 있는 권한을 부여함
GRANT SELECT ON scott.emp TO usertest04 WITH GRANT OPTION;
--[usertest04에서 실행] 실행 성공
SELECT * FROM scott.emp;
--[usertest03에서 실행] 권한이 없다.
SELECT * FROM scott.emp;
--[usertest04에서 실행] user04가 03에게 scott에 있는 emp TBL Read 권한을 부여함.
GRANT SELECT ON scott.emp TO usertest03;
--[usertest03에서 실행] 다시 실행해보면 실행 성공
SELECT * FROM scott.emp;

--[usertest03에서 실행] user03가 02에게 scott에 있는 emp TBL Read 권한을 부여함.
GRANT SELECT ON scott.emp TO usertest02;
--[usertest02에서 실행] 권한이 없다. 왜냐면 user04가 03에게 read 권한 부여할 때 WITH GRANT OPTION은 안주었기 때문.
SELECT * FROM scott.emp;
--[usertest03에서 실행] user03가 02에게 scott에 있는 emp TBL Read 권한을 부여하면서 WITH GRANT OPTION를 주면
GRANT SELECT ON scott.emp TO usertest02 WITH GRANT OPTION;
--[usertest02에서 실행] usertest02도 권한을 받을 수 있다. 실행 성공
SELECT * FROM scott.emp;

--6. 권한 회수
--[scott에서 실행]
GRANT SELECT ON scott.dept TO usertest04 WITH GRANT OPTION;
--[usertest04에서 실행] : 실행 성공
SELECT * FROM scott.dept;
GRANT SELECT ON scott.dept TO usertest03 WITH GRANT OPTION;
--[usertest03에서 실행] : 실행 성공
SELECT * FROM scott.dept;
GRANT SELECT ON scott.dept TO usertest02;
--[usertest02에서 실행]
SELECT * FROM scott.dept; -- 실행 성공
GRANT SELECT ON scott.dept TO usertest01; -- 실패

--[scott에서 실행] usertest02로부터 권한 회수
-- 권한 회수할 수 없다고 뜸. 왜냐면 user02는 03, 03은 04로부터 권한을 받은 것이기 때문.
-- 원래 권한 준 계정 아니면 권한 회수 안됨
REVOKE SELECT ON scott.emp FROM usertest02;
--[scott에서 실행] 원래 권한 준 계정으로부터 권한 회수하면 관련 권한도 모두 회수됨.
--그래서 usertest04로부터 권한 회수하기
REVOKE SELECT ON scott.emp FROM usertest04;
--[usertest02, 03, 04에서 실행] : 실행 실패. 권한 없어진 것 확인.
SELECT * FROM scott.dept;

 

--동의어(synonym)
--1. 정의 : 하나의 객체에 대해 다른 이름을 정의하는 방법
-- 1) 동의어와 별명(Alias) 차이점
--  동의어는 데이터베이스 전체에서 사용
--  별명은 해당 SQL 명령문에서만 사용

--2. 동의어의 종류
-- 1) 전용 동의어(private synonym) 
--  객체에 대한 접근 권한을 부여 받은 사용자가 정의한 동의어로 해당 사용자만 사용
-- 2) 공용 동의어(public sysnonym)
--  권한을 주는 사용자가 정의한 동의어로 누구나 사용
--  DBA 권한을 가진 사용자만 생성 (예 : 데이터 딕셔너리)

-- 공용 동의어 Test
--[usertest03에서 실행]
CREATE TABLE sampleTBL(
memo VARCHAR2(50)
);
INSERT INTO sampleTBL VALUES('오월 푸름');
INSERT INTO sampleTBL VALUES('결실을 맺으리라');
--[system에서 실행]
CREATE TABLE systemTBL(
memo VARCHAR2(50)
);
INSERT INTO systemTBL VALUES('오월 푸름');
INSERT INTO systemTBL VALUES('결실을 맺으리라');
--[system에서 실행] 나는 스스로 조회 가능
SELECT * FROM systemTBL;
--system에 있는 student TBL Read 권한을 usertest04에게 줌.
GRANT SELECT ON systemTBL TO usertest04;
--[usertest04에서 실행] system으로부터 Read권한 부여받으면 조회 가능.
SELECT * FROM system.systemTBL;

--위의 권한받는 과정이 번거로움. 이것을 간단히 하는 것이 공용 동의어.
--[system에서 실행] systemTBL를 pub_system로 SYNONYM(동의어)를 생성한다.
CREATE PUBLIC SYNONYM pub_system FOR systemTBL; 
--[usertest04에서 실행] 동의어 사용
SELECT * FROM pub_system;
-- 실무에서는 이렇게 씀.(위와 똑같음)
CREATE PUBLIC SYNONYM systemTBL FOR systemTBL; 
--[usertest04에서 실행] 
SELECT * FROM systemTBL;
-- 이렇게 system 테이블인 줄 모르고 마치 나의 테이블처럼 사용하는 것이 공용 동의어.

-- sampleTBL 이용한 전용 동의어 생성
--[system에서 실행]
CREATE TABLE sampleTBL(
memo VARCHAR2(50)
);
INSERT INTO sampleTBL VALUES('오월 푸름');
INSERT INTO sampleTBL VALUES('결실을 맺으리라');
--[system에서 실행] system 권한을 usertest04에게 준다.
GRANT DBA TO usertest04;
--[usertest04에서 실행] 이것이 조회 가능한 상태(권한이 있는 상태)임을 확인
select * from system.sampleTBL;
--[usertest04에서 실행] sampleTBL 이용한 priv_sampleTBL 전용 동의어 생성
--system에 있는 sampleTBL을 priv_sampleTBL라고 선언하겠다.
CREATE SYNONYM priv_sampleTBL FOR system.sampleTBL;
--그러면 이렇게 system.sampleTBL라고 하지 않고 priv_sampleTBL로 불러와도 조회가 된다. 
--하지만 usertest04에서만 사용 가능하다.
select * from priv_sampleTBL;

--결론
--공용 동의어는 권한을 가지고 있는 admin에서 만든다. 그러면 모든 계정에서 그 동의어를 사용할 수 있다.
--전용 동의어는 권한을 획득한 상태의 어떠한 계정에서 만든 것이다. 그러면 그 계정에서만 동의어를 사용할 수 있다.(다른 계정에서 사용할 수 없다.)
--(중요 : 동의어 질문 나올 수 있음. 개념 확실하게 하기)

이 체크박스를 확인하여 동의어가 들어가있는지 안들어가있는지 확인할 수 있다.

 

 Trigger
1. 정의
어떤 사건이 발생했을 때 내부적으로 실행되도록 데이터베이스에 저장된 프로시저
트리거가 실행되어야 할 이벤트 발생시 자동으로 실행되는 프로시저
트리거링 사건(Triggering Event), 즉 오라클 DML 문인 INSERT, DELETE, UPDATE이 실행되면 자동으로 실행
2. Oracle Trigger 사용 범위
1) 데이터베이스 테이블 생성하는 과정에서 참조 무결성과 데이터 무결성 등의 복잡한 제약 조건 생성하는 경우 
하지만 무결성용으로 사용하기에는 위험할 수 있다. DB를 껐다가 켰을 때 트리거가 비활성화되어 수동으로 활성화시켜줘야 하는 경우가 있기 때문.
2) 데이터베이스 테이블의 데이터에 생기는 작업의 감시, 보완 
3) 데이터베이스 테이블에 생기는 변화에 따라 필요한 다른 프로그램을 실행하는 경우 
4) 불필요한 트랜잭션을 금지하기 위해 
5) 컬럼의 값을 자동으로 생성되도록 하는 경우 

CREATE OR REPLACE TRIGGER trigger_test
BEFORE UPDATE ON dept --update가 일어나기 전에 실행
FOR EACH ROW    -- row 단위로 실행 (old, new 사용하기 위해)
BEGIN
    DBMS_OUTPUT.ENABLE;
    DBMS_OUTPUT.PUT_LINE('변경 전 칼럼 값 : ' || :old.dname);    --전 컬럼
    DBMS_OUTPUT.PUT_LINE('변경 후 칼럼 값 : ' || :new.dname);    --후 컬럼
END;

UPDATE dept
SET dname = '회계1팀'
WHERE deptno= 72;
COMMIT;

 

 

--과제
-- 조건 : emp Table의 급여가 변화시 emp Table 수정전
-- 화면에 출력하는 Trigger 작성(emp_sal_change)
-- 조건 : 입력시는 empno가 0보다 커야함
-- 출력결과 예시
--      이전급여  : 10000
--      신  급여  : 15000
--      급여 차액 :  5000
CREATE OR REPLACE TRIGGER emp_sal_change
BEFORE DELETE OR INSERT OR UPDATE ON emp    --조건
FOR EACH ROW
    WHEN (new.empno>0)
    DECLARE sal_diff number;
BEGIN
    sal_diff := :new.sal - :old.sal;
    DBMS_OUTPUT.PUT_LINE('이전 급여 : ' || :old.sal);
    DBMS_OUTPUT.PUT_LINE('신 급여 : ' || :new.sal);
    DBMS_OUTPUT.PUT_LINE('급여 차액1 : ' || sal_diff);
    DBMS_OUTPUT.PUT_LINE('급여 차액2: ' || (:new.sal - :old.sal));  
END;

UPDATE emp SET sal = 1000
WHERE empno = 7369;

출력 결과

 

*트리거 결과 확인 방법 : 보기 > DBMS 출력 > 실행하는 계정으로 설정

 

-- audit 감시 테이블 (log 테이블 제작)
-- EMP 테이블에 INSERT, UPDATE, DELETE 문장이 하루에 몇 건의 ROW가 발생되는지 조사
-- log 테이블은 trigger로 만드는 것을 추천한다. 하지만 log테이블을 너무 많이 만들면 성능이 떨어진다.
--  조사 내용은 EMP_ROW_AUDIT에 저장
--  ID, 사용자 이름, 작업 구분, 작업 일자시간을 저장하는 트리거를 작성
-- 1.  SEQUENCE 생성
-- DROP SEQUENCE emp_row_seq;
CREATE SEQUENCE emp_row_seq;

--2. Audit Table 생성
-- DROP TABLE emp_row_audit;
CREATE TABLE emp_row_audit(
    e_id  NUMBER(6) CONSTRAINT emp_row_pk PRIMARY KEY,
    e_name VARCHAR2(30),
    e_gubun VARCHAR2(10),
    e_date DATE
);

--3. Trigger 생성
CREATE OR REPLACE TRIGGER emp_row_aud
    -- EMP테이블에 대해 insert, update, delete 작업이 일어나면 그 후에(after)
    AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW  -- 각각의 row에 대해 작업한다.
    BEGIN
        IF INSERTING THEN --만약 insert를 하면
            INSERT INTO emp_row_audit
                VALUES(emp_row_seq.NEXTVAL,:new.ename, 'inserting', SYSDATE);
        ELSIF UPDATING THEN --만약 update를 하면
            INSERT INTO emp_row_audit
                VALUES(emp_row_seq.NEXTVAL,:new.ename, 'updating', SYSDATE);
        ELSIF DELETING THEN --만약 delete를 하면
            INSERT INTO emp_row_audit
                VALUES(emp_row_seq.NEXTVAL,:old.ename, 'deleting', SYSDATE);
        END IF;
END;

INSERT INTO emp(empno, ename, sal, deptno)
    VALUES(3000, '권동주', 3500, 50);
INSERT INTO emp(empno, ename, sal, deptno)
    VALUES(3100, '김건희', 3500, 50);
    
UPDATE emp SET ename = '건희' WHERE empno=3100;

DELETE emp WHERE empno = 9999;

emp_row_audit 테이블 결과