Skip to Main Content
  • Questions
  • Ensuring an Insert is made through a specific procedure

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: April 08, 2022 - 11:52 am UTC

Last updated: April 19, 2022 - 3:16 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hello!

Let's say we have a table T1 like:
version | col1
W       | value1
S       | value2


W version rows are inserted through our application (java).
S version rows are inserted through a PL/SQL procedure, PR1.

We need to ensure that S version rows cannot be created by anything else than the dedicated procedure, PR1.

Is there an easy way to check this?

We were thinking of a trigger BEFORE INSERT, but I don't know a way to check if the insert comes from the procedure.
Also, splitting W and S versions in two different tables is not an option, so i'm stuck here.

Your help would be much appreciated, and thanks for your time!
Regards
Will

and Connor said...

Search this site for UTL_CALL_STACK and/or DBMS_UTILITY.FORMAT_CALL_STACK.

Both return the current call stack, so you could interrogate that in a trigger to decide if you want the operation to proceed,

eg

create or replace
trigger CHECK_PROC 
before insert on MY_TABLE
for each row
begin
  if dbms_utility.format_call_stack not like '....' then
      raise_application_error(-20000,'No No No');
  end if;
end;


Obviously some care is needed to ensure that people don't try subvert this with their own custom named procedures etc

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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;
/

Connor McDonald
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
Connor McDonald
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>



Connor McDonald
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,
Connor McDonald
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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library