Home » SQL & PL/SQL » SQL & PL/SQL » Grant - Table & Trigger. (Win XP, oracle 9i.)
Grant - Table & Trigger. [message #323326] Wed, 28 May 2008 03:58 Go to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi all,

I have created a table and trigger for the same.
<Grant all> permission is given to the table for user_schema.

Do we need to give grant permission for trigger seperately?

Example:

 create table sample_test(update_time date);
 /
 create trigger sample_trigger after insert on sample_test ...
 /
 Grant all on sample_test to user_schema;
 /


Is this enough? or should i need to grant permission to
user_schema seperately?

Thanks,
Thiyagarajan Palanisamy
Re: Grant - Table & Trigger. [message #323332 is a reply to message #323326] Wed, 28 May 2008 04:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm guessing you haven't tried.
Had you tried before posting you'd have noticed that what you want to di isn't actually possible:
SQL> create table trig_test (col_1 varchar2(10));

Table created.

SQL> 
SQL> create or replace trigger trig_test_trg before insert on trig_test for each row
  2  begin
  3    null;
  4  end;
  5  /

Trigger created.

SQL> 
SQL> grant execute on trig_test_trg to public;
grant execute on trig_test_trg to public
                 *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist
Re: Grant - Table & Trigger. [message #323334 is a reply to message #323326] Wed, 28 May 2008 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
NEVER use grant ALL.
Only grants the mandatory privileges to do the job.
Grant ALL includes the privilege to ALTER your table, do you want user_schema can drop any column of your table?

Regards
Michel
Re: Grant - Table & Trigger. [message #323368 is a reply to message #323334] Wed, 28 May 2008 05:43 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Yes. I havent tried it since testing is going on.

user_schema can able to change the structure, thats why i was told to give grant all.

Thanks for all your support.

thiyagarajan palanisamy
Re: Grant - Table & Trigger. [message #323377 is a reply to message #323368] Wed, 28 May 2008 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
user_schema can able to change the structure,

Only the owner should be able to do that.

Regards
Michel
Re: Grant - Table & Trigger. [message #323396 is a reply to message #323377] Wed, 28 May 2008 06:50 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi Micale,

Although after giving 'ALL' grant Permission For the table RAVI_TEST1 from itr_app Schema to Accountancy schema,i am not able to see the trigger on RAVI_TEST1 in Accountancy.

Here is wat i have done

create or replace trigger RAVI_TRIG before insert on ravi_test1
for each row
begin
DBMS_OUTPUT.PUT_LINE('hiiiiiii');
end;

In Accountancy schema

Insert into itr_app.ravi_test1 values ('GGGG',123,'K','');

I am able to Do Insert operation,..

My Question is when i give DESC RAVI_TEST1 in ACCOUNTANCY schema it will display as there are no triggers on it.
Re: Grant - Table & Trigger. [message #323398 is a reply to message #323396] Wed, 28 May 2008 06:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In SQL*Plus, DESC doesn't show triggers.
Never has.
Re: Grant - Table & Trigger. [message #323404 is a reply to message #323396] Wed, 28 May 2008 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Query ALL_TRIGGERS and you'll see that you can see it.

Regards
Michel
Re: Grant - Table & Trigger. [message #323413 is a reply to message #323396] Wed, 28 May 2008 07:54 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
panyam wrote on Wed, 28 May 2008 07:50

create or replace trigger RAVI_TRIG before insert on ravi_test1
for each row
begin
DBMS_OUTPUT.PUT_LINE('hiiiiiii');
end;



I hope this was just a "sample" JRowbottom's test, because this trigger will do nothing. You will not see any output.

[added]Just to be clear, I meant that any insert via an application will not see any output. You will only see output in a SQL*Plus session.

[Updated on: Wed, 28 May 2008 07:57]

Report message to a moderator

Previous Topic: Remove characters from field
Next Topic: is it possible to Retrieve All the rows also need some more functional columns
Goto Forum:
  


Current Time: Thu Feb 13 23:30:36 CST 2025