Example from adp-gmbh.ch
This is an example that I copied from my old web site
adp-gmbh.ch and updated on
2020-12-16 (added logging procedure).
First, two users are created, one named
PROC_OWNER and the other named
PROC_USER. They're given the required
privileges for this example:
connect rene/rene
create user proc_owner identified by pw default tablespace data quota unlimited on data;
create user proc_user identified by pw default tablespace data quota unlimited on data;
grant create session, create procedure, create table, create view to proc_owner;
grant create session, create procedure, create table, create view to proc_user ;
Now, user PROC_OWNER creates two tables, named T and U and fills them with This is pkg_owner's table.
PROC_USER is then allowed to read T, but not U.
connect proc_owner/pw;
create table t (txt varchar2(40));
insert into t values (q'!This is proc_owner's table!');
create table u (txt varchar2(40));
insert into u values (q'!This is proc_owner's table!');
grant select on t to proc_user;
-- no grant on u
PROC_OWNER creates a simple logging procedure:
create procedure log_msg(msg varchar2) as begin
dbms_output.put_line('PROC_OWNER: ' || msg);
end log_msg;
/
PROC_OWNER also creates the procedure READ_T_CURRENT_USER with AUTHID CURRENT_USER. This procedure simply selects the content of T and calls log_msg with the values found in T:
create procedure read_t_current_user
AUTHID CURRENT_USER
as begin
for rec in (select txt from t) loop
log_msg(rec.txt);
end loop;
end;
/
PROC_OWNER then creates another procedure, READ_U_DEFINER, that reads from table U. This procedure runs under AUTHID DEFINER:
create procedure read_u_definer
AUTHID DEFINER
as begin
for rec in (select txt from u) loop
log_msg(rec.txt);
end loop;
end;
/
User
PROC_USER is granted the
privilege to execute both procedures:
grant execute on read_t_current_user to proc_user;
grant execute on read_u_definer to proc_user;
Now, user PROC_USER comes into play. This user also creates two tables, again named T and U and fills them with this is proc_user's table:
PROC_USER executes the procedure READ_T_CURRENT_USER:
set serveroutput on
begin
proc_owner.read_t_current_user;
end;
/
The execution prints.
PROC_OWNER: This is proc_user's table
Note: this procedure evaluates table names in the context of the current user (i.e. PROC_USER) but procedures (such as LOG_MSG) in the context of the owner of the package.
PROC_USER also executes the procedure READ_U_DEFINER:
set serveroutput on
begin
proc_owner.read_t_current_user;
end;
/
This time, the output is:
PROC_OWNER: This is proc_owner's table
The following example tries to demonstrate that
execute immediate is executed in the context of the user that invokes a procedure that is defined with
authid current_user:
--
-- In what schema is an object created (execute immediate)
--
connect / as sysdba
create user usr_01 identified by usr_01;
create user usr_02 identified by usr_02;
grant create table, create session, create procedure to usr_01;
grant create table, create session to usr_02;
connect usr_01/usr_01
create package pkg
authid current_user
as
procedure do;
end pkg;
/
create package body pkg as
procedure do is begin
execute immediate 'create table tq84_tab (a number)';
end do;
end pkg;
/
grant execute on pkg to usr_02;
connect usr_02/usr_02
exec usr_01.pkg.do
connect / as sysdba
select owner from dba_tables where table_name = 'TQ84_TAB';
-- Table is created in USR_02' schema.
drop user usr_01 cascade;
drop user usr_02 cascade;
Another example
Another example that I should describe, eventually…
accept db prompt "TNS name: "
accept priv_user prompt "User with enough rights to create schema: "
accept priv_pw prompt "Password of that user: "
set verify off
connect &priv_user/&priv_pw@&db
create user data
identified by data$012
quota unlimited on users;
create user reader
identified by reader$012;
grant create table,
create session,
create public synonym,
create view to data;
grant create procedure,
create session to reader;
connect data/data$012@&db
create table some_data (
nm number,
tx varchar2(20)
);
insert into some_data values ( 1, 'one');
insert into some_data values ( 2, 'two');
insert into some_data values ( 3, 'three');
insert into some_data values ( 4, 'four');
insert into some_data values ( 5, 'five');
insert into some_data values ( 6, 'six');
insert into some_data values ( 7, 'seven');
insert into some_data values ( 8, 'eight');
insert into some_data values ( 9, 'nine');
create view some_data_view as
select * from data.some_data
where nm > 5;
grant select on some_data_view to reader with grant option;
create public synonym some_data for data.some_data_view;
connect reader/reader$012@&db
create package read as
procedure data;
end read;
/
create package read_dynamic
AUTHID CURRENT_USER
as
procedure data;
end read_dynamic;
/
create package body read as
procedure data
is begin
for r in (select * from some_data) loop
dbms_output.put_line(r.nm || ': ' || r.tx);
end loop;
end data;
end read;
/
create package body read_dynamic as
procedure data
is begin
for r in (select * from some_data) loop
dbms_output.put_line(r.nm || ': ' || r.tx);
end loop;
end data;
end read_dynamic;
/
grant execute on read to data;
grant execute on read_dynamic to data;
prompt Schema read.
prompt
prompt read data:
prompt
exec read.data
prompt
prompt read dynamic data:
prompt
exec read_dynamic.data
connect data/data$012@&db
prompt Schema Data.
prompt
prompt read data:
prompt
exec reader.read.data
prompt
prompt read dynamic data:
prompt
exec reader.read_dynamic.data
connect &priv_user/&priv_pw@&db
drop user reader cascade;
drop user data cascade;
drop public synonym some_data;