We can lock certain Oracle user to prevent that user login to the database. User can be locked by using the following command :
oracle@mytestbed:~$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 5 23:34:57 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> ALTER USER testing ACCOUNT LOCK; User altered. SQL> quit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
Whenever the locked user try to connect, some error code will appear like this :
oracle@mytestbed:~$ sqlplus testing/password SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 5 23:35:04 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. ERROR: ORA-28000: the account is locked Enter user-name: ^C oracle@mytestbed:~$
To release the lock and let the user login again we simply do it by the following command :
oracle@mytestbed:~$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 5 23:37:59 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> ALTER USER testing ACCOUNT UNLOCK; User altered. SQL> quit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options oracle@mytestbed:~$
Here is the result, user testing can do login again :
oracle@mytestbed:~$ sqlplus testing/password SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 5 23:38:11 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL>