HOW TO GIVE GRANT TO A TRIGGER [message #428106] |
Tue, 27 October 2009 04:42  |
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 #428115 is a reply to message #428111] |
Tue, 27 October 2009 05:14   |
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 #428190 is a reply to message #428122] |
Tue, 27 October 2009 08:02   |
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  |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
joy_division wrote on Tue, 27 October 2009 13:02satyam.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.
|
|
|