231221 Linux_ Definer' right 와 Invoker's right

■ PL/SQL 
Definer's right : 만든 사람 입장에서 프로그램 수행할 것인지, 해당 프로시저나 패키지가 소유자의 권한으로 실행
& 
Invoker's right : 호출자 입장에서 프로그램 수행할 것인지, 해당 프로시저나 패키지가 호출자의 권한으로 실행

# SYS SESSION
#) user 생성 
SQL> create user green identified by oracle;

SQL> grant connect, resource to green;

 

#) 테이블생성
SQL> drop table hr.emp purge; <- drop any table 권한이 존재해서 drop 가능하다.

#) table 생성

SQL> create table hr.emp

as select employee_id, last_name, salary

from hr.employees

where 1 = 2;
SQL> create table green.emp
as select employee_id, last_name, salary
from hr.employees
where 1 = 2;  

# HR SESSION
#1) Definer's right : green 계정에서의 hr.emp의 insert+commit
SQL> CREATE OR replace PROCEDURE insert_emp1 (
    p_id    IN  emp.employee_id%TYPE,
    p_name  IN  emp.last_name%TYPE,
    p_sal   IN  emp.salary%TYPE )
IS 
BEGIN
    INSERT INTO EMP(employee_id, last_name, salary)
    VALUES (p_id, p_name, p_sal);
    COMMIT;
END;
/

SQL> show error
No errors.

#2) Invoker's right : green 계정에서의 green.emp의 insert+commit
SQL> CREATE OR replace PROCEDURE insert_emp2 (
    p_id    IN  emp.employee_id%TYPE,
    p_name  IN  emp.last_name%TYPE,
    p_sal   IN  emp.salary%TYPE)
    AUTHID CURRENT_USER
IS
BEGIN
    INSERT INTO EMP(employee_id, last_name, salary)
    VALUES (p_id, p_name, p_sal);
    COMMIT;

# ex) 
#   INSERT INTO hr.emp(employee_id, last_name, salary)

                           : 테이블 앞의 계정명과 같이 저장할 때, 지정한 계정명인 hr 계정 확인된다.

                              : 즉, 지정해준 계정이 우선권이 생성된다.
#    VALUES (p_id, p_name, p_sal);
#    COMMIT;
END;

SQL> select * from user_source where name = 'INSERT_EMP1';

SQL> select * from user_source where name = 'INSERT_EMP2';

# 권한 부여

SQL> grant execute on insert_emp1 to green;
SQL> grant execute on insert_emp2 to green;

SQL> select * from user_tab_privs;

# GREEN SESSION 
SQL> select * from user_tab_privs;

SQL> desc hr.insert_emp1
SQL> desc hr.insert_emp2

# definer's right 개념으로 만든 프로그램은 만든 사람 입장에서 수행한다.
SQL> execute hr.insert_emp1(7777, 'test1', 5000)

# invoker's right 개념으로 만든 프로그램은 호출 하는 사람 입장에서 수행한다.
SQL> execute hr.insert_emp2(8888, 'test2', 6000)

SQL> select * from emp;

# HR SESSION
SQL> select * from emp;

 

□ role 활성화/비활성화
# SYS SESSION 

# SQL 에서 시간 변경 작업X

SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

# SQL 에서 나와서 OS 에서 변경 작업 진행

SQL> ! 
[oracle1@oracle ~]$ date

[oracle1@oracle ~]$ timedatectl set-timezone

# root 계정에서 변경 하지 않아 root 인증이 필요하다.

"Asia/Seoul"==== AUTHENTICATING FOR org.freedesktop.timedate1.set-timezone ===
Authentication is required to set the system timezone. : root 인증 
Authenticating as: root
Password: 1234(보이지 않으나 비번작성)
==== AUTHENTICATION COMPLETE ===

[oracle1@oracle ~]$ timedatectl   

# OS에서 다시 SQL 로 로그인해서 확인해보기

[oracle1@oracle ~]$ exit
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

# 바로 적용이 되지 않아 재접속 필요
SQL> exit 
[oracle1@oracle ~]$ sqlplus / as sysdba
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

tip) 현재 날짜 시간 정보 수정
OS 에서 한국시간대로 변경된 확인되지 않았을 경우 + 재접속을 했을 경우
[root@oracle ~]# rdate -s time.bora.net

# INSA SESSION 
SQL> conn insa/oracle
SQL> select * from session_roles;

# SYS SESSION 
# application role

#) (현장에서 많이 사용함) 동일 유저가 시간별로 role 을 별도로 설정할 때 사용
SQL> CREATE OR REPLACE PROCEDURE priv_mgr
AUTHID CURRENT_USER
IS 
BEGIN
    IF to_char(sysdate, 'hh24:mi') BETWEEN '15:10' AND '15:30' THEN

                                                    -  적용 시간 지정
        DBMS_SESSION.SET_ROLE('sec_app_role');

                                                    - 별도의 함수 필요
    END IF;
END;
/

# 프로시저를 통해 실행유무 설정
SQL> create role sec_app_role identified using priv_mgr; 

       - SYS SESSION 에서 함수('sec_app_role' ) 생성하는데, 'priv_mgr'의 PROCEDURE 에서 사용할 것.
SQL> grant select any dictionary to sec_app_role;

SQL> grant execute on priv_mgr to insa;

# INSA SESSION 
SQL> select * from session_roles;
SQL> execute sys.priv_mgr
SQL> select * from session_roles;
SQL> select * from sys.tab$;

# SYS SESSION
- 비활성화 하기 위함
SQL> CREATE OR REPLACE PROCEDURE priv_mgr
AUTHID CURRENT_USER
IS 
BEGIN
    IF to_char(sysdate, 'hh24:mi') BETWEEN '15:10' AND '15:20' THEN 

                                                  - 해당 시간이 지나고 다시 컴파일.
        DBMS_SESSION.SET_ROLE('sec_app_role');
    END IF;
END;
/

# INSA SESSION 
SQL> conn insa/oracle
SQL> select * from session_roles;
SQL> execute sys.priv_mgr;
SQL> select * from session_roles;
SQL> select * from sys.tab%;

# SYS SESSION
SQL> CREATE OR REPLACE PROCEDURE priv_mgr
    AUTHID current_user
IS
BEGIN
    IF to_char(sysdate, 'hh24:mi') BETWEEN '15:10' AND '15:20' THEN
        dbms_session.set_role('sec_app_role');
    ELSE
        dbms_session.set_role('NONE'); -- 이렇게 작성 가능하다 

         - 이렇게 작성하는 이유) 해당 시간이 지나면 비활성화 하기 위함
    END IF;
END;
/