Home » SQL & PL/SQL » SQL & PL/SQL » [ ORA-01031: insufficient privileges ] error while creating trigger (10g)
[ ORA-01031: insufficient privileges ] error while creating trigger [message #315904] Wed, 23 April 2008 03:04 Go to next message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
hi;
while I'm trying to create the following triggers I get ORA-01031: insufficient privileges ,
however, DBA role is granted to SCOTT
{{ I'm trying to follow users login and logout}}
triggers
-------------------- trigger to trap logged in users
create or replace trigger login_tri after logon ON database
begin
insert into logins values(user,to_char(sysdate,'dd-mon-yyyy hh:mi am'));
end;
-------------------- trigger to trap logged out users
create or replace trigger logoffs_tri before logoff ON database
begin
insert into logoffs values(user,to_char(sysdate,'dd-mon-yyyy hh:mi am'));
end;
tables
------ log on 
create table logins
(username varchar2(20),
dt varchar2(20)
);
------- log off
create table logoffs
(username varchar2(20),
dt varchar2(20));
thanks
Re: [ ORA-01031: insufficient privileges ] error while creating trigger [message #315909 is a reply to message #315904] Wed, 23 April 2008 03:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Who owns those tables?
Re: [ ORA-01031: insufficient privileges ] error while creating trigger [message #315920 is a reply to message #315904] Wed, 23 April 2008 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a FAQ.

http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html


Regards
Michel
Re: [ ORA-01031: insufficient privileges ] error while creating trigger [message #315992 is a reply to message #315909] Wed, 23 April 2008 07:51 Go to previous messageGo to next message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
Frank wrote on Wed, 23 April 2008 03:19
Who owns those tables?

SCOTT
DBA role is granted to SCOTT

[Updated on: Wed, 23 April 2008 07:54]

Report message to a moderator

Re: [ ORA-01031: insufficient privileges ] error while creating trigger [message #315996 is a reply to message #315904] Wed, 23 April 2008 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something wrong in what you think you have:
SQL>  create user test identified by test quota unlimited on ts_d01;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL> connect test/test
Connected.
TEST> create table logins
  2  (username varchar2(20),
  3  dt varchar2(20)
  4  );

Table created.

TEST> create table logoffs
  2  (username varchar2(20),
  3  dt varchar2(20));

Table created.

TEST> create or replace trigger login_tri after logon ON database
  2  begin
  3  insert into logins values(user,to_char(sysdate,'dd-mon-yyyy hh:mi am'));
  4  end;
  5  /

Trigger created.

TEST> create or replace trigger logoffs_tri before logoff ON database
  2  begin
  3  insert into logoffs values(user,to_char(sysdate,'dd-mon-yyyy hh:mi am'));
  4  end;
  5  /

Trigger created.

Regards
Michel
Re: [ ORA-01031: insufficient privileges ] error while creating trigger [message #316363 is a reply to message #315996] Thu, 24 April 2008 16:08 Go to previous messageGo to next message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
thanks everybody
nothing changed or modified, but now it works properly,
I don't know why not , or how and why it work.
it keep its secret.
Re: [ ORA-01031: insufficient privileges ] error while creating trigger [message #316395 is a reply to message #316363] Thu, 24 April 2008 23:31 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, but something must have changed. A database does not prevent you doing things one day, claiming you lack privileges and allow you to do the same thing a few days later.
Previous Topic: Putting multiple processes on rownum selection
Next Topic: update table columns depending on other table column values
Goto Forum:
  


Current Time: Sat Dec 10 08:42:30 CST 2016

Total time taken to generate the page: 0.08175 seconds