OraFAQ Forum: Server Administration » Grant Role/Grant Privilege

Home » RDBMS Server » Server Administration » Grant Role/Grant Privilege

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Grant Role/Grant Privilege [message #255470]

Wed, 01 August 2007 00:38

dba_giri
Messages: 26
Registered: July 2007
Location: Hyderabad

Junior Member

Hi..,

While Creating the following trigger at "Scott" User I've got an error as follows:

create or replace trigger data_logon_trigger
after logon
ON DATABASE
begin
execute immediate
'alter session set nls_date_format = ''yyyymmdd'' ';
end;
/

ERROR at line 3:
ORA-01031: insufficient privileges

I've given the following Grants to Scott
Grant Create Trigger to Scott
Grant Alter Any Trigger to Scott

After Above also I've got the same error Later I've given the following:
Grant DBA to Scott
Still I've got the same error, later I've given the following Grant:
Grant Administer Database Trigger to Scott
Then I can able to create the trigger.

By Default "Administer Database Trigger" is a Privelege is part of DBA role
Even If I 've granted that role to Scott, then why it is not able to created that trigger ...........?
Can u please explain problem might be what..?

Thanks in advance..
Giri K.Y.

Report message to a moderator

Re: Grant Role/Grant Privilege [message #255472 is a reply to message #255470]

Wed, 01 August 2007 00:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Repeat after me, "privs acquired via ROLE do NOT apply with PL/SQL procedures (including triggers)!"

Report message to a moderator

Re: Grant Role/Grant Privilege [message #255475 is a reply to message #255470]

Wed, 01 August 2007 00:44

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Make him grant create any trigger and grant execute any trigger.

[Updated on: Wed, 01 August 2007 00:54]

Report message to a moderator

Re: Grant Role/Grant Privilege [message #255480 is a reply to message #255470]

Wed, 01 August 2007 00:49

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SYSTEM> grant dba to scott;

Grant succeeded.

SYSTEM> connect scott/tiger
Connected.
SCOTT> create or replace trigger data_logon_trigger 
  2  after logon 
  3  ON DATABASE 
  4  begin 
  5  execute immediate 
  6  'alter session set nls_date_format = ''yyyymmdd'' '; 
  7  end; 
  8  /

Trigger created.

Got no problem.
You surely missed something.

Regards
Michel

Report message to a moderator

Re: Grant Role/Grant Privilege [message #255486 is a reply to message #255470]

Wed, 01 August 2007 01:02

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

What is the proof that u execute this trigger by scott user?

output of "show user" before execute trigger.

Report message to a moderator

Re: Grant Role/Grant Privilege [message #255504 is a reply to message #255480]

Wed, 01 August 2007 01:40

dba_giri
Messages: 26
Registered: July 2007
Location: Hyderabad

Junior Member

Hi..,
what u said is correct,, but I've seen that problem in the following scenario..:
I've already connected with that Scott user,
at my SYSTEM> I 've provided that "Grant DBA to Scott", immediately I've tried to create that trigger in that scenario it is giving the insufficient privilege, but if I've disconnected that SCOTT user and connected in that case am able to create that trigger..
Any how am thankful to u..

With Best Regards
Giri K.Y.

Report message to a moderator

Re: Grant Role/Grant Privilege [message #255508 is a reply to message #255504]

Wed, 01 August 2007 01:45

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Roles are enabled at session creation.
You can enable new roles with "set role" statement.

Regards
Michel

Report message to a moderator

Re: Grant Role/Grant Privilege [message #255591 is a reply to message #255508]

Wed, 01 August 2007 04:34

dba_giri
Messages: 26
Registered: July 2007
Location: Hyderabad

Junior Member

Hi.., Michel,

Thanks for ur reply.. I've tried using this option also for another user.., it is working fine.

With Best Regards
Giri K.Y.

Report message to a moderator

Previous Topic:	installing 10g s/w
Next Topic:	ORA-01990

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 25 03:45:12 CST 2025