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. 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:
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. 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:
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
< >
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
< >
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;
< >
-- 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;
/
