Oracle-OCP-SQL-13-Controlling-User-Access

Copyright©OracleCorporation,2001.Allrightsreserved.ControllingUserAccess13-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Createusers•Createrolestoeasesetupandmaintenanceofthesecuritymodel•UsetheGRANTandREVOKEstatementstograntandrevokeobjectprivileges•Createandaccessdatabaselinks13-3Copyright©OracleCorporation,2001.Allrightsreserved.ControllingUserAccessDatabaseadministratorUsersUsernameandpasswordPrivileges13-4Copyright©OracleCorporation,2001.Allrightsreserved.Privileges•Databasesecurity:–Systemsecurity–Datasecurity•Systemprivileges:Gainingaccesstothedatabase•Objectprivileges:Manipulatingthecontentofthedatabaseobjects•Schemas:Collectionsofobjects,suchastables,views,andsequences13-5Copyright©OracleCorporation,2001.Allrightsreserved.SystemPrivileges•Morethan100privilegesareavailable.•Thedatabaseadministratorhashigh-levelsystemprivilegesfortaskssuchas:–Creatingnewusers–Removingusers–Removingtables–Backinguptables13-6Copyright©OracleCorporation,2001.Allrightsreserved.CreatingUsersTheDBAcreatesusersbyusingtheCREATEUSERstatement.CREATEUSERscottIDENTIFIEDBYtiger;Usercreated.CREATEUSERuserIDENTIFIEDBYpassword;13-7Copyright©OracleCorporation,2001.Allrightsreserved.UserSystemPrivileges•Onceauseriscreated,theDBAcangrantspecificsystemprivilegestoauser.•Anapplicationdeveloper,forexample,mayhavethefollowingsystemprivileges:–CREATESESSION–CREATETABLE–CREATESEQUENCE–CREATEVIEW–CREATEPROCEDUREGRANTprivilege[,privilege...]TOuser[,user|role,PUBLIC...];13-8Copyright©OracleCorporation,2001.Allrightsreserved.GrantingSystemPrivilegesTheDBAcangrantauserspecificsystemprivileges.GRANTcreatesession,createtable,createsequence,createviewTOscott;Grantsucceeded.13-9Copyright©OracleCorporation,2001.Allrightsreserved.WhatisaRole?AllocatingprivilegeswithoutaroleAllocatingprivilegeswitharolePrivilegesUsersManager13-10Copyright©OracleCorporation,2001.Allrightsreserved.CreatingandGrantingPrivilegestoaRoleCREATEROLEmanager;Rolecreated.GRANTcreatetable,createviewTOmanager;Grantsucceeded.GRANTmanagerTODEHAAN,KOCHHAR;Grantsucceeded.•Createarole•Grantprivilegestoarole•Grantaroletousers13-11Copyright©OracleCorporation,2001.Allrightsreserved.ChangingYourPassword•TheDBAcreatesyouruseraccountandinitializesyourpassword.•YoucanchangeyourpasswordbyusingtheALTERUSERstatement.ALTERUSERscottIDENTIFIEDBYlion;Useraltered.13-12Copyright©OracleCorporation,2001.Allrightsreserved.ObjectPrivilegeTableViewSequenceProcedureALTERDELETEEXECUTEINDEXINSERTREFERENCESSELECTUPDATEObjectPrivileges13-13Copyright©OracleCorporation,2001.Allrightsreserved.ObjectPrivileges•Objectprivilegesvaryfromobjecttoobject.•Anownerhasalltheprivilegesontheobject.•Anownercangivespecificprivilegesonthatowner’sobject.GRANTobject_priv[(columns)]ONobjectTO{user|role|PUBLIC}[WITHGRANTOPTION];13-14Copyright©OracleCorporation,2001.Allrightsreserved.GrantingObjectPrivileges•GrantqueryprivilegesontheEMPLOYEEStable.•Grantprivilegestoupdatespecificcolumnstousersandroles.GRANTselectONemployeesTOsue,rich;Grantsucceeded.GRANTupdate(department_name,location_id)ONdepartmentsTOscott,manager;Grantsucceeded.13-15Copyright©OracleCorporation,2001.Allrightsreserved.UsingtheWITHGRANTOPTIONandPUBLICKeywords•Giveauserauthoritytopassalongprivileges.•AllowallusersonthesystemtoquerydatafromAlice’sDEPARTMENTStable.GRANTselect,insertONdepartmentsTOscottWITHGRANTOPTION;Grantsucceeded.GRANTselectONalice.departmentsTOPUBLIC;Grantsucceeded.13-16Copyright©OracleCorporation,2001.Allrightsreserved.ConfirmingPrivilegesGrantedDataDictionaryViewDescriptionROLE_SYS_PRIVSSystemprivilegesgrantedtorolesROLE_TAB_PRIVSTableprivilegesgrantedtorolesUSER_ROLE_PRIVSRolesaccessiblebytheuserUSER_TAB_PRIVS_MADEObjectprivilegesgrantedontheuser’sobjectsUSER_TAB_PRIVS_RECDObjectprivilegesgrantedtotheuserUSER_COL_PRIVS_MADEObjectprivilegesgrantedonthecolumnsoftheuser’sobjectsUSER_COL_PRIVS_RECDObjectprivilegesgrantedtotheuseronspecificcolumnsUSER_SYS_PRIVSListssystemprivilegesgrantedtotheuser13-17Copyright©OracleCorporation,2001.Allrightsreserved.HowtoRevokeObjectPrivileges•YouusetheREVOKEstatementtorevokeprivilegesgrantedtootherusers.•PrivilegesgrantedtoothersthroughtheWITHGRANTOPTIONclausearealsorevoked.REVOKE{privilege[,privilege...]|ALL}ONobjectFROM{user[,user...]|role|PUBLIC}[CASCADECONSTRAINTS];13-18Copyright©OracleCorporation,2001.Allrightsreserved.RevokingObjectPrivilegesAsuserAlice,revoketheSELECTandINSERTprivilegesgiventouserScottontheDEPARTMENTStable.REVOKEselect,insertONdepartmentsFROMscott;Revokesucceeded.13-19Copyright©OracleCorporation,2001.Allrightsreserved.DatabaseLinksAdatabaselinkconnectionallowslocaluserstoaccessdataonaremotedatabase.LocalRemoteSELECT*FROMemp@HQ_ACME.COM;HQ_ACME.COMdatabaseEMPTable13-20Copyright©OracleCorporation,2001.Allrightsreserved.DatabaseLinks•Createthedatabaselink.•WriteSQLstatementsthatusethedatabaselink.CREATEPUBLICDATABASELINKhq.acme.comUSING'sales';Databaselinkcreated.SELECT*[e-mail];13-21Copyright©Orac