Home » SQL & PL/SQL » SQL & PL/SQL » triger
triger [message #284824] Sat, 01 December 2007 11:00 Go to next message
Upperm
Messages: 95
Registered: July 2003
Member
I use oracle 9i Entreprise Edition on win2k.
My goal is to lock the account SCOTT using a trigger.


HERE'S THE SCRIPT:

create or replace trigger trg_lock_scott
after logon
on schema
Begin
if ora_login_user='SCOTT' then
execute immediate 'alter user '||ora_login_user||' account lock';
end if ;
End ;
/


The deception is I found out that Scott can log in and log out with no probelm. I mean the account scott is still unlock even if the trigger is enabled.
Re: triger [message #284825 is a reply to message #284824] Sat, 01 December 2007 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you really want to do?
Why do you want to lock an account when connecting?
basically, you can't execute a DDL in a trigger.

Regards
Michel
Re: triger [message #284826 is a reply to message #284824] Sat, 01 December 2007 11:29 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
try
create or replace trigger trg_lock_scott
after logon 
on schema 
declare
stmnt varchar2(100):='alter user '||ora_login_user||' account lock'; 
Begin 
if ora_login_user='SCOTT' then 
execute immediate stmnt;
end if ;
End ;

and let us tell what happens

[Updated on: Sat, 01 December 2007 11:53]

Report message to a moderator

Re: triger [message #284828 is a reply to message #284826] Sat, 01 December 2007 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is the difference with the previous trigger?
Just use an intermediate variable?

Regards
Michel
Re: triger [message #284829 is a reply to message #284824] Sat, 01 December 2007 11:37 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
No difference as such..
But we do have ddl triggers and after logon triggers
Re: triger [message #284830 is a reply to message #284829] Sat, 01 December 2007 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

But we do have ddl triggers and after logon triggers

And?

Regards
Michel
Re: triger [message #284831 is a reply to message #284824] Sat, 01 December 2007 11:47 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
And what does it mean
Quote:

basically, you can't execute a DDL in a trigger.

Re: triger [message #284832 is a reply to message #284831] Sat, 01 December 2007 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Exactly what it is written, what don't you understand?

Regards
Michel
Re: triger [message #284833 is a reply to message #284824] Sat, 01 December 2007 12:04 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
SQL>conn / as sysdba


SQL>
create or replace trigger trg_lock_scott
after logon 
on database 
declare
stmnt varchar2(100):='alter user '||ora_login_user||' account lock'; 
Begin 
if ora_login_user='SCOTT' then 
execute immediate stmnt;
end if ;
End ;

/

Trigger created.

SQL> conn
Enter user-name: scott
Enter password: *****
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00940: invalid ALTER command
ORA-06512: at line 5

Is this what you mean Michel?

[Updated on: Sat, 01 December 2007 12:04]

Report message to a moderator

Re: triger [message #284834 is a reply to message #284832] Sat, 01 December 2007 12:07 Go to previous messageGo to next message
Upperm
Messages: 95
Registered: July 2003
Member
Thanks for all,
I tried what varu123 suggested, but still scott can log in and out freely with no problem !

And answering Michel:
I do the script I posted just to know more about handling triggers Smile

.. To be continuoud,
Re: triger [message #284835 is a reply to message #284833] Sat, 01 December 2007 12:13 Go to previous messageGo to next message
Upperm
Messages: 95
Registered: July 2003
Member

SQL>show user
USER est "SCOTT"
SQL>disc
DÚconnectÚ de Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL>conn scott/tiger
ERROR:
ORA-00604: Une erreur s'est produite au niveau SQL rÚcursif 1
ORA-30511: opÚration DDL non valide pour les dÚclencheurs systÞme
ORA-06512: Ó ligne 5


SQL>conn / as sysdba
ConnectÚ.
SQL>
SQL>select username,account_lock from dba_users where lower(username)='scott';
select username,account_lock from dba_users where lower(username)='scott'
*
ERREUR Ó la ligne 1 :
ORA-00904: "ACCOUNT_LOCK" : identificateur non valide


SQL>select username,account_status from dba_users where lower(username)='scott';

USERNAME ACCOUNT_STATUS
--------------- ------------------
SCOTT OPEN

SQL>conn scott/tiger
ERROR:
ORA-00604: Une erreur s'est produite au niveau SQL rÚcursif 1
ORA-30511: opÚration DDL non valide pour les dÚclencheurs systÞme
ORA-06512: Ó ligne 5


Avertissement : vous n'Ûtes plus connectÚ Ó ORACLE.
SQL>select username,account_status from dba_users where lower(username)='scott';
SP2-0640: Non connectÚ
SQL>conn / as sysdba
ConnectÚ.
SQL>select username,account_status from dba_users where lower(username)='scott';

USERNAME ACCOUNT_STATUS
--------------- ------------------
SCOTT OPEN
Re: triger [message #284836 is a reply to message #284824] Sat, 01 December 2007 12:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
30511, 00000, "invalid DDL operation in system triggers" 
// *Cause:  An attempt was made to perform an invalid DDL operation
//          in a system trigger. Most DDL operations currently are not 
//          supported in system triggers. The only currently supported DDL 
//          operations are table operations and ALTER?COMPILE operations. 
// *Action: Remove invalid DDL operations in system triggers.

Rhetorical question - Does user SCOTT have ALTER USER priv?

[Updated on: Sat, 01 December 2007 12:19] by Moderator

Report message to a moderator

Re: triger [message #284837 is a reply to message #284836] Sat, 01 December 2007 12:22 Go to previous messageGo to next message
Upperm
Messages: 95
Registered: July 2003
Member
I got it now Smile
Things are more clear than before .. sure Smile
Thanks for help folks.
Re: triger [message #284838 is a reply to message #284824] Sat, 01 December 2007 12:25 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
How did you get?
Re: triger [message #284839 is a reply to message #284833] Sat, 01 December 2007 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@varu
Yes, this is basically what I meant.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#sthref8003
Although LOGON trigger executes itself in another transaction.

@Upperm
There are restrictions on what you can do in a logon trigger, you have to spawn a job to do that.
Once again why do you want this precise action? What do you think it means?
Who is the owner of the trigger?
Have a look at Responding to System Events through Triggers

By the way, if you just want SCOTT to fire the trigger you can use "after logon on scott.schema" then you don't need to check the user.

Regards
Michel

[Updated on: Sat, 01 December 2007 12:29]

Report message to a moderator

Re: triger [message #284840 is a reply to message #284839] Sat, 01 December 2007 13:18 Go to previous messageGo to next message
Upperm
Messages: 95
Registered: July 2003
Member
@michel

As I know, when using a job I'm asked to precise a specific time (hour:minute) in order that the job to be launched.
But the issue of my script we're discussing about, is to lock the user scott once he log in.
I want this precise action simply because I think of locking the accounts of the users (fakers) who try to connect to DataBase during a period of time they should not be at work.

The owner of trigger is one grantee having dba role.
Re: triger [message #284842 is a reply to message #284840] Sat, 01 December 2007 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can say that the job has to be run right now (sysdate). Actually it does it as soon as the logon trigger ends and the job coordinator awakes (that is in less than a minute).

Instead of locking the user, you can test the time in the logon trigger and use "raise_application_error" if the condition is not met.

Regards
Michel
Re: triger [message #284843 is a reply to message #284842] Sat, 01 December 2007 13:55 Go to previous messageGo to next message
Upperm
Messages: 95
Registered: July 2003
Member
@michel

Usng raise_application_error,here's what I have:

create or replace trigger trg_lock_scott
after logon
on scott.schema

Begin
if to_number(sysdate,'hh24') > 18 then
raise_application_error(-20010,'Not allowed to connect Now !');
End if ;
End ;
/


SQL>conn scott/tiger
ERROR:
ORA-00604: Une erreur s'est produite au niveau SQL rÚcursif 1
ORA-06502: PL/SQL : erreur numÚrique ou erreur sur une valeur
ORA-06512: Ó ligne 2


Avertissement : vous n'Ûtes plus connectÚ Ó ORACLE.
SQL>



Regards,
Re: triger [message #284844 is a reply to message #284843] Sat, 01 December 2007 14:05 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Re-read documentation about to_char(datetime) and to_number.

Regards
Michel
Previous Topic: Newbie help,where can i download extra tables
Next Topic: Prevent update on field value if no changes
Goto Forum:
  


Current Time: Fri Dec 09 02:15:10 CST 2016

Total time taken to generate the page: 0.26460 seconds