231102 PL/SQL PROCEDURE 생성 후 권한 부여 및 회수

<HR SESSION>;

- HR SESSION 에서 프로시저 생성 

CREATE OR REPLACE PROCEDURE hr.query_emp (
    p_id NUMBER ) 
IS
    v_rec hr.employees%ROWTYPE;
BEGIN
    SELECT * 
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = p_id;
    
    DBMS_OUTPUT.PUT_LINE(v_rec.last_name||' works on '||v_rec.job_id||', earns $'||v_rec.salary||' per month.');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당 사원이 없습니다');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END query_emp;
/

# 컴파일 완료 후 출력

execute hr.query_emp(100);

 

<DBA SESSION>;

# scott 유저 생성

CREATE USER scott IDENTIFIED BY oracle;

# 권한 부여 

GRANT CREATE SESSION TO scott;

# 권한 확인

SELECT * 
FROM dba_sys_privs
WHERE grantee = 'SCOTT';

 

<SCOTT SESSION>;

# 권한 확인

SELECT * 
FROM session_privs;

SELECT * 
FROM user_tab_privs;

<HR SESSION>;
# 프로시저 실행 권한

GRANT EXECUTE ON hr.query_emp TO scott;

# 프로시저에 대한 권한 확인

SELECT * 
FROM user_tab_privs
WHERE grantee = 'SCOTT';

<SCOTT SESSION>;

SELECT * 
FROM session_privs;

SELECT * 
FROM user_tab_privs;

execute HR.query_emp(100);

 

<HR SESSION>; 
# RETURN 문 : 프로시저 종료 

CREATE OR REPLACE PROCEDURE hr.query_emp (
    p_id NUMBER ) 
IS
    v_rec hr.employees%ROWTYPE;
BEGIN
    IF p_id IN (100, 101) THEN
        -- TRUE 
        RETURN; -- 종료 
    ELSE 
        SELECT * 
        INTO v_rec
        FROM hr.employees
        WHERE employee_id = p_id;
    
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name||' works on '||v_rec.job_id||', earns $'||v_rec.salary||' per month.');
    
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당 사원이 없습니다');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END query_emp;
/

# 호출

execute hr.query_emp(100);

 

<SCOTT SESSION>;

# 변경된 프로시저 호출

execute hr.query_emp(100);

<HR SESSION>;
# DROP 프로시저를 했을 경우, 권한도 자동 회수.

DROP PROCEDURE hr.query_emp;

# 권한 확인

SELECT * FROM user_tab_privs;

# 권한 부여

GRANT EXECUTE ON hr.query_emp TO scott;

=> 'OR REPLACE' 불필요한 일을 줄여준다. 허나, 권한 회수는 별도로 해줘야 한다. 

# 권한 확인.

SELECT * FROM user_tab_privs WHERE grantee = 'SCOTT';