More clear now
Dusan, September 30, 2001 - 12:08 pm UTC
At least, it diverted me from wrong direction.
Reader
Reader, September 30, 2001 - 11:30 pm UTC
Tom,
Can you tell us what is the rational in
PL/SQL Procedures having Definer's rights as default and
Java Stored Procedures ( extrenal routines written in Java)
having Invoker's rights as the default
Thsnks
October 01, 2001 - 6:30 am UTC
When you load java into the database, the java itself is compiled with invokers rights. However -- since all java is called via a call spec written in SQL and this call spec will compile by default with DEFINER rights -- most people don't even notice this nuance (it appears to be definer rights).
The reasons why this is so -- there are a couple:
1) you typically use java in the database as a "utility" as a way to extend plsql to let it do what plsql cannot. By loading with IR (invoker rights) -- this is facilitated. Instead of loading the java per schema to get the rights privs -- we load it once and when you use it -- it'll take on "your" identity (you being another stored procedure). So, if both user A and user B create a call spec to the java AND call the java from their procedures, it will be as if they put the java into their schema.
2) the sql in java is typically in jdbc and is invisible to the compiler. Unlike plsql where the sql is right there and visible typically. When you compile java, we do not "rip the sql" out of it like with do with plsql. With plsql there are great efficiencies to be gained by using definer rights and doing the security checks and such right then and there at compile time. DR (definer rights) are very efficient with plsql -- they would add nothing to java.
So, in short -- since the call spec is a DR routine -- the fact that the java was loaded with IR isn't typically relevant at all (or even noticed).
But why triggers "Always" have definers rights...
Subhrajyoti Paul, September 13, 2002 - 1:45 am UTC
Tom, what is the philosphy behind triggers always having definers rights...?
September 14, 2002 - 1:35 pm UTC
Because they are considered part of the table itself.
Tell me -- can you give a logical, real world, practical case where it should run with invokers rights?
Thanks, Tom, excellent explanation, but....
Olga, May 13, 2003 - 5:01 pm UTC
... My goal is to track WHO updated certain column of some table and to store username in rec_upd_username column of the said table, for each row. I tried various solutions, including the one described in this topic, but came to the same conclusion that you just described.
I believe, there must be the way to do this in ORACLE, would you be so kind to give me a hint? :)
I run Oracle 8.1.7 database.
Thank you!
May 13, 2003 - 5:18 pm UTC
WHO = user?
create trigger before update on t for each row
begin
:new.updated_by := user;
end;
Oracle Triggers invoking a java class or servlet or component
Ram, January 14, 2004 - 9:08 am UTC
I am new to oracle. And want to know can Oracle triggers invoke a java class( can be a servler, class or component). If so can you help me in this. It is urgent
January 14, 2004 - 3:55 pm UTC
sure, the trigger can call any java STORED PROCEDURE, which could call a jsp or anything
would it be "slow"
would it be "wrong"
would it be "not a good idea"
would it be "totally non-transactional" (hence having the ability to destroy data integrity)
yes to all of the above.
Oracle Triggers invoking a java class or servlet or component
Ram, January 14, 2004 - 4:38 pm UTC
yes if it is a bad idea . It is Ok. My idea is to implement connection pooling between two machines. THe client is independent from which machine the response is coming. But in my case the session is dying. So I want to create a class and call that class in trigger , so that session can be maintained whatever the server may be. Is there any better idea to implement this rather than thinking it is bad idea.
I searched in google , found like we can use loadjava .....
. It is giving tablename space is not sufficient.
Is there any alternative.
January 14, 2004 - 5:56 pm UTC
er?
no idea where you are going with this? we sort of have connection pooling in the db already. guess I'd need "an example"
Triggers
Natasha, January 25, 2004 - 9:40 am UTC
Toms,
To trap the created date & the name of the user who logged in...Which practice is good. Please advice.
Table Available:
Create Table Xyz
( eno pls_integer,
ename Varchar2(20),
created_date date,
created_user varchar2(50),
modified_date date,
modified_user varchar2(50)
)
Ex #1:
To create a before insert trigger and we can track the created date & created user.
Ex #2:
Assinging a default values to Created_user & Created_Dates at the table level.
Please suggest for the developers.
Thanks in advance.
Natasha
January 25, 2004 - 10:12 am UTC
I would use defaults whenever possible (more efficient than a trigger) and a trigger only when I could not use something declaritive.
and when granting on this table, do not grant the ability to modify these columns as demonstrated (else someone can override the defaults if they wanted to spoof you)
ops$tkyte@ORA9IR2> create table t
2 ( id int primary key,
3 data varchar2(20),
4 create_date date default sysdate,
5 creator varchar2(30) default user,
6 update_date date,
7 updated_by varchar2(30)
8 )
9 /
Table created.
ops$tkyte@ORA9IR2> create or replace trigger t_trigger
2 before update on t for each row
3 begin
4 :new.update_date := sysdate;
5 :new.updated_by := user;
6 end;
7 /
Trigger created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> grant select, update (id,data), insert(id,data) on t to scott;
Grant succeeded.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect scott/tiger
ops$tkyte@ORA9IR2> set termout off
scott@ORA9IR2> REM GET afiedt.buf NOLIST
scott@ORA9IR2> set termout on
scott@ORA9IR2> insert into ops$tkyte.t ( id, data ) values ( 1, 'hello world' );
1 row created.
scott@ORA9IR2> insert into ops$tkyte.t ( id, data, create_date ) values ( 1, 'hello world', sysdate-1 );
insert into ops$tkyte.t ( id, data, create_date ) values ( 1, 'hello world', sysdate-1 )
*
ERROR at line 1:
ORA-01031: insufficient privileges
scott@ORA9IR2> update ops$tkyte.t set updated_by = 'OPS$TKYTE';
update ops$tkyte.t set updated_by = 'OPS$TKYTE'
*
ERROR at line 1:
ORA-01031: insufficient privileges
scott@ORA9IR2> update ops$tkyte.t set data = 'goodbye';
1 row updated.
scott@ORA9IR2> select * from ops$tkyte.t;
ID DATA CREATE_DA CREATOR UPDATE_DA UPDATED_BY
---------- -------------------- --------- ------------------------------ --------- ------------------------------
1 goodbye 25-JAN-04 SCOTT 25-JAN-04 SCOTT
definer's right
Mike, September 11, 2004 - 11:25 pm UTC
we have a trigger to prevent schema other than the two schemes (admuser and privuser) to DML on table admuser.taskrsrc. I wanted to test the definer's right.
As schema usace, i created a function to update admuser.taskrsrc:
create or replace function update_taskrsrc
(p_owner in varchar2,p_newcost in number, p_id in number, p_rowid out varchar2)
return number
is
begin
execute immediate
'update '||p_owner||'.taskrsrc
set target_cost =:bv1
where taskrsrc_id = :pk
returning rowid into :out'
using p_newcost, p_id
returning into p_rowid;
return sql%rowcount;
end;
/
as USACE, use the function to update record of ADMUSER.TASKRSRC, and failed on the trigger:
USACE@S0P2PVD5> set serveroutput on
USACE@S0P2PVD5> declare
2 l_rowid varchar(50);
3 l_rows number;
4 begin
5 l_rows := usace.update_taskrsrc( 'ADMUSER', 1,
6 6639, l_rowid );
7
8 dbms_output.put_line( 'Updated ' || l_rows || ' rows' );
9 dbms_output.put_line( 'its rowid was ' || l_rowid );
10 end;
11 /
declare
*
ERROR at line 1:
ORA-06550: line 1, column 22:
PLS-00201: identifier 'FND_GLOBAL.CONC_REQUEST_ID' must be declared
ORA-06550: line 1, column 11:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1118
ORA-06512: at "SYS.DBMS_SQL", line 316
ORA-06512: at "ADMUSER.OP3_UTIL", line 532
ORA-06512: at "ADMUSER.OP3_UTIL", line 560
ORA-06512: at "ADMUSER.OP3_UTIL", line 585
ORA-06512: at "ADMUSER.OP3_ACTUALS2", line 268
ORA-06512: at "ADMUSER.OP3_ACTUALS2_BEFORE_UPDATE", line 2
ORA-04088: error during execution of trigger 'ADMUSER.OP3_ACTUALS2_BEFORE_UPDATE'
ORA-06512: at "USACE.UPDATE_TASKRSRC", line 6
ORA-06512: at line 5
I granted the execution priveldge to admuser. it should fail also by admuser. But it does sucess.
admuser@s0p2pvd5> set serveroutput on
admuser@s0p2pvd5> declare
2 l_rowid varchar(50);
3 l_rows number;
4 begin
5 l_rows := usace.update_taskrsrc( 'ADMUSER', 1,
6 6639, l_rowid );
7
8 dbms_output.put_line( 'Updated ' || l_rows || ' rows' );
9 dbms_output.put_line( 'its rowid was ' || l_rowid );
10 end;
11 /
Updated 1 rows
its rowid was AAAF+iAAFAAACA1AAA
PL/SQL procedure successfully completed.
admuser@s0p2pvd5>
If i understood the definer's right correctly, admuser's execution should fail also, because usace's privilege had been used.
September 12, 2004 - 11:04 am UTC
<quote>
we have a trigger to prevent schema other than the two schemes (admuser and
privuser) to DML on table admuser.taskrsrc.
</quote>
you have a trigger?!? In my world I would use "grant".
but in any case, unless you can provide a
CONCISE (as in small)
yet 100% COMPLETE (as in anyone that wanted to run the test case could do so)
testcase -- nothing we can really say. I mean -- hey, the error is being thrown in code we don't even have a clue as to what it looks like.
Trigger and invoker...
Ajeet, October 28, 2004 - 1:08 pm UTC
Tom,
I have a trigger on a table say T1 in a schema 'FLT' at one oracle instance.This trigger insert a row into a table say T2 in a differnet schema 'RDO' which is at a different oracle instance.
There is a public db link between the 2 oracle instances.
Now when a user 'A' tries to insert a row in table T1 at
then this trigger fails to insert a row in table T2 (which is at remote instance or other instance)..
This user 'A' does not exist at other instance.
I know that the for a public db link to work we have to have user 'A' at both instances with same password.
But using trigger (which is owned by same user which owns the table T1) --is there a way to do this.
the error i get is obvious -- invalid user/password...which tell me the reason..but is there a way to solve this problem.
Please advice.
October 28, 2004 - 7:29 pm UTC
trigger would use a database created via:
create database link foo connect to A identified by A_PW using 'connect_string'
else, database link connects as the current user, with their privs.
invoker's right does not work in nested stored procedure
jianhui, May 11, 2005 - 10:07 am UTC
Tom,
I need to turn on sql trace in a stored procedure but the user does not have ALTER SESSION granted directly, although it has it granted indirectly through a role.
I tried to use invoker's right to work around but it seems not working in nested procedure calls, see below example. Could you explain why it does not work in nested call but it works in direct call? Does not procedure foo thinks the invoker is the current user? I can not put invoker right in procedure gee since it will be called by other users, which will break the application.
1 create or replace procedure foo
2 authid current_user
3 is
4 begin
5 dbms_session.set_sql_trace(true);
6* end;
F5DBOD@dor_f501>/
Procedure created.
SQL>create or replace procedure gee
SQL>is
SQL>begin
SQL> foo;
SQL>end;
SQL>/
Procedure created.
SQL>exec gee
BEGIN gee; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 126
ORA-06512: at "F5DBOD.FOO", line 5
ORA-06512: at "F5DBOD.GEE", line 4
ORA-06512: at line 1
SQL>exec foo
PL/SQL procedure successfully completed.
May 11, 2005 - 12:09 pm UTC
sure it is working - your issue is that the INVOKER of foo is in fact GEE and GEE without any roles.
(if you have expert one on one oracle handy, i cover this in large detail)
before you call gee - you have your privs and roles, when you call gee, you have gee's owners BASE privs. when gee calls foo -- foo is running as gee's owner with no roles.
java definer vs invoker rights
Clark Pearson, April 02, 2012 - 9:50 am UTC
Over ten years ago now (!) you stated that effectively all java calls run as AUTHID DEFINER. Is this still true, or is there now an option to make the java run as AUTHID CURRENT_USER?
I've tried putting AUTHID CURRENT_USER after CREATE OR REPLACE AND COMPILE JAVA... but seemingly to no avail.
Thanks,
Clark.
April 03, 2012 - 6:27 am UTC
I do not believe anything changed there, no.
difference among 'current_user', 'session_user', 'current_schema'
A reader, April 05, 2012 - 1:56 am UTC
Hi Tom,
I am testing to have a better undestanding about 'current_user', 'session_user' and 'current_schema', but seems they are always same.
Anything wrong with my testing?
My understanding per your reply here is:
current_user should always equal to current_schema(definer should refer to definer's schema, while invoker of course need to refer invoker's schema
but sessoin_user can be different from them
Basically what i did was create 2 users, use 1 user to create 2 procedures, 1 is deifiner and 1 is invoker.
When call those 2 procedures with another user, the output:
for definer procedure are all definer, should not 'session_user' be different from the other 2?
for invoker procedure are all invoker, this one is understandable
Below is my testing script
create user leo identified by leo;
create user stt identified by stt;
grant sysdba,dba to leo,stt;
conn leo/leo
create or replace procedure leop1
as
begin
for x in (select sys_context( 'userenv', 'current_user' ) current_user,
sys_context( 'userenv', 'session_user' ) session_user,
sys_context( 'userenv', 'current_schema' ) current_schema
from dual) loop
dbms_output.put_line('Current User: '||x.current_user);
dbms_output.put_line('Session User: '||x.current_user);
dbms_output.put_line('Schema User: '||x.current_user);
end loop;
end;
/
create or replace procedure leop2
authid current_user
as
begin
for x in (select sys_context( 'userenv', 'current_user' ) current_user,
sys_context( 'userenv', 'session_user' ) session_user,
sys_context( 'userenv', 'current_schema' ) current_schema
from dual) loop
dbms_output.put_line('Current User: '||x.current_user);
dbms_output.put_line('Session User: '||x.current_user);
dbms_output.put_line('Schema User: '||x.current_user);
end loop;
end;
/
create public synonym leop1 for leo.leop1;
create public synonym leop2 for leo.leop2;
conn stt/stt
show user
set serveroutput on
exec leop1;
--Current User: LEO
--Session User: LEO
--Schema User: LEO
exec leop2;
--Current User: STT
--Session User: STT
--Schema User: STT
April 06, 2012 - 10:02 am UTC
look at your dbms_outputs! you printed out the same variable three times.
couple of notes:
a) do not use public synonyms, avoid them at all times. I'll let you have at most ONE per application but that is about it. Do not use them in your demos/test cases/etc especially - be explicit
b) DO NOT EVER GRANT SYSDBA/DBA to a developer account, a test account, nothing. use the concept of LEAST PRIVS (you need an account with create session/create procedure and another with create session and execute on two procedures). this is very very very important. do not over privilege yourself ever.
c) use better names when possible :)
Here is a better example:
stt%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> drop user leo cascade;
User dropped.
ops$tkyte%ORA11GR2> drop user stt cascade;
User dropped.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create user leo identified by leo;
User created.
ops$tkyte%ORA11GR2> create user stt identified by stt;
User created.
ops$tkyte%ORA11GR2> grant create session, create procedure to leo;
Grant succeeded.
ops$tkyte%ORA11GR2> grant create session to stt;
Grant succeeded.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> conn leo/leo
Connected.
leo%ORA11GR2> create or replace procedure dr( dont_call in boolean default false )
2 as
3 begin
4 for x in (select sys_context( 'userenv', 'current_user' ) current_user,
5 sys_context( 'userenv', 'session_user' ) session_user,
6 sys_context( 'userenv', 'current_schema' ) current_schema
7 from dual)
8 loop
9 dbms_output.put_line('dr Current User: '||x.current_user);
10 dbms_output.put_line('dr Session User: '||x.session_user);
11 dbms_output.put_line('dr Schema User: '||x.current_schema);
12 end loop;
13 if (not dont_call)
14 then
15 execute immediate 'begin leo.ir( true ); end;';
16 end if;
17 end;
18 /
Procedure created.
leo%ORA11GR2> create or replace procedure ir( dont_call in boolean default false )
2 authid current_user
3 as
4 begin
5 for x in (select sys_context( 'userenv', 'current_user' ) current_user,
6 sys_context( 'userenv', 'session_user' ) session_user,
7 sys_context( 'userenv', 'current_schema' ) current_schema
8 from dual)
9 loop
10 dbms_output.put_line('ir Current User: '||x.current_user);
11 dbms_output.put_line('ir Session User: '||x.session_user);
12 dbms_output.put_line('ir Schema User: '||x.current_schema);
13 end loop;
14 if (not dont_call)
15 then
16 execute immediate 'begin leo.dr( true ); end;';
17 end if;
18 end;
19 /
Procedure created.
leo%ORA11GR2>
leo%ORA11GR2> grant execute on dr to stt;
Grant succeeded.
leo%ORA11GR2> grant execute on ir to stt;
Grant succeeded.
leo%ORA11GR2>
leo%ORA11GR2> conn stt/stt
Connected.
stt%ORA11GR2> show user
USER is "STT"
stt%ORA11GR2> alter session set current_schema=SCOTT;
Session altered.
stt%ORA11GR2> set serveroutput on
stt%ORA11GR2> exec leo.ir;
ir Current User: STT
ir Session User: STT
ir Schema User: SCOTT
dr Current User: LEO
dr Session User: STT
dr Schema User: LEO
PL/SQL procedure successfully completed.
stt%ORA11GR2> exec leo.dr;
dr Current User: LEO
dr Session User: STT
dr Schema User: LEO
ir Current User: LEO
ir Session User: STT
ir Schema User: LEO
PL/SQL procedure successfully completed.
invoker VS definer follow up
A reader, April 09, 2012 - 1:16 am UTC
Hi Tom,
Sorry for my previous bad test example. Thanks a ton for your clear explaination.
At first glance, i was wondering why you use "execute immediate 'begin leo.dr/ir(true);' end;", after testing i know your purpose
is to compile them successfully, otherwise both of them will result in INVALID. I have not met this kind of circle function call
before, so this is the only way to handle it?
Meanwhile, just learned from your example that a invoker will get lost if called from a definer right procedure/function.
Is not it a issue?
Let's see i have a schema(we call it parent schema) which define a 'logger' procedure which can be called by any other schema.
Its purpose is to keep log in 'app_log' table of this parent schema.
Besides this i also want to keep log in 'app_log' table of individual other schema who call 'logger' procedure.
So i create a 'child_logger' with invoker's right with parent schema, it will be called by 'logger' procedure.
Per my testing, result is not what i expected, all log flow into table of parent schema. (invoker right lost)
Any idea? I guess my example is not a good design.
But my point is inevitably 'invoker' and 'definer' procedures will call each other, which will be not our expectation.
drop user leo cascade;
drop user stt cascade;
create user leo identified by leo;
create user stt identified by stt;
grant create session, create procedure to leo;
grant create session to stt;
grant create table,resource to leo;
grant create table,resource to stt;
conn leo/leo
create table app_log(log_time date, log_msg varchar2(1000));
create or replace procedure child_logger
authid current_user
as
begin
insert into app_log(log_time,log_msg) values(sysdate,'This is log in individual schema as child');
end;
/
create or replace procedure logger as
begin
insert into app_log(log_time,log_msg) values(sysdate,'This is log in parent schema');
child_logger;
end;
/
grant execute on logger to stt;
grant execute on child_logger to stt;
conn stt/stt
create table app_log(log_time date, log_msg varchar2(1000));
exec leo.logger;
April 09, 2012 - 11:39 am UTC
so this is the only way to handle it?
No, we could have used a package with forward declares or by having the functions be exposed in the specification of the package.
Meanwhile, just learned from your example that a invoker will get lost if
called from a definer right procedure/function.
Is not it a issue?
it doesn't get lost - it is quite sensible the way it works.
If you invoke the invokers rights routine, it runs with your privs.
If someone else invokes the invokers rights routine, it runs with their privs.
If you run a definers rights routine, you "become" them from a security perspective. Anything that routine does - is done "as them". Hence, if a definers rights routine runs an invokers rights routine - the invokers rights routine has to run as the owner of the definers rights routine.
Think about the security black hole that would be opened up otherwise!
The invoker of an invoker rights routine is the schema that was "in charge" when the call was made.
Authid Current_User in Triggers
Guruswam D, August 17, 2012 - 11:47 am UTC
Tom,
With continuation on the current thread.
## Scott
create table uom_master
(
uom_code varchar2(240),
uom_code_desc varchar2(2400),
uom_client_id varchar2(3)
);
Insert into uom_master Values
('U001','Unit of Measurement 1','001');
Insert into uom_master Values
('U002','Unit of Measurement 1','001');
Insert into uom_master Values
('U001','Unit of Measurement 2','002');
Insert into uom_master Values
('U002','Unit of Measurement 2','002');
grant select, insert, update, delete, references, alter, index on uom_master to hr;
CREATE OR REPLACE TRIGGER RND_TRG_UOM_MASTER
BEFORE INSERT ON UOM_MASTER
FOR EACH ROW
DECLARE
M_SYS_CTX_VALUE VARCHAR2(240);
BEGIN
SELECT VALUE INTO M_SYS_CTX_VALUE
FROM SESSION_CONTEXT
WHERE NAMESPACE LIKE 'RND_CLIENT%';
:NEW.uom_CLIENT_ID := M_SYS_CTX_VALUE;
END;
## HR Schema
create or replace procedure p_set_clid_context_001(p_client_id in varchar2 default '001')
--authid current_user
is
begin
sys.dbms_session.set_context('RND_CLIENT_ID_001','CLIENT_ID_001',P_CLIENT_ID);
exception
when others then
dbms_output.put_line('code '|| sqlcode || 'message '|| sqlerrm);
dbms_output.put_line('Exception '||dbms_utility.format_error_backtrace);
end p_set_clid_context_001;
/
create or replace context RND_CLIENT_ID_001 using p_set_clid_context_001;
CREATE OR REPLACE TRIGGER hr.after_logon_trg
AFTER LOGON ON hr.SCHEMA
BEGIN
DBMS_APPLICATION_INFO.set_module(USER, ' Logon Context Initialized');
p_set_clid_context_001(); -- Default value will be Set
END;
create or replace view uom_master as
select * from
SCOTT.uom_master
where UOM_CLIENT_ID = sys_context('RND_CLIENT_ID_001','CLIENT_ID_001');
Insert into uom_master(UOM_CODE,UOM_CODE_DESC) Values
('U005','Unit of Measurement 2');
During this Insert the Trigger of the Table Fires and Fetches the Sys context Value from HR Schema. There is no Context defined in Scott Schema, the following select Statement
SELECT *
FROM
SESSION_CONTEXT
will return No Rows when run from Scott. But the context is picked from HR Schema where its defined. Though there is No Context defined in Scott Schema. The trigger fired for the view UOM_MASTER in HR is through the trigger RND_TRG_UOM_MASTER defined in Scott. Is the behaviour correct and kindly explain and clarify.
August 17, 2012 - 3:31 pm UTC
when others then
dbms_output.put_line('code '|| sqlcode || 'message '|| sqlerrm);
dbms_output.put_line('Exception '||dbms_utility.format_error_backtrace);
end p_set_clid_context_001;
/i hate your code
There is no Context defined in Scott Schema, ,there are no contexts defined in ANY schema outside of PUBLIC. they are all public.
the context is always going to be set in the session that invoked the set context call.
This is all very convoluted and confusing. I don't even know who I should be logging in as for the various steps.
you don't fetch a context value from a schema, that doens't make sense.
I don't really follow what you are trying to do here at all
DDL triggers
Rustam Kafarov, January 23, 2013 - 4:31 am UTC
Hi Tom,
You said that triggers always have definer rights because they are part of tables. OK. What about DDL triggers created on DATABASE? I want to handle every DDL statement in database (let's say for audit) and write it into table in current schema (definer of this trigger doesn't have those rights).
You can say that you can to create SCHEMA triggers on every schema to be able to do that. But I think that's a good real example when we need to execute trigger under invoker rights. Correct me if I'm wrong.
Thanks
January 30, 2013 - 12:30 pm UTC
why in the world would you want a bunch of audit trails????
No, I don't think this is a good idea for "invokers rights" triggers - I would never want
a) the audit trail for a schema to actually be in that schema (think about it, what can the owner of that audit trail do to the audit trail? everything). that doesn't even begin to make sense.
b) more than one audit trail for an event like this.
DDL triggers
Rustam Kafarov, February 04, 2013 - 2:26 pm UTC
I agree that in most cases you don't need to do that or you should not do that.
However I faced the situation when it might be done just not to create triggers on every schema. I found following solution which probably not ideal:
Create dbms_scheduler job as
dbms_scheduler.create_job(
job_name => <schema_name>||'.'||<job_name>,
job_type => 'STORED_PROCEDURE',
job_action => '<proc_name>',
enabled => FALSE,
auto_drop => FALSE
);
In database level trigger call
dbms_scheduler.run_job(
job_name => <schema_name>||'.'||<job_name>,
use_current_session => true
);
Owner of this trigger must have privilege "create any job" which is very powerfull.
Obviously if owner is very powerfull user it can make changes in any schema directly. So this approch suits only if we need to call some existing complex procedure created with current user right without possibility to change it.
February 06, 2013 - 7:50 am UTC
I don't follow this - what is the meaning of <schema_name> || '.' || <job_name> ??
what is the use case here, what were you trying to accomplish at a high level (what was the original requirement - forget for a moment the implementation you tried, what were you trying to accomplish...)
DDL triggers
Rustam Kafarov, February 06, 2013 - 10:18 am UTC
Let's say we have 2 schemas: schema_a and schema_b. Schema_a owns DDL trigger on some event and procedure proc_current with user rights. Trigger calls this procedure. When schema_b fires this trigger we need to execute proc_current with user schema_b rights.
The idea is when you create scheduler_job using schema name like: 'schema_b.test_job' it will be created in schema_b and executed with schema_b user rights.
February 06, 2013 - 2:08 pm UTC
but it won't, it doesn't work that way, ddl triggers have to run with definers rights.
what is in proc_current that needs to do this - please tell me what you are trying to accomplish NOT HOW you would like to accomplish it.
back up and explain the requirement, not the approach you would like to take (that cannot possibly work).