triger [message #284824] |
Sat, 01 December 2007 11:00 |
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 #284826 is a reply to message #284824] |
Sat, 01 December 2007 11:29 |
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 #284833 is a reply to message #284824] |
Sat, 01 December 2007 12:04 |
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 #284835 is a reply to message #284833] |
Sat, 01 December 2007 12:13 |
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 |
|
BlackSwan
Messages: 26766 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 #284840 is a reply to message #284839] |
Sat, 01 December 2007 13:18 |
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 #284843 is a reply to message #284842] |
Sat, 01 December 2007 13:55 |
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 |
|
Michel Cadot
Messages: 68722 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Re-read documentation about to_char(datetime) and to_number.
Regards
Michel
|
|
|