OCP - Oracle9i Database Administrator Fundamentals 1 Exam Prep - Module 14: Managing Password Security and Resources

Practice: Managing Password Security and Resources.

This practice covers the implementation of password management within a database.

Instructions

1. Logon as the user system and create a new user named JEFF. Assign the users tablespace as Jeff's default tablespace and the temp tablespace as Jeff's temporary tablespace. Also grant Jeff 1 meg of quota on both the users and indx tablespaces. Grant both the connect and resource roles to Jeff.

SQL> CONNECT system/manager

Connected.

SQL> CREATE USER jeff

2 IDENTIFIED BY superman

3 DEFAULT TABLESPACE users

4 TEMPORARY TABLESPACE temp

5 QUOTA 1M ON users

6 QUOTA 1M ON indx;

User created.

SQL> GRANT connect,resource TO jeff;

Grant succeeded.

2. Enable password management by running script @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql.

SQL> CONNECT system/manager as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

Function created.

Profile altered.

3. Try to change the password for user Jeff to Jeff. What happens?

SQL> ALTER USER jeff IDENTIFIED BY jeff;

ALTER USER jeff IDENTIFIED BY jeff

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20001: Password same as or similar to user

4. Try changing the password for Jeff to follow the password management format. If successful, try to logon as Jeff with his new password.

Hint: Password should contain at least one digit, one character, and one punctuation mark.

SQL> ALTER USER jeff

2 IDENTIFIED BY super1$;

User altered.

SQL> connect jeff/super1$

Connected.

SQL> show user

USER is "JEFF"

SQL>

Practice: Managing Password Security and Resources.

This practice covers the implementation of password management within a database.

Instructions

SQL> CONNECT system/manager

Connected.

SQL> CREATE USER jeff

2 IDENTIFIED BY superman

3 DEFAULT TABLESPACE users

4 TEMPORARY TABLESPACE temp

5 QUOTA 1M ON users

6 QUOTA 1M ON indx;

User created.

SQL> GRANT connect,resource TO jeff;

Grant succeeded.

2. Enable password management by running script @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql.

SQL> CONNECT system/manager as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

Function created.

Profile altered.

3. Try to change the password for user Jeff to Jeff. What happens?

SQL> ALTER USER jeff IDENTIFIED BY jeff;

ALTER USER jeff IDENTIFIED BY jeff

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20001: Password same as or similar to user

4. Try changing the password for Jeff to follow the password management format. If successful, try to logon as Jeff with his new password.

Hint: Password should contain at least one digit, one character, and one punctuation mark.

SQL> ALTER USER jeff

2 IDENTIFIED BY super1$;

User altered.

SQL> connect jeff/super1$

Connected.

SQL> show user

USER is "JEFF"

SQL>

Practice: Administer profiles.

This practice covers the administration of profiles within a database.

Instructions

1. Alter the DEFAULT profile to ensure the following applies to users assigned the DEFAULT profile:

- After two login attempts, the account should be locked.

- The password should expire after 30 days.

- The same password should not be reused for at least one minute.

- The account should have a grace period of five days to change an expired password.

- Ensure that the requirements given have been implemented.

Hint Use the ALTER PROFILE command to change the default profile limits. Query the data dictionary view

DBA_PROFILES to verify the result..

SQL> connect system/manager as sysdba

Connected.

SQL> ALTER PROFILE default LIMIT

2 FAILED_LOGIN_ATTEMPTS 2

3 PASSWORD_LIFE_TIME 30

4 PASSWORD_REUSE_TIME 1/1440

5 PASSWORD_GRACE_TIME 5;

Profile altered.

SQL> SELECT resource_name, limit

2 FROM dba_profiles

3 WHERE profile='DEFAULT'

4 AND resource_type='PASSWORD';

RESOURCE_NAME LIMIT

---------------------------- --------------------

FAILED_LOGIN_ATTEMPTS 2

PASSWORD_LIFE_TIME 30

PASSWORD_REUSE_TIME .0006

PASSWORD_REUSE_MAX UNLIMITED

PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION

PASSWORD_LOCK_TIME .0006

PASSWORD_GRACE_TIME 5

7 rows selected.

2. Login as user Jeff supplying an invalid password. Try this twice, then log in again, this time supplying the correct password. What happens?

SQL> CONNECT jeff/superman

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> CONNECT jeff/super

ERROR:

ORA-01017: invalid username/password; logon denied

SQL> CONNECT jeff/super1$

ERROR:

ORA-28000: the account is locked

3. Using data dictionary view DBA_USERS verify user Jeff is locked. Unlock the account for user Jeff. After unlocking user Jeff connect as Jeff.

Hint: Execute the ALTER USER command to unlock the account.

SQL> SELECT username, account_status

2* FROM dba_users

SQL> /

USERNAME ACCOUNT_STATUS

------------------------------ -------------------------

SYS OPEN

SYSTEM OPEN

OUTLN OPEN

DBSNMP OPEN

ORDSYS OPEN

HR OPEN

MDSYS OPEN

OE OPEN

SH OPEN

JEFF LOCKED(TIMED)

10 rows selected.

SQL> ALTER USER jeff

2* ACCOUNT UNLOCK;

User altered.

SQL> CONNECT jeff/super1

Connected.

4. Disable password checks for the DEFAULT profile.

Hint: Execute the ALTER PROFILE command to disable the password checks.

SQL> connect system/manager

Connected.

SQL> ALTER PROFILE default LIMIT

2 FAILED_LOGIN_ATTEMPTS UNLIMITED

3 PASSWORD_LIFE_TIME UNLIMITED

4 PASSWORD_REUSE_TIME UNLIMITED

5 PASSWORD_REUSE_MAX UNLIMITED

6 PASSWORD_VERIFY_FUNCTION NULL

7 PASSWORD_LOCK_TIME UNLIMITED

8 PASSWORD_GRACE_TIME UNLIMITED;

Profile altered.

5. Log in to user Jeff supplying an invalid password. Try this twice, then log in again, this time supplying

the correct password. What happens? Why?

SQL> CONNECT jeff/superman

ERROR:

ORA-01017: invalid username/password; logon denied

SQL> CONNECT jeff/super

ERROR:

ORA-01017: invalid username/password; logon denied

SQL> CONNECT jeff/super1$

Connected.

Practice: Control use of resources using profiles.

This practice covers using profiles to control the use of resources.

Instructions

1. Create a new profile called OLTP to ensure the following applies to users assigned the OLPT profile:

- Limit the number of logical reads per session to 100,000.

- Limit the number of logical reads per call to 1000.

- Limit the connect time to 15 minutes.

- Limit the idle time to 5 minutes.

- Limit the number of sessions per user to 4.

- Limit the amount of cpu per session to 2 minutes (note the time is in hundreths of a second).

- Ensure that the requirements given have been implemented.

Hints

- Use the ALTER PROFILE command to change the default profile limits.

- Use the ALTER SYSTEM command to enforce the limits.

- Query the data dictionary view DBA_PROFILES to verify the result of the alter profile

command and the show parameter command to determine the setting for the RESOURCE_LIMIT

parameter.

SQL> connect system/manager as sysdba

Connected.

SQL> CREATE PROFILE oltp LIMIT

2 LOGICAL_READS_PER_SESSION 100000

3 LOGICAL_READS_PER_CALL 1000

4 CONNECT_TIME 15

5 IDLE_TIME 5

6 CPU_PER_SESSION 12000

7 SESSIONS_PER_USER 4;

Profile created.

SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;

System altered.

SQL> select resource_name, resource_type, limit

2 from dba_profiles

3 where profile = 'OLTP';

RESOURCE_NAME RESOURCE LIMIT

-------------------------------- -------- ---------------

COMPOSITE_LIMIT KERNEL DEFAULT

SESSIONS_PER_USER KERNEL 4

CPU_PER_SESSION KERNEL 12000

CPU_PER_CALL KERNEL DEFAULT

LOGICAL_READS_PER_SESSION KERNEL 100000

LOGICAL_READS_PER_CALL KERNEL 1000

IDLE_TIME KERNEL 5

CONNECT_TIME KERNEL 15

PRIVATE_SGA KERNEL DEFAULT

FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT

PASSWORD_LIFE_TIME PASSWORD DEFAULT

PASSWORD_REUSE_TIME PASSWORD DEFAULT

PASSWORD_REUSE_MAX PASSWORD DEFAULT

PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT

PASSWORD_LOCK_TIME PASSWORD DEFAULT

PASSWORD_GRACE_TIME PASSWORD DEFAULT

16 rows selected.

2. Create a new user called test with a password of test123$. Assign the user tablespace as the default tablespace for test and the temp tablespace as the temporary tablespace for test. Grant both the connect and resource roles to test and assign the OLTP profile to test.

SQL> create user test identified by test123$

2 default tablespace users

3 temporary tablespace temp;

User created.

SQL> grant connect, resource to test;

Grant succeeded.

SQL> alter user test profile oltp;

User altered.

3. Logon as the user TEST and create a single column table. Write a PL/SQL procedure to insert 1000 rows into the table. If you are successful, select the count of rows in the table.

SQL> connect test/test123$

Connected.

SQL> create table t1 (c1 number);

Table created.

SQL> begin

2 for i in 1..1000 loop

3 insert into t1 values (i);

4 end loop;

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

COUNT(*)

----------

1000

4. Now try to insert 10,000 rows into the table. What happens and why?

SQL> begin

2 for i in 1..10000 loop

3 insert into t1 values (i);

4 end loop;

5 end;

6 /

begin

ERROR at line 1:

ORA-02394: exceeded session limit on IO usage, you are being logged off

ORA-06512: at line 3

Profile altered.

Answer: The loop tried to perform more than 1000 logical I/Os which is limited by the LOGICAL_CALLS_PER_SESSION in the OLTP profile.

5. Log in as the user system and set all of the limits that were specified in step number 1 above for the OLTP profile to unlimited. Next, disable profiles in the database by setting in RESOURCE_LIMIT parameter to false.

SQL> CONNECT SYSTEM/MANAGER

Connected.

SQL> ALTER PROFILE oltp LIMIT

2 LOGICAL_READS_PER_SESSION unlimited

3 LOGICAL_READS_PER_CALL unlimited

4 CONNECT_TIME unlimited

5 IDLE_TIME unlimited

6 CPU_PER_SESSION unlimited

7 SESSIONS_PER_USER unlimited;

Profile altered.

SQL> ALTER SYSTEM SET RESOURCE_LIMIT=FALSE;

System altered.