066 - [Oracle PL/SQL] Design - Definer's right, Invoker's rights

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

 

 

 

 

 

• Definer's rights 무슨 의미인지 샘플 코드를 통해서 확인

# 테스트를 위한 준비과정

# hr 사용자로 디비 접속해서 테이블 생성.
create table hr_table
( id number,
  name varchar2(100)
);
/*
Table HR_TABLE created.
*/



create or replace procedure add_hr_table
( p_id number, p_name varchar2 )
is
begin
insert into hr_table values (p_id,p_name);
end;
/*
Procedure ADD_HR_TABLE compiled
*/


# 여기부터는 sys 사용자로 접속하여 실행

--now conn as sys as sysdba and create user called ford (orclpdb)
--alter session set container=orclpdb;
--create user ford identified by ford;
-- grant create session, connect to ford;
/*

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 8 23:30:09 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user
USER is "SYS"

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> alter session set container=orclpdb;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
ORCLPDB

SQL> create user ford identified by ford;
User created.

SQL> grant create session, connect to ford;
Grant succeeded.
*/


# 다시 hr 사용자로 접속한 환경에서 실행.
--user hr will give jake execute to add_hr_table
grant execute on add_hr_table to ford;
/*
Grant succeeded.
*/

 

 

 

ford 사용자에게는 hr.add_hr_table 프로시져 접근권한과 기본 권한 할당된 상태이다.

hr 사용자의 어떤 테이블에 대한 접근 권한도 할당되지 않은 상태에서 아래와 같은 코드를 실행해 보자

 

# 새로운 세션을 만들어서 ford 사용자로 접속해서 아래 코드 실행

--now connect as ford
--execute hr.add_hr_table(1,'khaled');
--he can execute even if he doesnt access to table hr_table
--hr.hr_table 에 대한 접근 권한이 없지만, 
--hr.add_hr_table 프로시져에 대한 권한이 있어서 오브젝트에 대한권한이 없어도 인서트 가능하다. 

/*
SQL> conn ford/ford@orclpdb;
Connected.

SQL> show user;
USER is "FORD"

SQL> show con_name;
CON_NAME
------------------------------
ORCLPDB

SQL> execute hr.add_hr_table(1,'khaled');
PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.


# 테이블 접근권한이 없다. 하지만 프로시져를 통해서 데이터 인서트가 가능하다.
SQL> select * from hr.hr_table;
select * from hr.hr_table
                 *
ERROR at line 1:
ORA-00942: table or view does not exist
*/

 

 

 

 

Invoker's rights 샘플코드는 아래와 같다.

간략히 설명하면 위에서 ford 유저가 테이블 접근권한이 없어도 프로시져를 호출가능했지만,

아래 샘플에서는 오류가 발생하는 것을 볼 수 있다.

 

# hr 사용자 세션에서 함수를 다시 정의한다.

--now user hr will create the proc again but adding authid current_user
# authid 를 정의하지 않으면 기본값으로 definer 로 처리된다.

create or replace procedure add_hr_table
( p_id number, p_name varchar2 ) authid current_user
is
begin
insert into hr.hr_table values (p_id,p_name);--테이블이 속한 스키마 이름 추가.
end;


--now connect as ford
--execute hr.add_hr_table(2,'khaled');
--now 'ford' can not insert, he should priv insert to hr_table

/*
SQL> show user;
USER is "FORD"
SQL> show con_name;

CON_NAME
------------------------------
ORCLPDB

SQL> execute hr.add_hr_table(2,'khaled');
BEGIN hr.add_hr_table(2,'khaled'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "HR.ADD_HR_TABLE", line 5
ORA-06512: at line 1
*/