Home » SQL & PL/SQL » SQL & PL/SQL » HOW TO GIVE GRANT TO A TRIGGER (ORACLE,Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production,XP)
icon3.gif  HOW TO GIVE GRANT TO A TRIGGER [message #428106] Tue, 27 October 2009 04:42 Go to next message
satyam.sumit
Messages: 7
Registered: October 2009
Location: INDIA
Junior Member
Hi

Please tell me the process for assigning grants to a trigger.

For the trigger i tried to give all grant but following error i got.

SQL> grant all on oradba.trg_proxy_job_tasks to role;
grant all on oradba.trg_proxy_job_tasks to role
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> grant execute on oradba.trg_proxy_job_tasks to role;
grant execute on oradba.trg_proxy_job_tasks to role
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

SQL> grant all on trg_proxy_job_tasks to role;
grant all on trg_proxy_job_tasks to role
*
ERROR at line 1:
ORA-01775: looping chain of synonyms
Re: HOW TO GIVE GRANT TO A TRIGGER [message #428108 is a reply to message #428106] Tue, 27 October 2009 04:45 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't give grants for triggers.
Triggers fire automatically whenever the trigger event is done on the table the triggers belong to.
Users only need grants on the table.
icon4.gif  Re: HOW TO GIVE GRANT TO A TRIGGER [message #428111 is a reply to message #428108] Tue, 27 October 2009 04:58 Go to previous messageGo to next message
satyam.sumit
Messages: 7
Registered: October 2009
Location: INDIA
Junior Member
Thanks for your quick reply.

If i am not giving any type of grant to trigger i am not able to view my trigger in other region like QA,UAT.
Re: HOW TO GIVE GRANT TO A TRIGGER [message #428112 is a reply to message #428111] Tue, 27 October 2009 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Region? Do you mean schema?
What query do you use to see triggers?
You can ALWAYS view your OWN trigger.

Regards
Michel
Re: HOW TO GIVE GRANT TO A TRIGGER [message #428115 is a reply to message #428111] Tue, 27 October 2009 05:14 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
If i am not giving any type of grant to trigger i am not able to view my trigger in other region like QA,UAT.


I do not understand what you mean, I am not able to view my trigger.

XE@SQL> create user test_one identified by test_one default tablespace users;

User created.

XE@SQL> grant connect,resource to test_one;

Grant succeeded.

XE@SQL> create user test_two identified by test_two default tablespace users;

User created.

XE@SQL> grant connect,resource to test_two;

Grant succeeded.

XE@SQL> conn test_one/test_one

Connected.

XE@SQL> sho user
USER is "TEST_ONE"

XE@SQL> create table t1(x int);

Table created.

XE@SQL> create trigger bef_inst_t1 before insert on t1
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.

XE@SQL> 

XE@SQL> grant select on t1 to test_two;

Grant succeeded.

XE@SQL>

XE@SQL> conn test_two/test_two

Connected.

XE@SQL> sho user
USER is "TEST_TWO"

XE@SQL> select trigger_body from all_triggers where table_name = 'T1'
  2  and owner = 'TEST_ONE';

TRIGGER_BODY
--------------------------------------------------------------------------------

begin
null;
end;


1 row selected.

XE@SQL> conn test_one/test_one

Connected.

XE@SQL> sho user
USER is "TEST_ONE"

XE@SQL> revoke select on t1 from test_two;

Revoke succeeded.

XE@SQL>

XE@SQL> conn test_two/test_two

Connected.

XE@SQL> sho user
USER is "TEST_TWO"

XE@SQL> select trigger_body from all_triggers where table_name = 'T1'
  2  and owner = 'TEST_ONE';

no rows selected

XE@SQL>


Re: HOW TO GIVE GRANT TO A TRIGGER [message #428122 is a reply to message #428112] Tue, 27 October 2009 05:37 Go to previous messageGo to next message
satyam.sumit
Messages: 7
Registered: October 2009
Location: INDIA
Junior Member
yes Region meant other Shcema

I am using SQL Developer Version 1.5.3

select trigger_body from all_triggers where table_name = 'ORADBA.proxy_job_tasks';
Re: HOW TO GIVE GRANT TO A TRIGGER [message #428123 is a reply to message #428115] Tue, 27 October 2009 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
NEVER grant connect or resource EVEN in an example as it is a BAD example.

Regards
Michel
Re: HOW TO GIVE GRANT TO A TRIGGER [message #428128 is a reply to message #428123] Tue, 27 October 2009 05:43 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Michel Cadot wrote on Tue, 27 October 2009 05:37
NEVER grant connect or resource EVEN in an example as it is a BAD example.

Regards
Michel


Sir,Would you please let me know why?
Re: HOW TO GIVE GRANT TO A TRIGGER [message #428132 is a reply to message #428106] Tue, 27 October 2009 05:51 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Because one should always give proper access privileges to the user and connect/resource has more privileges assigned.

Asktom
Re: HOW TO GIVE GRANT TO A TRIGGER [message #428165 is a reply to message #428122] Tue, 27 October 2009 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
satyam.sumit wrote on Tue, 27 October 2009 11:37
yes Region meant other Shcema

I am using SQL Developer Version 1.5.3

select trigger_body from all_triggers where table_name = 'ORADBA.proxy_job_tasks';

Have you really a table named "ORADBA.proxy_job_tasks"?

Regards
Michel

Re: HOW TO GIVE GRANT TO A TRIGGER [message #428190 is a reply to message #428122] Tue, 27 October 2009 08:02 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
satyam.sumit wrote on Tue, 27 October 2009 06:37

select trigger_body from all_triggers where table_name = 'ORADBA.proxy_job_tasks';


This can never bring back any rows. Table names are in upper case and if you were so misguided to actually force the table name into mixed case, then you must refer to it with double quotes.
Re: HOW TO GIVE GRANT TO A TRIGGER [message #428204 is a reply to message #428190] Tue, 27 October 2009 08:20 Go to previous message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
joy_division wrote on Tue, 27 October 2009 13:02
satyam.sumit wrote on Tue, 27 October 2009 06:37

select trigger_body from all_triggers where table_name = 'ORADBA.proxy_job_tasks';


This can never bring back any rows. Table names are in upper case and if you were so misguided to actually force the table name into mixed case, then you must refer to it with double quotes.


And of course the schema name would be found in the table_owner column not the table_name column.
Previous Topic: How to save cm² ?
Next Topic: PL/SQL Problem
Goto Forum:
  


Current Time: Sat Feb 15 12:18:37 CST 2025