Database Security -Exercise and Case study Solution

Created Feb. 7, 2024 by Girija Narasimhan

UNIT 2 - User Profile Exercise Solution

1. Create Profile name as "HR_Manager_Prof", in this profile apply the following limits

- Password validity is 3 months

- Fifteen days before the password validity expiry the user get proper information.

- User is allowed to try three incorrect password attempts

- If incorrect password attempt exist then it will lock the user account two days

Ans:

SQL> create profile HR_Manager_Prof limit
2     PASSWORD_LIFE_TIME 90
3     PASSWORD_GRACE_TIME 15
4     FAILED_LOGIN_ATTEMPTS 3
5     PASSWORD_LOCK_TIME 2;

Profile created.

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

2. Create the user Induja and assign the profile the HR_Manager_Prof to her.

Create the user Induja and assign the profile the HR_Manager_Prof to her. Display the assigned profile to user Induja.

Ans:

create user Induja IDENTIFIED by Indu;

alter user induja profile HR_Manager_Prof;

profile HR_Manager_Prof;

grant create session to Induja ;

grant create session to induja identified by indu profile HR_Manager_prof;

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

3. Modify the HR_Manager_Prof, the user is allow to work two concurrent session.

- User is allowed for only 60 minutes per session

- Change the user lock time is 6 hours

Ans:

Alter profile HR_Manager_Prof limit

sessions_per_user 2

connect_time 60

password_lock_time 6/24;

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

4. Display password created date and expired date information

Ans:

SQL> select created,expiry_date from dba_users where username = 'INDUJA';

CREATED EXPIRY_DATE

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

08-MAY-17 06-AUG-17

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

5. Display the information when user induja recently changed her password with date and time

Ans:

Alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

SELECT PTIME FROM SYS.USER$ WHERE NAME='INDUJA';

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

6. Change the user password should not be case sensitive.

Ans:

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

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

7. Prohibit the user Induja to access database? or Change the user Induja login the session as inactive.

Ans:

Alter user induja account lock;

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

9. Release access denied by user Induja or change the user induja session is active or permit the user induja to connect the database.

Ans:

alter user induja account unlock;

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

10. Don’t allow if user password and username is identical Using pwdverify password verification function and Include pwdverify function into profile HR_Manager_prof. Assign HR_manager_prof to user Induja.

Ans:

CREATE OR REPLACE FUNCTION pwdverify

(username varchar2,

password varchar2,

old_password varchar2)

RETURN boolean IS

n boolean;

m integer;

differ integer;

isdigit boolean;

ischar boolean;

ispunct boolean;

db_name varchar2(40);

digitarray varchar2(20);

punctarray varchar2(25);

chararray varchar2(52);

i_char varchar2(10);

simple_password varchar2(10);

reverse_user varchar2(32);

BEGIN

digitarray:= '0123456789';

chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

-- Check if the password is same as the username or username(1-100)

IF NLS_LOWER(password) = NLS_LOWER(username) THEN

raise_application_error(-20002, 'Password same as or similar to user');

END IF;

FOR i IN 1..100 LOOP

i_char := to_char(i);

if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN

raise_application_error(-20005, 'Password same as or similar to user name ');

END IF;

END LOOP;

RETURN(TRUE);

END;

alter profile HR_Manager_Prof limit
password_verify_function pwdverify;

Alter user induja profile HR_Manager_prof;

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

11. User never allow to reuse the same password

Ans:

alter profile HR_Manager_Prof limit

password_reuse_time 2

password_reuse_max unlimited;

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

12. Remove HR_Manager_Prof profile from user Induja

Ans:

ALTER USER INDUJA PROFILE DEFAULT;

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

13. Whenever the user Induja login her session first attempts, the user must type the new password.

Ans:

ALTER USER INDUJA PASSWORD EXPIRE;

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

UNIT 2 - User Profile Exercise Solution

Standards