A reader, April 11, 2022 - 8:45 am UTC
How about this?
create table t_foo_tab (version varchar2(1), col1 varchar2(100));
CREATE OR REPLACE PACKAGE p_foo IS
g_im_inserting BOOLEAN;
PROCEDURE set_im_inserting_flag(pi_value IN BOOLEAN);
END;
/
CREATE OR REPLACE PACKAGE BODY p_foo IS
PROCEDURE set_im_inserting_flag(pi_value IN BOOLEAN) IS
BEGIN
g_im_inserting := pi_value;
END;
END;
/
CREATE OR REPLACE TRIGGER t_ff_bi
BEFORE INSERT ON t_foo_tab
FOR EACH ROW
BEGIN
IF :new.version = 'S'
AND NOT nvl(p_foo.g_im_inserting, FALSE) THEN
raise_application_error(-20001, 'Insert not allowed');
END IF;
END t_ff_bi;
/
CREATE OR REPLACE TRIGGER t_ff_ai
AFTER INSERT ON t_foo_tab
FOR EACH ROW
BEGIN
p_foo.set_im_inserting_flag(pi_value => FALSE);
END t_ff_ai;
/
BEGIN
p_foo.set_im_inserting_flag(pi_value => TRUE);
INSERT INTO t_foo_tab
(version
,col1)
VALUES
('S'
,'foo');
END;
/
April 12, 2022 - 4:34 am UTC
You need to move that package variable to the body, otherwise I'll just do:
exec p_foo.g_im_inserting := true;
and away I go
Thanks!
Will, April 11, 2022 - 2:45 pm UTC
Thanks a lot for your ideas, I will dig into it!
regards,
Will
April 12, 2022 - 4:35 am UTC
If you get stuck, get back to us
CBAC and Accessible by clause....
Rajeshwaran Jeyabal, April 13, 2022 - 5:13 am UTC
Team,
given this
....We need to ensure that S version rows cannot be created by anything else than the dedicated procedure, PR1....as problem statement, why not using CBAC and accessible by clause something like this to get done..?
demo@XEPDB1> create user a identified by a
2 default tablespace users
3 quota unlimited on users;
User created.
demo@XEPDB1>
demo@XEPDB1> create user b identified by b
2 default tablespace users
3 quota unlimited on users;
User created.
demo@XEPDB1> grant create session, create table, create procedure, create role to a ;
Grant succeeded.
demo@XEPDB1> grant create session to b ;
Grant succeeded.
demo@XEPDB1> conn a/a@pdb1
Connected.
a@XEPDB1> create table t (ver varchar2(1), col1 varchar2(10));
Table created.
a@XEPDB1> create or replace procedure p( p_param1 in varchar2 )
2 accessible by (procedure pr1)
3 authid current_user as
4 begin
5 insert into a.t(ver,col1) values( 'S',p_param1 );
6 end;
7 /
Procedure created.
a@XEPDB1> create or replace procedure pr1( p_param1 in varchar2 )
2 authid current_user as
3 begin
4 a.p( p_param1 );
5 end;
6 /
Procedure created.
a@XEPDB1> grant execute on pr1 to b;
Grant succeeded.
a@XEPDB1> grant read on t to b;
Grant succeeded.
a@XEPDB1> create role r1;
Role created.
a@XEPDB1> grant insert on a.t to r1;
Grant succeeded.
a@XEPDB1> grant r1 to procedure pr1;
Grant succeeded.
a@XEPDB1> conn b/b@pdb1
Connected.
b@XEPDB1> select * from a.t;
no rows selected
b@XEPDB1> exec a.p('value1');
BEGIN a.p('value1'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'A.P' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
b@XEPDB1> exec a.pr1('value1');
PL/SQL procedure successfully completed.
b@XEPDB1> select * from a.t;
V COL1
- ----------
S value1
b@XEPDB1> insert into a.t(ver,col1) values('A','Hello');
insert into a.t(ver,col1) values('A','Hello')
*
ERROR at line 1:
ORA-01031: insufficient privileges
b@XEPDB1> insert into a.t(ver,col1) values('S','Ver2');
insert into a.t(ver,col1) values('S','Ver2')
*
ERROR at line 1:
ORA-01031: insufficient privileges
b@XEPDB1>
April 19, 2022 - 3:16 am UTC
All good inputs, but if they are considering PL/SQL then the issue is moot anyway, because you can trivially encapsulate access either via CBAC or schema separation.
Another Idea
Tyler, April 17, 2022 - 8:46 pm UTC
My personal preference would probably be to use the procedure to set an application context, then in a before statement trigger on the table check if the context is set appropriately and raise an error if it is not.
Cheers,
April 19, 2022 - 3:04 am UTC
All good ideas.
If you use PLSQL to encapsulate your DML, and then only expose privs to the PLSQL, then all of these issues go away