- Create PROFILE
- Alter PROFILE
- Drop PROFILE
- PROFILE Parameters ( Resource Limits & Password Limits)
- PASSWORD_VERIFY_FUNCTION
CONN /AS SYSDBA
Create PROFILE
CREATE PROFILE DBA_USER LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;
Alter PROFILE
ALTER PROFILE DBA_USER LIMIT FAILED_LOGIN_ATTEMPTS 4;
Drop PROFILE
DROP PROFILE <Profile_Name> CASCADE;
PROFILE PARAMETERS
Setting Profile Resource Limits
SESSIONS_PER_USER 5 --INTGR/UNLIMITED/DEFAULT
[The user can have any number of concurrent sessions.]
CPU_PER_SESSION 2160000 --INTGR/UNLIMITED/DEFAULT
[In a single session, the user can consume an 6 Hr (6*60*60)*100 of CPU time. (100ths of a second)]
CPU_PER_CALL 3000 --INTGR/UNLIMITED/DEFAULT
[A single call made by the user cannot consume more than 30 seconds of CPU time. It is 100ths of a second]
CONNECT_TIME 45 --INTGR/UNLIMITED/DEFAULT
[A single session cannot last for more than 45 minutes.]
IDLE_TIME 10 --INTGR/UNLIMITED/DEFAULT
[Specify the permitted periods of continuous inactive time during a session, expressed in minutes.
Long-running queries and other operations are not subject to this limit.]
LOGICAL_READS_PER_SESSION 300000 --INTGR/UNLIMITED/DEFAULT
[In a single session, the number of data blocks read from memory and disk is subject to the limit specified in the DEFAULT profile.]
LOGICAL_READS_PER_CALL 1000 --INTGR/UNLIMITED/DEFAULT
[A single call made by the user cannot read more than 1000 data blocks from memory and disk.]
COMPOSITE_LIMIT 5000000 --INTGR/UNLIMITED/DEFAULT
[In a single session, the total resource cost cannot exceed 5 million service units.
The formula for calculating the total resource cost is specified by the ALTER RESOURCE COST statement.]
PRIVATE_SGA 200K --SIZE_CLAUSE/UNLIMITED/DEFAULT
[A single session cannot allocate more than 15 kilobytes of memory in the SGA.] (useful for systems using multi-threaded server MTS)
Setting Profile Password Limits
FAILED_LOGIN_ATTEMPTS 3 --UNLIMITED/DEFAULT
[The number of failed attempts to log in to the user account before the account is locked.]
PASSWORD_LIFE_TIME 15 --UNLIMITED/DEFAULT
[The number of days the same password can be used for authentication]
PASSWORD_REUSE_TIME 365 --UNLIMITED/DEFAULT
[The number of days between reuses of a password]
PASSWORD_REUSE_MAX 5 --UNLIMITED/DEFAULT
[The number of times a password must be changed before it can be reused]
PASSWORD_LOCK_TIME .00139 --UNLIMITED/DEFAULT
[the number of days an account will be locked after the specified number of consecutive
failed login attempts defined by FAILED_LOGIN_ATTEMPTS] Here 2 mins
PASSWORD_GRACE_TIME 5 --UNLIMITED/DEFAULT
[The number of days after the grace period begins during which a warning is issued and login is allowed.
If the password is not changed during the grace period, the password expires]
PASSWORD_VERIFY_FUNCTION NULL --VERIFY_FUNCTION/NULL/DEFAULT
[Verify passwords for length, content, and complexity.]
Create Function for PASSWORD_VERIFY_FUNCTION
CREATE OR REPLACE FUNCTION SYS.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 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 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 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 should differ by at \
least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/
No comments:
Post a Comment