Home » SQL & PL/SQL » SQL & PL/SQL » grant inside trigger
grant inside trigger [message #204932] Wed, 22 November 2006 12:06 Go to next message
cducdu
Messages: 3
Registered: November 2006
Junior Member
Hello,
i have creating a trigger on create in a schema with the following code:
begin
execute immediate 'grant select on ora_dict_obj_name to user_report';
end;

any error occur during compilation, but when i create e new table i have: ORA-30511: invalid DDL operation in system triggers

Is it possible to grant inside a trigger ? if yes how could i do that, or what is the other way to do that ?
thx
Re: grant inside trigger [message #204982 is a reply to message #204932] Thu, 23 November 2006 00:03 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can check this....
DDL in Trigger

By
Vamsi
Re: grant inside trigger [message #204990 is a reply to message #204932] Thu, 23 November 2006 00:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
May I ask why you would want such a thing?
In an application the objects are fixed. You don't create objects on the fly.
So why can't you just manually grant select privileges on the objects to the desired user? (you could even generate the grant script)
Re: grant inside trigger [message #205026 is a reply to message #204932] Thu, 23 November 2006 01:45 Go to previous messageGo to next message
cducdu
Messages: 3
Registered: November 2006
Junior Member
kasina, thanks for your link, but any solution work. ;(

Frank, i work with a ERP, and use it for modify tables, in this case the ERP drop and recreate, and the grants disappear.

[Updated on: Thu, 23 November 2006 02:06]

Report message to a moderator

Re: grant inside trigger [message #205084 is a reply to message #205026] Thu, 23 November 2006 04:54 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You should not do DDL on runtime. Why would "the ERP" drop and recreate tables? Use GTT for that; after all, this isn't SQLServer/Sybase!
Previous Topic: How can I delay a dbms_job?
Next Topic: Identifying and extracting an old data over three years
Goto Forum:
  


Current Time: Wed Dec 07 06:51:02 CST 2016

Total time taken to generate the page: 0.05107 seconds