Password Management for Oracle8

1
Password Management for Oracle8

• Ari Kaplan
• Independent Consultant

2
(No Transcript)
3
Oracle8, Oracle7, and UNIX Password Capabilities
4
Password Functionality

• Account Locking
• Password Complexity Verifications
• Password History
• Password Lifetime and Expiration

5
Creating Users

• The CREATE USER command sets the initial password
  for a user account

CREATE USER IOUG_PERSON IDENTIFIED BY applaud_now
6
Describing User Attributes

• For existing accounts, you can view some
  attributes of that user, by entering

SELECT FROM ALL_USERS
The SQL shows
UsernameUser_IDPasswordDefault
TablespaceDate/Time that the user was
createdDefault Profile
7
Table and System Privileges

• The GRANT command gives privileges directly to
  user accounts and indirectly via roles
• The REVOKE command removes privileges from user
  accounts or roles
• Table Privileges SELECT/INSERT/UPDATE/DELETE/ALTE
  R
• System Privileges CREATE PUBLIC SYNONYM,
  UNLIMITED TABLESPACE, DROP ANY TABLE, etc.

8
Maintaining Restrictions via Profiles

• Restrictions on system and object resources are
  maintained via profiles
• Some of them include SESSION_PER_USER,
  CPU_PER_SESSION, CPU_PER_CALL, CONNECT_TIME,
  IDLE_TIME, LOGICAL_READS_PER_SESSION,
  LOGICAL_READS_PER_CALL, COMPOSITE_LIMIT,
  IDLE_TIME, and PRIVATE_SGA.
• To restrict user accounts to 15 minutes per
  connection, you would create a profile with the
  CONNECT_TIME set, then assign the user to the new
  profile.

9
Creating Profiles

• When a user account is created, they are assigned
  to the DEFAULT profile (unless otherwise
  specified). To create a new profile, use the
  CREATE PROFILE command and list all restrictions
  along with their values.

CREATE PROFILE profile_name LIMIT
SESSIONS_PER_USER 4
10
Assign a Profile to a User

• Once a profile has been created, you can
  associate users to the profile

ALTER USER username PROFILE profile_name

• It is through this method that different password
  options can be set for user accounts.

11
Password-Related Options that can be Defined for
Profiles

• FAILED_LOGIN_ATTEMPTS
• PASSWORD_GRACE_TIME
• PASSWORD_LIFE_TIME
• PASSWORD_LOCK_TIME
• PASSWORD_REUSE_MAX
• PASSWORD_REUSE_TIME
• PASSWORD_VERIFY_FUNCTION

12
Default Profile Password Parameter Values
13
Creating a Sample Password-Related Profile

• If you want to assign non-default password
  management options to user accounts, you can
  create a new profile. The following SQL
  statement below will lock a user account out of
  the database for one hour after failing to
  successfully login three times. Also, the
  password will automatically expire every thirty
  days.

CREATE PROFILE NORMAL_USERS LIMIT PASSWORD_LIFE_T
IME 30 FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIM
E 1/24
14
Password Management Options for a Profile

• The following SQL statement will prompt you for a
  profile. The result will be a listing of all
  defined password management options, along with
  their values.

COLUMN PROFILE FORMAT A15COLUMN RESOURCE_NAME
FORMAT A25COLUMN RESOURCE_TYPE FORMAT A15COLUMN
LIMIT FORMAT A15SELECT PROFILE,
RESOURCE_NAME, RESOURCE_TYPE, LIMITFROM
DBA_PROFILESWHERE PROFILE profile_name
AND RESOURCE_TYPE LIKE PASSWORD
15
Password Management Options for a Profile

• Here is a sample output from the previous SQL
• Enter value for profile_name NORMAL_USERS
• old 4 WHERE PROFILE profile_name AND
• new 4 WHERE PROFILE NORMAL_USERS AND
• PROFILE RESOURCE_NAME
  RESOURCE_TYPE LIMIT
• NORMAL_USERS FAILED_LOGIN_ATTEMPTS PASSWORD
  3
• NORMAL_USERS PASSWORD_LIFE_TIME PASSWORD 30
• NORMAL_USERS PASSWORD_REUSE_TIME PASSWORD
  DEFAULT
• NORMAL_USERS PASSWORD_VERIFY_FUNCTION PASSWORD
  DEFAULT
• NORMAL_USERS PASSWORD_LOCK_TIME PASSWORD
  .0416
• NORMAL_USERS PASSWORD_GRACE_TIME PASSWORD
  DEFAULT

16
The Default verify_function Provided by Oracle

• This function must be created in SYS schema, and
  you must connect sys/ltpasswordgt as sysdba before
  running the script. This snippet contains the
  start of the function and checks if the password
  is the same as the username.

The verify_function is shown in five parts 1.
The beginning of the verify_function, check if
the password is the same as the username2.
Check to make sure that the password has at least
4 characters3. Check to make sure that the
password is not too simple by comparing
against 8 simple words4. Check to make sure that
the password contains at least one letter,
one digit, and one punctuation mark5. Check if
the password differs from the previous password
by at least 3 letters
17
The Default verify_function Provided by Oracle
-Part 1

• 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 'abcdefghijklmnopqrstuvwxyzABCDEFGH
  IJKLMNOPQRSTUVWXYZ'
• punctarray'!"(),-/ltgt?_'
• --Check if the password is same as the username
• IF password username THEN
• raise_application_error(-20001, 'Password same
  as user')
• END IF

18
The Default verify_function Provided by Oracle
-Part 2

• This snippet checks to make sure that the
  password has at least 4 characters
• --Check for the minimum length of the password
• IF length(password) lt 4 THEN
• raise_application_error(-20002, 'Password
  length less than 4')
• END IF

19
The Default verify_function Provided by Oracle
-Part 3

• --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

20
The Default verify_function Provided by Oracle
-Part 4

• --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.
• isdigitFALSE 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
• isdigitTRUE
• 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
• ltltfindchargtgt
• ischarFALSE
• FOR i IN 1..length(chararray) LOOP

FOR j IN 1..m LOOP IF substr(password,j,1)
substr(chararray,i,1) THEN
ischarTRUE 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 ltltfindpunctgtgt ispunctFALSE
FOR i IN 1..length(punctarray) LOOP FOR j IN
1..m LOOP IF substr(password,j,1)
substr(punctarray,i,1) THEN
ispunctTRUE 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
ltltendsearchgtgt
21
The Default verify_function Provided by Oracle
-Part 5

• --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) lt 3 THEN
• IF length(password) lt 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 lt 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
22
Creating your own verify_function

• You can create your own verify function. It may
  be called anything you like (within reason no
  reserved words, etc.).
• The only requirements are that it be owned by the
  SYS user account, and that it sticks to the
  following format

function_name ( userid_parameter IN
VARCHAR(30), password_parameter IN
VARCHAR(30), old_password_parameter IN
VARCHAR(30))RETURN BOOLEAN
23
Assigning the verify_function to a Profile

• Once the function is defined, it must be assigned
  to one or more profiles in order to be used.

ALTER PROFILE profile_name LIMIT
PASSWORD_VERIFY_FUNCTION function_name
24
Expiring/Unlocking Passwords
Expiring This SQL will force users to change
their password the next time that they log in
ALTER USER username PASSWORD EXPIRE

• Manually unlock a user The account may be
  unlocked by only two methods
• 1) The user waits for the specified
  PASSWORD_LOCK_TIME period.
• 2) The security administrator will have to
  manually unlock the account

ALTER USER username ACCOUNT UNLOCK
25
Determining the Password Status of a Particular
User Account
The following SQL will prompt you for a user
account name, and then show account status (if
the account has expired), the date that the
account was locked out of the database (if any),
the date that the password will expire on (if
any), and the profile that the user account has
been assigned to.
COLUMN USERNAME FORMAT A14COLUMN ACCOUNT_STATUS
FORMAT A14COLUMN LOCK_DATE FORMAT A14COLUMN
EXPIRY_DATE FORMAT A16COLUMN PROFILE FORMAT
A15SELECT USERNAME, ACCOUNT_STATUS, TO_CHAR(LOCK
_DATE,DD-MON-YY) LOCK_DATE, TO_CHAR(EXPIRY_DATE
,DD-MON-YY) EXPIRY_DATE, PROFILEFROM
DBA_USERSWHERE USERNAME username
26
Determining the Password Status of a Particular
User Account

• A sample output from the above SQL follows
• Enter value for username IOUG_PERSON
• old 6 WHERE USERNAME username
• new 6 WHERE USERNAME IOUG_PERSON
• USERNAME ACCOUNT_STATUS LOCK_DATE
  EXPIRY_DATE PROFILE
• IOUG_PERSON EXPIRED
  00-DECEMBER-00 NORMAL_USERS

27
Data Dictionary Views Used for Password Management

• There are a few data dictionary views that can be
  queried to determine the status of password
  management in your database
• USER_PASSWORD_LIMITS,ALL_PASSWORD_LIMITS,
  DBA_PASSWORD_LIMITS
• USER_USERS, ALL_USERS, DBA_USERS
• USER_PROFILES, ALL_PROFILES, DBA_PROFILES

28
The Donts of Oracle Passwords

• Do not use SQLPLUS username/password to go into
  SQLPlus. For example, in UNIX, someone can type
  ps -ef and see all passwords clearly!
• Do not use EXP80 or IMP80 with the USERID
  parameter on the command line. Also, the ps -ef
  command would see it! Instead, use a parameter
  file and specify the PARFILE parameter.
• Do not use SQLLDR with the USERID parameter on
  the command line for the same reasons.