1 / 28

Password Management for Oracle8

Password Management for Oracle8. Ari Kaplan Independent Consultant. Oracle8, Oracle7, and UNIX Password Capabilities. Password Functionality. Account Locking Password Complexity Verifications Password History Password Lifetime and Expiration. Creating Users.

rhona
Download Presentation

Password Management for Oracle8

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Password Management forOracle8 Ari Kaplan Independent Consultant

  2. Oracle8, Oracle7, and UNIX Password Capabilities

  3. Password Functionality • Account Locking • Password Complexity Verifications • Password History • Password Lifetime and Expiration

  4. Creating Users The CREATE USER command sets the initial password for a user account: CREATE USER IOUG_PERSON IDENTIFIED BY applaud_now;

  5. 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

  6. Table and System Privileges • The GRANTcommand gives privileges directly to user accounts and indirectly via roles • The REVOKEcommand removes privileges from user accounts or roles • Table Privileges: SELECT/INSERT/UPDATE/DELETE/ALTER • System Privileges: CREATE PUBLIC SYNONYM, UNLIMITED TABLESPACE, DROP ANY TABLE, etc.

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

  8. 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;

  9. 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.

  10. 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

  11. Default Profile Password Parameter Values

  12. 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_TIME 30 FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1/24;

  13. 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%’;

  14. 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

  15. The Default verify_function Provided by Oracle This function must be created in SYS schema, and you must connect sys/<password> 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

  16. 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:= '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;

  17. 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) < 4 THEN raise_application_error(-20002, 'Password length less than 4'); END IF;

  18. 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;

  19. The Default verify_function Provided by Oracle -Part 4 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 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

  20. The Default verify_function Provided by Oracle -Part 5 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; --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);

  21. 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

  22. 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;

  23. Expiring/Unlocking Passwords Expiring: This SQL will force users to change their password the next time that they log in: 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 PASSWORD EXPIRE; ALTER USER username ACCOUNT UNLOCK;

  24. 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’;

  25. 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’ USERNAMEACCOUNT_STATUS LOCK_DATEEXPIRY_DATE PROFILE IOUG_PERSON EXPIRED 00-DECEMBER-00 NORMAL_USERS

  26. 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

  27. The Don’ts of Oracle Passwords Do not use SQLPLUS username/password to go into SQL*Plus. 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.

More Related