Home » RDBMS Server » Security » DB password limitations/constraints
| DB password limitations/constraints [message #242052] |
Thu, 31 May 2007 08:53  |
coolbalaga
Messages: 24
Registered: September 2006
|
Junior Member |
|
|
Hello,
Can I have a sys or system password with the whitespaces
like "my name" or " tiger" or
(note the spaces between the words)
Is there any document specify the rules/limitations for the oracle database user.
Thanks.
|
|
|
|
|
|
|
|
|
|
| Re: DB password limitations/constraints [message #242589 is a reply to message #242241] |
Mon, 04 June 2007 04:18  |
coolbalaga
Messages: 24
Registered: September 2006
|
Junior Member |
|
|
Thanks for the replies.
But do we have an oracle document mentioning the rules for the password usage.
for eg: (may/maynot be related to the oracle)
The password should always start with the alphabet and no whitespaces between the words.
something like the above.
|
|
|
|
|
|
| Re: DB password limitations/constraints [message #243307 is a reply to message #242052] |
Wed, 06 June 2007 16:50 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Look at the script in ORACLE_HOME/rdbms/admin for enforcing password complexity. Edit their script and compile it. Then you must make each group use it in Oracle's admin tool. And you are correct about the password starting with alpha. I could not get that to work, It needs an alpha char in position 1.
7.4 Password Management Policy
Database security systems that are dependent on passwords require that passwords be kept secret at all times. Because passwords are vulnerable to theft, forgery, and misuse, Oracle Database uses a password management policy. DBAs and security officers control this policy through user profiles, enabling greater control over database security.
Use the CREATE PROFILE statement to create a user profile. The profile is assigned to a user with the CREATE USER or ALTER USER statement. Details of creating and altering database users are not discussed in this section. This section is concerned with the password parameters that can be specified using the CREATE PROFILE (or ALTER PROFILE) statement.
This section contains the following topics relating to Oracle password management:
Account Locking
Password Aging and Expiration
Password History
Password Complexity Verification
See Also:
"Managing Resources with Profiles"
"Managing Oracle Users"
Oracle Database SQL Reference for syntax and specific information about SQL statements discussed in this section
7.4.1 Account Locking
When a particular user exceeds a designated number of failed login attempts, the server automatically locks that user account. You specify the permissible number of failed login attempts using the CREATE PROFILE statement. You can also specify the amount of time accounts remain locked.
In the following example, the maximum number of failed login attempts for the user johndoe is four, and the amount of time the account will remain locked is 30 days. The account will unlock automatically after the passage of 30 days.
CREATE PROFILE prof LIMIT
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME 30;
ALTER USER johndoe PROFILE prof;
If you do not specify a time interval for unlocking the account, then PASSWORD_LOCK_TIME assumes the value specified in a default profile. If you specify PASSWORD_LOCK_TIME as UNLIMITED, then the account must be explicitly unlocked using an ALTER USER statement. For example, assuming that PASSWORD_LOCK_TIME UNLIMITED is specified for johndoe, then the following statement must be used to unlock the account:
ALTER USER johndoe ACCOUNT UNLOCK;
After a user successfully logs into an account, the unsuccessful login attempt count for the user, if it exists, is reset to 0.
The security officer can also explicitly lock user accounts. When this occurs, the account cannot be unlocked automatically, and only the security officer should unlock the account. The CREATE USER or ALTER USER statements are used to explicitly lock or unlock user accounts. For example, the following statement locks the user account, susan:
ALTER USER susan ACCOUNT LOCK;
7.4.2 Password Aging and Expiration
Use the CREATE PROFILE statement to specify a maximum lifetime for passwords. When the specified amount of time passes and the password expires, the user or DBA must change the password. The following statements create and assign a profile to user johndoe, and the PASSWORD_LIFE_TIME clause specifies that johndoe can use the same password for 90 days before it expires.
CREATE PROFILE prof LIMIT
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME 30
PASSWORD_LIFE_TIME 90;
ALTER USER johndoe PROFILE prof;
You can also specify a grace period for password expiration. Users enter the grace period upon the first attempt to log in to a database account after their password has expired. During the grace period, a warning message appears each time users try to log in to their accounts, and continues to appear until the grace period expires. Users must change the password within the grace period. If the password is not changed within the grace period, then users are prompted for a new password each time an attempt is made to access their accounts. Access to an account is denied until a new password is supplied.
Figure 7-2 shows the chronology of the password lifetime and grace period.
Figure 7-2 Chronology of Password Lifetime and Grace Period
Description of the illustration admin024.gif
In the following example, the profile assigned to johndoe includes the specification of a grace period: PASSWORD_GRACE_TIME = 3. The first time johndoe tries to log in to the database after 90 days (this can be any day after the 90th day, that is, the 70th day, 100th day, or another day), he receives a warning message that his password will expire in three days. If three days pass, and he does not change her password, then the password expires. After this, he receives a prompt to change his password on any attempt to log in, and cannot log in until he does so.
CREATE PROFILE prof LIMIT
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME 30
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 3;
ALTER USER johndoe PROFILE prof;
Oracle provides a means of explicitly expiring a password. The CREATE USER and ALTER USER statements provide this functionality. The following statement creates a user with an expired password. This setting forces the user to change the password before the user can log in to the database.
CREATE USER jbrown
IDENTIFIED BY zX83yT
...
PASSWORD EXPIRE;
7.4.3 Password History
The following two parameters control user ability to reuse an old password:
Table 7-1 Parameters Controlling Reuse of an Old Password
Parameter Name Description and Use
PASSWORD_REUSE_TIME Requires either of the following:
A number specifying how many days (or a fraction of a day) between the earlier use of a password and its next use
The word UNLIMITED.
PASSWORD_REUSE_MAX Requires either of the following:
An integer to specify the number of password changes required before a password can be reused
The word UNLIMITED
If you specify neither, then the user can reuse passwords at any time, which is not a good security practice.
If neither parameter is UNLIMITED, then password reuse is allowed, but only after meeting both conditions. The user must have changed the password the specified number of times, and the specified number of days must have passed since the old password was last used.
For example, suppose that the user A's profile had PASSWORD_REUSE_MAX set to 10 and PASSWORD_REUSE_TIME set to 30. Then user A cannot reuse a password until the password has been reset 10 times, and until 30 days had passed since the password was last used.
If either parameter is specified as UNLIMITED, then the user can never reuse a password.
If both parameters are set to UNLIMITED, then Oracle ignores both, and the user can reuse any password at any time.
Note:
If you specify DEFAULT for either parameter, then Oracle uses the value defined in the DEFAULT profile, which sets all parameters to UNLIMITED. Oracle thus uses UNLIMITED for any parameter specified as DEFAULT, unless you change the setting for that parameter in the DEFAULT profile.
7.4.4 Password Complexity Verification
Oracle sample password complexity verification routine can be specified using a PL/SQL script (UTLPWDMG.SQL), which sets the default profile parameters.
The password complexity verification routine ensures that the password meets the following requirements:
Is at least four characters long
Differs from the user name
Has at least one alpha, one numeric, and one punctuation mark character
Is not simple or obvious, such as welcome, account, database, or user
Differs from the previous password by at least 3 characters
Note:
The ALTER USER command now has a REPLACE clause by using which users can change their own unexpired passwords by supplying the old password to authenticate themselves.
If the password has expired, then the user cannot log in to SQL to issue the ALTER USER command. Instead, the OCIPasswordChange() function must be used, which also requires the old password.
A DBA with ALTER ANY USER privilege can alter any user password (force a new password) without supplying the old one.
7.4.4.1 Password Verification Routine Formatting Guidelines
You can enhance the existing password verification complexity routine or create other password verification routines using PL/SQL or third-party tools.
The PL/SQL call must adhere to the following format:
routine_name
(
userid_parameter IN VARCHAR(30),
password_parameter IN VARCHAR (30),
old_password_parameter IN VARCHAR (30)
)
RETURN BOOLEAN
After a new routine is created, it must be assigned as the password verification routine by using the user profile or the system default profile.
CREATE/ALTER PROFILE profile_name LIMIT
PASSWORD_VERIFY_FUNCTION routine_name
The password verification routine must be owned by the SYS user.
7.4.4.2 Sample Password Verification Routine
You can use the following sample password verification routine as a model when developing your own complexity checks for a new password.
The default password complexity function performs the following minimum complexity checks:
The password satisfies minimum length requirements.
The password is not the username. You can modify this function based on your requirements.
This function must be created in SYS schema, and you must CONNECT SYS/password AS SYSDBA before running the script.
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:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()''*+,-/:;<=>?_';
--Check if the password is same as the username
IF password = username THEN
raise_application_error(-20001, 'Password same as 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
--You may delete 1. and replace with 2. or 3.
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 should 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 should 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 should \
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 = '' THEN
raise_application_error(-20004, 'Old password is null');
END IF;
--Everything is fine; return TRUE ;
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 should differ by at \
least 3 characters');
END IF;
END IF;
--Everything is fine; return TRUE ;
RETURN(TRUE);
END;
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Dec 11 19:46:09 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
