Home » SQL & PL/SQL » SQL & PL/SQL » Granting privileges on trigger
Granting privileges on trigger [message #219351] Wed, 14 February 2007 02:14 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I have a two procedures which I cannot modify since they are system objects , these two procedures are created with invoker rights previlege

The scenario is like this i connect into apps schema and execute both the procedures.

connect apps/apps

exec proc_a
exec proc_b

Proc_b uses a trigger which is created on a table in a different schema so i get a error saying trigger does not exist in apps schema.

I have given all the privileges on the table to apps schema .

Is there any way to solve the problem?


Re: Granting privileges on trigger [message #219354 is a reply to message #219351] Wed, 14 February 2007 02:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
Proc_b uses a trigger

You cannot call triggers directly in your code, they only fire upon the triggering event.
Re: Granting privileges on trigger [message #219360 is a reply to message #219351] Wed, 14 February 2007 02:44 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
Think so my question was not clear, I call dbms_sql.parse procedure which takes the statement to be executed and the trigger name to be applied as arguments .
The call to the dbms_sql.parse procedure is inside proc_b

Re: Granting privileges on trigger [message #219363 is a reply to message #219360] Wed, 14 February 2007 02:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What if you show us the relevant snippet of your code?
Re: Granting privileges on trigger [message #219368 is a reply to message #219351] Wed, 14 February 2007 03:16 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
create or replace procedure cons_execute(stmt in long, trig in varchar2,
s_rowid in rowid, e_rowid in rowid,
rows out number)
authid current_user is
c integer;

begin
c := dbms_sql.open_cursor();
dbms_sql.parse(c, stmt, dbms_sql.native,
-- edition in which to fire - null is current edition
null,
-- which trigger
trig,
-- fire_apply_trigger
true);
dbms_sql.bind_variable(c, ':r1', s_rowid);
dbms_sql.bind_variable(c, ':r2', e_rowid);
rows := dbms_sql.execute(c);

commit;

dbms_sql.close_cursor(c);
end;
/


This is what I said as proc_b . Now when i execute this procedure from apps schema I get the following error.

ORA-04070: invalid trigger name
ORA-06512: at "SYS.DBMS_SQL", line 1035
ORA-06512: at "APPS.CONS_EXECUTE", line 8
ORA-06512: at line 42

The trigger and the table are present in applsys schema.
Now is there any way through which I can execute this procedure from apps schema itself by granting privileges from applsys schema to apps schema ?
Re: Granting privileges on trigger [message #219378 is a reply to message #219368] Wed, 14 February 2007 03:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What database version are you on? I cannot find any documentation on the arguments you provide for dbms_sql.parse..
Re: Granting privileges on trigger [message #219416 is a reply to message #219368] Wed, 14 February 2007 05:25 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
Its a wrapper over the dbms_sql.parse which also accepts trigger as parameter .

Is it possible to change the schema from within a procedure ?

I have created a new topic on the same. Pls help me
Re: Granting privileges on trigger [message #219418 is a reply to message #219416] Wed, 14 February 2007 05:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is this a wrapper that you've written yourselves?
If so, that'd be where I'd start looking for the problem.
Re: Granting privileges on trigger [message #219432 is a reply to message #219416] Wed, 14 February 2007 06:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
show us the code of this wrapper then please.
Re: Granting privileges on trigger [message #219444 is a reply to message #219351] Wed, 14 February 2007 08:04 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
Its related to the project that I am doing

Can anyone let me know if I can change the schema from within a procedure ?
Is that possible ?
Re: Granting privileges on trigger [message #219461 is a reply to message #219444] Wed, 14 February 2007 09:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can use ALTER SESSION SET current_schema=<schema name>. If you run a procedure that is defined as AUTHID Current_User, then this will cause unspecified names (ie Tablename as opposed to Schemaname.Tablename) to be resolved as though you were logged in as <schema name>
It won't change the privileges that you have to alter these table.


I suspect that there's something deeply unlovely and unmaintainable hidden in that wrapper of yours.
Lets have a think - you claim that it lets you execute triggers, which is (to the best of my knowledge) impossible.
You're not doing something daft like getting the trigger code from All_Triggers, and then executing that dynamically are you?
That really would be creating a world of pain for the next person responsible for this system.
Re: Granting privileges on trigger [message #219467 is a reply to message #219461] Wed, 14 February 2007 09:33 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since the error states
ORA-04070: invalid trigger name
ORA-06512: at "SYS.DBMS_SQL", line 1035
ORA-06512: at "APPS.CONS_EXECUTE", line 8
ORA-06512: at line 42
it even looks as though you created your wrapper in the SYS schema...
Talking about worlds of pain..
Previous Topic: execution of procedure in sql
Next Topic: Query help (selecting diff field from same row)
Goto Forum:
  


Current Time: Tue Dec 06 02:19:50 CST 2016

Total time taken to generate the page: 0.09242 seconds