In practice, the password operation of Oracle is implemented through profile, while the resource is controlled by the resource consumption group. profile is actually a limitation.
You can use profile to control the use of passwords. Generally, there are four:
1) Password History
Here, there are two parameters: password_reuse_time and password_reuse_max. A good practice is to associate these two parameters. Example: password_reuse_time = 30, password_reuse_max = 10,
The user can reuse the password after 30 days. The password must be changed more than 10 times.
Lab:
Session 1: SYS
Sys @ orcl> Create profile P1 limit password_reuse_time 1/1440 password_reuse_max 1;
Profile created.
Sys @ orcl> alter user Scott profile P1;
User altered.
Sys @ orcl> alter user Scott password expire;
User altered.
Sys @ orcl> alter profile P1 limit password_reuse_time 5/1440 password_reuse_max 1; -- this password can be reused in 5 minutes, but it must be changed to another password once.
Profile altered.
Sys @ orcl> alter user Scott password expire;
User altered.
Session 2: Scott
Scott @ orcl> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the partitioning, OLAP and Data Mining options
[Oracle @ localhost ~] $ Sqlplus/nolog
SQL * Plus: Release 10.2.0.1.0-production on Mon Sep 3 01:11:09 2012
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Idle> conn Scott/Oracle
Error:
ORA-28001: the password has expired
Changing password for Scott
New Password: -- use the original password, that is, Oracle
Retype new password:
Error:
The ORA-28007: the password cannot be reused
Password unchanged
Idle> conn Scott/Oracle
Error:
ORA-28001: the password has expired
Changing password for Scott
New Password: -- use the new password and change it to think.
Retype new password:
Password Changed
Connected.
Session 1: SYS
Sys @ orcl> alter user Scott password expire;
User altered.
Session 2: Scott
Scott @ orcl> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the partitioning, OLAP and Data Mining options
[Oracle @ localhost ~] $ Sqlplus/nolog
SQL * Plus: Release 10.2.0.1.0-production on Mon Sep 3 01:19:04 2012
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Idle> conn Scott/think
Error:
ORA-28001: the password has expired
Changing password for Scott
New Password: -- use the oldest password, that is, Oracle
Retype new password:
Password Changed
Connected.
Scott @ orcl>
2) password login verification
In this regard, there are two parameters:
Failed_login_attempts: Maximum number of failed logons allowed before locking
Password_lock_time: Lock time
Lab:
Session 1: SYS
Sys @ orcl> drop profile P1 cascade;
Profile dropped.
Sys @ orcl> Create profile P1 limit failed_login_attempts 1 password_lock_time 1/1440; -- the instance is locked once it fails and locked for 1 minute
Profile created.
Sys @ orcl> alter user Scott profile P1;
User altered.
Session 2: Scott
[Oracle @ localhost ~] $ Sqlplus/nolog
SQL * Plus: Release 10.2.0.1.0-production on Mon Sep 3 01:42:46 2012
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Idle> conn Scott/think
Error:
ORA-01017: invalid username/password; logon denied
Idle> conn Scott/Oracle
Error:
ORA-28000: the account is locked
Idle> conn Scott/Oracle -- 1 minute later
Connected.
3) password Lifecycle
Likewise, there are two parameters:
Password_life_time: Password life
Password_grace_time: The grace time, especially the time before the lifetime.
Lab:
Session 1: SYS
Sys @ orcl> drop profile P1 cascade;
Profile dropped.
Sys @ orcl> Create profile P1 limit password_life_time 2/1440 password_grace_time 2/1440;
Profile created.
Sys @ orcl> alter user Scott profile P1;
User altered.
Session 2: Scott
[Oracle @ localhost ~] $ Sqlplus/nolog
SQL * Plus: Release 10.2.0.1.0-production on Mon Sep 3 01:56:59 2012
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Idle> conn Scott/Oracle
Error:
ORA-28002: the password will expire within 0 days
Connected.
4) Complexity of passwords
There is a password function in $ ORACLE_HOME/rdbms/admin/utlpwdmg. SQL to control the complexity of the password.
The function is extracted as follows:
Create or replace function verify_function
(Username varchar2,
Password varchar2,
Old_password varchar2)
Return Boolean is
N Boolean;
M integer;
Differ integer;
Isdigit Boolean;
Ischar Boolean;
Ispunct Boolean;
Digitarray varchar2 (20 );
Punctarray varchar2 (25 );
Chararray varchar2 (52 );
Begin
Digitarray: = '000000 ';
Chararray: = 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxy ';
Punctarray: = '! "# $ % & () ''' * +,-/:; <=>? _';
-- Check if the password is same as the username
If nls_lower (password) = nls_lower (username) then
Raise_application_error (-20001, 'password same as or similar to user ');
End if;
-- Check for the minimum length of the password
If length (password) <4 then
Raise_application_error (-20002, 'password length less than 4 ');
End if;
-- Check if the password is too simple. A Dictionary of words may be
-- Maintained and a check may be made so as not to allow the words
-- That are too simple for the password.
If nls_lower (password) in ('Welcome ', 'database', 'account', 'user', 'Password', 'oracle', 'computer ', 'abcd') then
Raise_application_error (-20002, 'password too simple ');
End if;
-- Check if the password contains at least one letter, one digit and one
-- Punctuation mark.
-- 1. Check for the digit
Isdigit: = false;
M: = length (password );
For I in 1 .. 10 Loop
For J in 1 .. m Loop
If substr (password, J, 1) = substr (digitarray, I, 1) then
Isdigit: = true;
Goto findchar;
End if;
End loop;
End loop;
If isdigit = false then
Raise_application_error (-20003, 'password shoshould contain at least one digit, one character and one punctuation ');
End if;
-- 2. Check for the character
<Findchar>
Ischar: = false;
For I in 1 .. length (chararray) loop
For J in 1 .. m Loop
If substr (password, J, 1) = substr (chararray, I, 1) then
Ischar: = true;
Goto findpunct;
End if;
End loop;
End loop;
If ischar = false then
Raise_application_error (-20003, 'password shoshould contain at least one \
Digit, one character and one punctuation ');
End if;
-- 3. Check for the punctuation
<Findpunct>
Ispunct: = false;
For I in 1 .. length (punctarray) loop
For J in 1 .. m Loop
If substr (password, J, 1) = substr (punctarray, I, 1) then
Ispunct: = true;
Goto endsearch;
End if;
End loop;
End loop;
If ispunct = false then
Raise_application_error (-20003, 'password shoshould contain at least one \
Digit, one character and one punctuation ');
End if;
<Endsearch>
-- Check if the password differs from the previous password by at least
-- 3 letters
If old_password is not null then
Differ: = length (old_password)-length (password );
If ABS (differ) <3 then
If length (password) <length (old_password) then
M: = length (password );
Else
M: = length (old_password );
End if;
Differ: = ABS (differ );
For I in 1 .. m Loop
If substr (password, I, 1 )! = Substr (old_password, I, 1) then
Differ: = differ + 1;
End if;
End loop;
If differ <3 then
Raise_application_error (-20004, 'password shocould differ by \
Least 3 characters ');
End if;
End if;
End if;
-- Everything is fine; return true;
Return (true );
End;
/