Home » RDBMS Server » Server Administration » Grant Role/Grant Privilege
Grant Role/Grant Privilege [message #255470] Wed, 01 August 2007 00:38 Go to next message
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.

Re: Grant Role/Grant Privilege [message #255472 is a reply to message #255470] Wed, 01 August 2007 00:41 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Repeat after me, "privs acquired via ROLE do NOT apply with PL/SQL procedures (including triggers)!"
Re: Grant Role/Grant Privilege [message #255475 is a reply to message #255470] Wed, 01 August 2007 00:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Grant Role/Grant Privilege [message #255486 is a reply to message #255470] Wed, 01 August 2007 01:02 Go to previous messageGo to next message
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.
Re: Grant Role/Grant Privilege [message #255504 is a reply to message #255480] Wed, 01 August 2007 01:40 Go to previous messageGo to next message
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.
Re: Grant Role/Grant Privilege [message #255508 is a reply to message #255504] Wed, 01 August 2007 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Roles are enabled at session creation.
You can enable new roles with "set role" statement.

Regards
Michel
Re: Grant Role/Grant Privilege [message #255591 is a reply to message #255508] Wed, 01 August 2007 04:34 Go to previous message
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.
Previous Topic: installing 10g s/w
Next Topic: ORA-01990
Goto Forum:
  


Current Time: Thu Dec 08 06:16:19 CST 2016

Total time taken to generate the page: 0.05168 seconds