You can do this by setting the profiles..
===here some overiew of profiles ===
You can get an idea about scripting Oracle password security profiles by examining Oracle's utlpwdmg.sql script located in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql.
The script notes:
. . .
Rem utlpwdmg.sql - script for Default Password Resource Limits
. . .
-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- A default password complexity function is also provided.
-- This function makes the minimum complexity checks like
-- the minimum length of the password, password not same as the
-- username, etc. The user may enhance this function according to
-- the need.
-- This function must be created in SYS schema.
-- connect sys/ as sysdba before running the script
Oracle password profile security syntax
Oracle password security is implemented via Oracle "profiles" which are assigned to users. Here is the Oracle security profile syntax:
ALTER PROFILE profile_name LIMIT pw_limit(s) range
pw_limit = PASSWORD_LIFE_TIME
range = UNLIMITED | DEFAULT | expression
We start by creating security "profiles" in Oracle and then alter the user to belong to the profile group:
PASSWORD_LIFE_TIME = 365,
PASSWORD_GRACE_TIME = 10,
PASSWORD_REUSE_TIME = UNLIMITED,
PASSWORD_REUSE_MAX = 0,
FAILED_LOGIN_ATTEMPTS = 3,
PASSWORD_LOCK_TIME = UNLIMITED;
create user fred identified by flintstone profile finance_user;
We see the following "alter profile" parameters, which are invoked as;
failed_login_attempts = 4;
Oracle password security profile parameters
Here are the password security parameters:
* failed_login_attempts - This is the number of failed login attempts before locking the Oracle user account. The default is three failed attempts.
* password_grace_time - This is the grace period after the password_life_time limit is exceeded.
* password_life_time - This is how long an existing password is valid. The default here forces a password change every 60 days.
* password_lock_time – This specifies how long to lock the account after the failed login attempts is met. Most DBA’s set this value to UNLIMITED.
* password_reuse_max – This is the number of times that you may re-user a passwords and is intended to prevent repeating password cycles (north, south, east, west).
* password_reuse_time – This parameter specifies a time limit before a previous password can be re-entered. To never allow a re-used password set password_reuse_time to UNLIMITED.
* password_verify_function - This allows you to specify the name of a custom password verification function.