Scribd
Upload
63 views2 pages

SQL Database User and Role Management

The document outlines steps to create database objects including a tablespace, users, roles, and tables. It instructs to: 1) Create a tablespace named SALES_TB and specify properties like size and autoextend settings 2) Create users SALES_USER1 and SALES_USER2 assigning them the SALES_TB tablespace 3) Grant SALES_USER1 privileges to create a table with a partition and index 4) Create a role SL_ROLE with privileges to access SALES_USER1's SALES table and grant it to SALES_USER2 5) Revoke DELETE privileges from SL_ROLE and verify SALES_USER2 can no longer delete

Uploaded by

mishra_123
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
63 views2 pages

SQL Database User and Role Management

The document outlines steps to create database objects including a tablespace, users, roles, and tables. It instructs to: 1) Create a tablespace named SALES_TB and specify properties like size and autoextend settings 2) Create users SALES_USER1 and SALES_USER2 assigning them the SALES_TB tablespace 3) Grant SALES_USER1 privileges to create a table with a partition and index 4) Create a role SL_ROLE with privileges to access SALES_USER1's SALES table and grant it to SALES_USER2 5) Revoke DELETE privileges from SL_ROLE and verify SALES_USER2 can no longer delete

Uploaded by

mishra_123
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Project Work

1) Write a SQL statement to find the name of the database.

2) Write a SQL statement to find the tablespaces present in the database.

3) Create a tablespace named SALES_TB with below properties


 No Compress
 Extent Management Local
 Size 200 mega byes
 Autoextend
 Maximum size 2000 mega bytes

4) Create a new user SALES_USER1 with below properties

 Assign the default tablespace as SALES_TB


 Assign the default temporary tablespace as TEMP1
 Grant necessary roles so that user can connect to the session
 Grant privileges so that user can create tables, insert, delete, update data in his own
schema

5) Login into the user SALES_USER1 and perform the below tasks

Create a table SALES with columns ORDER_NUMBER, ORDER_DATE, PRODUCT_ID, ITEM_CNT,


ORDER_AMOUNT. Make sure ORDER_NUMBER, PRODUCT_ID is the primary key and create a
RANGE PARTITION on the column ORDER_DATE.

Create an INDEX on the table PRODUCT_ID.

Insert 4 rows of data into the table SALES.

6) Log out of the user SALES_USER1 and login to your DBA account

7) Write a SQL statement to find out all the ROLES in the database.

8) Create a new role named SL_ROLE with INSERT/UPDATE/DELETE/SELECT privileges on table


SALES_USER1.SALES.
9) Create a new user SALES_USER2 with below properties

 Assign the default tablespace as SALES_TB


 Assign the default temporary tablespace as TEMP1
 Grant necessary roles so that user can connect to the session
 Grant role SL_ROLE

10) Now, login to the user SALES_USER2 and test the permissions by inserting, updating, deleting,
selecting data form the table SALES_USER1.SALES.

11) Log out of the user SALES_USER2 and login into the DBA account

12) Revoke the DELETE privileges from the role SL_ROLE

13) Log back into the user SALES_USER2 and see if the delete privilege has been revoked.

14) Log off SALES_USER2, connect to the DBA account and now follow the below steps

 Revoke the role SL_ROLE from the user SALES_USER2


 Drop the user SALES_USER2

15) Add a new data file to the tablespace SALES_TB.

16) Write a SQL statement to find the current sessions running in the database;

You might also like