Lecture Notes Unit4 Chapter13 users , roles and privileges

Description:
Welcome to the comprehensive guide on Relational Database Management System (RDBMS) concepts, tailored for final year B.Sc. Computer Science students affiliated with Alagappa University. This document covers fundamental principles and advanced topics in RDBMS, offering a structured approach to understanding databases in the context of modern computing. PDF content is prepared from the text book Learn Oracle 8I by JOSE A RAMALHO.

Key Topics Covered:

Main Topic : USERS, Roles and Privileges
In Oracle databases, users are individuals or applications that interact with the database. Each user is assigned specific roles, which are collections of privileges that define their access levels and capabilities. Privileges are permissions granted to users or roles, allowing actions like creating tables, executing procedures, or querying data. Properly managing users, roles, and privileges is essential for maintaining security and ensuring that users have appropriate access to database resources, thus supporting effective data management and integrity within the Oracle environment.
Sub-Topic :
Definition of User, User Creation Commands, Grant Command, Deleting a user, Privileges, System privileges and object privileges, Grant Object Privileges, Viewing a users, Revoke Object Privileges, Creation of Role, Granting privileges and roles to role, View the roles of a user , Deleting a role
Target Audience:

Final year B.Sc. Computer Science students at Alagappa University seeking a solid foundation in RDBMS principles for academic and practical applications.

URL for previous slides
chapter 8,9 and 10 : https://www.slideshare.net/slideshow/lecture_notes_unit4_chapter_8_9_10_rdbms-for-the-students-affiliated-by-alagappa-university/270123800
Chapter 11 Sequence: https://www.slideshare.net/slideshow/sequnces-lecture_notes_unit4_chapter11_sequence/270134792
Chapter 12 View : https://www.slideshare.net/slideshow/rdbms-lecture-notes-unit4-chapter12-view/270199683

About the Author:
Dr. S. Murugan is Associate Professor at Alagappa Government Arts College, Karaikudi. With 23 years of teaching experience in the field of Computer Science, Dr. S. Murugan has a passion for simplifying complex concepts in database management.

Disclaimer:
This document is intended for educational purposes only. The content presented here reflects the author’s understanding in the field of RDBMS as of 2024.

1. RDBMS -Unit IV
Chapter 13
Users, Privileges and Roles
Prepared By
Dr. S.Murugan, Associate Professor
Department of Computer Science,
AlagappaGovernment Arts College, Karaikudi.
(Affiliated by AlagappaUniversity)
Mailid: [email protected]
Reference Book:
LEARN ORACLE 8i, JOSE A RAMALHO

2. USER
➢Theregisteredpersoninanoracleiscalleduser.
➢Each registered user has an access password.
➢individual privileges or roles assigned to every user.

3. The CREATE USER Command
➢The CREATE USER command is responsible for the creation of
new users.
➢SYNTAX
CREATE USER USERNAME IDENTIFIED BY PASSWORD
➢Arguments:
➢user
The name of the user to be created.
➢IDENTIFIED
Indicates how Oracle validates the user.
➢BY password
Requires the user to specify this password for the
connection.

4. Creating a New User
➢Tocreatenewuserorassignprivileges,youmust
connecttotheadministratorusingtheSystemname
andthemanagerkeyword.
Connectsystem/manager
➢First,let’screateausercalledcs8001,withthe
passwordComputer
➢SQL>CREATEUSERcs8001IDENTIFIEDBY
computer;
Usercreated.

5. The GRANT Command
➢Thegrantcommandisusedtoassignprivilegeorroles
totheuser.
➢Syntax
GRANTsystemprivileges/roleTOuser/role
Arguments:
privilege
The name of the privilege to be assigned.
user/role
The name of the user or role that is receiving the
privilege.

6. The GRANT Command Example
➢Let’sconnectasSystemandassigntheRESOURCEprivilege,
whichallowsaccesstothedatabaseandthecreationoftables,
sequences,procedures,triggers,indexes,andclusters.
➢SQL>connectsystem/manager;
Connected.
➢SQL>grantRESOURCEtocs8001;
Grantoperationsuccessful.
➢Nowtheusercs8001abletocreatetable,procedure,etc.
➢SQL>connectcs8001/computer;
Connected.
➢SQL>createtablex(anumber);
Tablecreated.

7. Deleting a User
➢YoucanremoveadatabaseuserwiththeDROPUSER
command.
➢Thiscommandremovesboththeuserandallthe
objectscontainedinthisuser’sschema.
Syntax:
DROP USER user_name
In the next example user cs8001 is removed:
SQL> connect system/manager
Connected.
SQL> drop user cs8001 cascade;
Users eliminated.
To display all users
SQL> select * from all_users;

8. Privileges
➢Aprivilegeisanauthorizationgiventotheuserto
accessandmanipulateadatabaseobjectinacertain
way.
➢Forexample,oneusercanbeassignedtheprivilegeto
selecttablesbutnotchangethem.
➢Therearetwotypesofprivileges:systemprivileges
andobjectprivileges.

9. System Privileges
➢Asystemprivilegeistherightorpermissiontoexecute
certaindatabaseactionsinaspecifictypeofdatabase
object.
➢Therearemorethan70typesofprivilegesassociated
totheactionsofadatabase.
➢Thenameoftheprivilegeissimilartothenameofthe
actionitexecutes.
➢Forexample,aprivilegecalledALTERTABLEgrants
ausertherighttomodifyatable.

10. Object Privileges
➢Anobjectprivilegeistherighttoperformcertain
actionsinaspecificobject,suchastherighttoinclude
arowinatable.

11. Object Privileges
➢AvariationoftheGRANTcommandallowsyouto
assignobjectprivilegestoauserorrole.
➢Theprivilegesassignedcanbe:SELECT,ALTER,
DELETE, EXECUTE, INSERT, INDEX,
REFERENCE,andUPDATE.
➢Thefollowingobjectscanbeassignedprivileges:
tables,views,sequences,snapshots,andsynonyms.
➢Anyuserwhohasauthoritycangrantprivilegeson
tables.

12. Object Privileges

13. Object Privileges
Options:
SELECT:Toselectdatainatableorview.
INSERT:Toinsertrowsinatableorview.
DELETE:Toeliminaterowsinatableorview.
UPDATE:Toupdateatableand,optionally,updateonly
thespecifiedcolumns.
INDEX:Tocreateoreliminatetheindexesofatable.
ALTER:Tomodifyatable.
ALL:Toperformalltheaboveprivileges.

14. Object Privileges
ALL:Toperformalltheaboveprivileges.
table_name:Namesofexistingtablesinthedatabase.
view_name:Namesofexistingviewsinthedatabase.
column_name:Thisisoptionalanddeterminesthe
columnsoftablesorviewsspecifiedintheONclause.
Thenamesofthecolumnsmustnotbequalified.
USER:Nameoftheusertowhomtheprivilegeis
assigned.

15. Object Privileges
PUBLIC:Meansthatallthecurrentandnewusershave
theprivilegesspecifiedforthetableorview.
WITHADMINOPTION:
Allowstheuserorrolethatreceivesthe
privilegetograntittootherusers,modifyit,oreven
deleteit.

16. Grant Object Privileges
System/manager Cs8001/computer
Connect system/manager
Connected.
Connect cs8001/computer
Connected.
grant select on empto cs8001
GRANT operation successful
SQL> select enamefrom
system.emp;
SQL> grant update (DNAME) on
deptto cs8001;
SQL> update system.dept
set dname=’SALES FORCE’
where deptno=30;
1 row updated.
SQL> update system.dept
set loc=’MIAMI’
where deptno=30;
update system.dept
*
ERROR on row 1:
ORA-01031: insufficient

17. Viewing Users
➢Youcanuseoneofthetablesofthedatadictionaryto
checkthedatabaseusers.Thedictionaryviewthat
maintainsthislistisALL_USERS.
➢SQL>select*fromall_users;

18. Revoke Object previleges
System/manager Cs8001/computer
Connect system/manager
Connected.
Connect cs8001/computer
Connected.
revoke select on empfrom
cs8001
Revoke successful
SQL> select enamefrom
system.emp;
It will displayserror
SQL> revoke update
(DNAME) on deptto
cs8001;
SQL> update system.dept
set dname=’SALES FORCE’
where deptno=30;
It will displayserror.

19. Viewing Users
➢Youcanuseoneofthetablesofthedatadictionaryto
checkthedatabaseusers.Thedictionaryviewthat
maintainsthislistisALL_USERS.
➢SQL>select*fromall_users;

20. Roles
Aroleisagroupofprivilegesassignedtoaname.Instead
ofgrantingeightprivilegestoauser,youcancreatearole
thatisassignedthoseeightprivileges,andthenassign
thatroletotheuser.

21. Creating a Role
➢Whenyoucreateanewrole,youcanassignitsome
privilegesorotherrolesthatwerepreviouslycreated.
➢TheSQLcommandusedtocreatearoleisCREATE
ROLE:
➢Inthefollowingexample,weconnectas
system/managertocreatearolecalledBasic1:
SQL> CREATE ROLE basic1;
Role created.

22. Granting Privileges and Roles to a Role
System/manager Arnold/terminator
Connect system/manager
Connected.
Create user arnoldidentified by
terminator
Connect arnold/terminator
Displayserror messages.
This example creates basic1 role.
SQL> CREATE ROLE basic1;
This example grants the
RESOURCE role to Basic1:
SQL> grant connect, resource to
basic1;
Grant operation successful.

23. Granting Privileges and Roles to a Role
System/manager arnold/terminator
SQL> grant basic1 to arnold;
Grant operation successful.
SQL> connect arnold/terminator
Connected.

24. Viewing the Roles of a User
➢TheUSER_ROLE_PRIVS commanddisplaysthe
rolesassignedtothecurrentuser:
➢SQL>select*fromuser_role_privs;
➢USERNAMEGRANTED_ROLE ADMDEFOS_
-----------------------------
ARNOLDBASIC1NOYESNO

25. Deleting a Role
➢Thecommandresponsibleforthedeletionofaroleis
DROPROLE.
➢Syntax:
DROPROLErole.
➢ThenextexampleremovestheBasic1role.
SQL>connectsystem/manager
Connected.
SQL>droprolebasic1;
Roleeliminated.
➢WhenwetrytoconnectasuserArnold,anerrorwill
occur,becausehehasnoassociatedprivilegeorrole: