Database Security -Exercise and Case study Solution

Created Feb. 7, 2024 by Girija Narasimhan

Unit 1 - Authentication Exercise Solution

1.        Create default permanent tablespace as “Lak_ts” using “lak.dbf” file. While creating user Lakshmi do the following:

                           - Lakshmi password is Lak01020

                          - Assign Lak_ts tablespace to user Lakshmi

                         - Assign unlimited quota using Lak_ts tablespace.


Method 1: first create table space Lak_ts and then create user to assign Lak_ts


create tablespace Lak_ts

Datafile 'c\temp\lak_ts.dbf' size 10M

Extent Management Local

Segment space Management  Auto;

SQL> create user lakshmi identified by lak01020

  2  default tablespace lak_ts

  3  quota unlimited on lak_ts;

 User created.

grant create session to Lakshmi;


Method2: Create user Lakshmi, then it automatically assign USERS is default tablespce after creating lak_ts tablespace. Assign using ALTER USER, to assign lak_ts tablespece to user laskhmi



create user Lakshmi identified by lak01020;

create tablespace Lak_ts

Datafile 'c\temp\lak_ts.dbf' size 10M

Extent Management Local

Segment space Management  Auto;

alter user Lakshmi default tablespace lak_ts;

grant create session to Lakshmi;

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

2.      Revoke the quota value of the user Lakshmi.

SQL> alter user lakshmi identified by lak01020

  2  quota 0 on lak_ts;

User altered.

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

3.      Change the password of the user Lakshmi to Lakshmi1546

SQL> alter user lakshmi identified by lakshmi1546;

User altered.

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

4.       Display default permanent tablespace of the user Lakshmi

Ans:

select default_tablespace from dba_users where username='LAKSHMI';

DEFAULT_TABLESPACE

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

LAK_TS

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

5.      Display temporary tablespace of the user Lakshmi.

Ans:

SQL> select temporary_tablespace from dba_users where username='LAKSHMI';

 TEMPORARY_TABLESPACE

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

TEMP

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

6.     Change the default permanent table space as "USERS" to user LAKSHMI

Ans:

alter user lakshmi default tablespace users;

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

7.     Login Lakshmi session

Ans:

sql>conn Lakshmi/lakshmi1546

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

8.      Remove the user Lakshmi from the database. 

Ans:

SQL> drop user lakshmi;

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

9.  Display the OS_AUTHENT_PREFIX value.

Ans:

SQL> show parameter OS_AUTHENT_PREFIX

NAME                                 TYPE        VALUE

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

os_authent_prefix                    string      OPS$

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

10. Change the OS_AUTHENT_PREFIX value as NULL. Display the OS_AUTHENT_PREFIX value.

Ans:

SQL> alter system set OS_AUTHENT_PREFIX='' scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1375792 bytes

Variable Size             314573264 bytes

Database Buffers          213909504 bytes

Redo Buffers                5804032 bytes

Database opened.

SQL> show parameter OS_AUTHENT_PREFIX

NAME                                 TYPE        VALUE

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

os_authent_prefix                    string


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

11. Create user lakshmi as external user with  OS_AUTHENCATE_PREFIX value.

Ans:

create user ops$lakshmi identified bylakshmi123;

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

12. Create OS authenticated user as Lakshmi456

Ans:

create user lakshmi456 identified externally;

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

13.  Assign proxy user proxy_lakshmi to user lakshmi.

Ans:

create user proxy_lakshmi identified by proxy123;

 grant create session to proxy_lakshmi;

 alter user lakshmi grant connect through proxy_lakshmi;

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

14.  Display the proxy_user, client and flag information.

Ans:

Select * from Proxy_users;

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

15.  Login the proxy_Lakshmi.

Ans:

SQL> conn proxy_lakshmi[lakshmi]

Enter password:proxy123

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

16.  Assign password authentication to proxy_Lakshmi.

Ans:

Alter user lakshmi grant connect through proxy_lakshmi authenticated using password;

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

17.  Remove the proxy_Lakshmi connection from the user Lakshmi.

Ans:

Alter user lakshmi revoke connect through proxy_lakshmi;

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

External User - Exercise

1.Change OS_AUTHENT_PREFIX value has Null and REMOTE_OS_AUTHENT value is TRUE.

Check both the parameter values are changed. Create user lakshmi as external user and assign connect privilege to user Lakshmi.

Ans:

alter system set REMOTE_OS_AUTHENT=TRUE scope=spfile;

alter system set OS_AUTHENT_PREFIX='' scope=spfile;

Shutdown immediate;

startup;  

show parameter OS_AUTHENT_PREFIX

show parameter REMOTE_OS_AUTHENT

create user Lakshmi identified externally;

GRANT CONNECT TO  Lakshmi

Exercise 2:

Change OS_AUTHENT_PREFIX value has OPS$ and REMOTE_OS_AUTHENT value is TRUE.

Check both the parameter values are changed. Create user ops$ram as external user and assign connect privilege to user ops$ram


Ans:

alter system set REMOTE_OS_AUTHENT=TRUE scope=spfile;

alter system set OS_AUTHENT_PREFIX='OPS$' scope=spfile;

Shutdown immediate;

Startup;  

show parameter OS_AUTHENT_PREFIX

show parameter REMOTE_OS_AUTHENT

create user ops$ramla identified externally;

GRANT CONNECT TO ops$ramla

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

--




Return to top