Oracle英文版培训课件之Security：L10-AuthorizationMethods

AuthorizationMethodsObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:ImplementrolesImplementthesecuringofobjectsthroughproceduresDescribehowsecureapplicationrolesworkManagerolesandusersbyusingsecureapplicationrolesAuthorizationAuthorizationdeterminestheprivilegesthattheuserhasinthedatabase.Userprivilegesaresetinthedatabaseby:SystemprivilegesObjectprivilegesRolesTherearetwotypesofuserprivileges:System:EnablesuserstoperformparticularactionsinthedatabaseObject:EnablesuserstoaccessandmanipulateaspecificobjectPrivilegesUsersPrivilegesRolesHR_CLERKHR_MGRRolesNeenaGirardVanceDeleteemployeesSelectemployeesUpdateemployeesInsertemployeesEasierprivilegemanagementDynamicprivilegemanagementSelectiveavailabilityofprivilegesCanbegrantedthroughtheoperatingsystemBenefitsofRolesPredefinedRolesCREATEANYJOB,CREATEJOB,EXECUTEANYCLASS,EXECUTEANYPROGRAM,MANAGESCHEDULERSCHEDULER_ADMINNosystemprivileges,butover1600objectprivilegesonthedatadictionarySELECT_CATALOG_ROLEMostsystemprivileges,severalotherroles.Donotgranttononadministrators.DBACREATETABLE,CREATEPROCEDURE,CREATESEQUENCE,CREATETRIGGER,CREATETYPE,CREATECLUSTER,CREATEINDEXTYPE,CREATEOPERATORRESOURCECREATESESSION,CREATETABLE,CREATEVIEW,CREATESYNONYM,CREATESEQUENCE,CREATEDATABASELINK,CREATECLUSTER,ALTERSESSIONCONNECTUsingProxyAuthenticationwithRolesSpecifyrolesthattheproxyisallowedtoactivate:Preventanyrolesfrombeingactivatedbytheproxy:ALTERUSERphallGRANTCONNECTTHROUGHhruser WITHROLEhr_clerk;ALTERUSERphallGRANTCONNECTTHROUGHappsrv WITHNOROLES;SecuringObjectswithProceduresObjectaccesscanbestrictlycontrolledthroughprocedures.Theobjectownercreatesproceduresandfunctionstoaccesstheobject.UsersaregrantedtheEXECUTEprivilegesonprogramunits.Usersdonothavedirectaccesstoobjects.SecureApplicationRole

Thesecureapplicationrolesolvestheproblemofpreventingunauthorizedaccesstodatathroughotherclientprograms.Itisbetterthanthepreviousmechanismwithahiddenpassword.ItusesthesameSYS_CONTEXTmechanismasVirtualPrivateDatabase.Enablingaroleischeckedthroughapackage,

andnotapassword.ImplementingaSecureApplicationRole1. Createtherole.2. Createthepackagethatsetstherole:a. Createthepackagespecification.b. Createthepackagebody.3. Granttheexecuteprivilegeonthepackage.4. Writetheapplicationservercodethatsetstherole.CREATEROLEoe_sales_repIDENTIFIEDUSINGsecure.oe_roles;Step1:CreatetheRoleTheCREATE

ROLEcommandidentifiesthepackagethatsetstherole.Thepackagedoesnotneedtoexist.Example:CREATEORREPLACEPACKAGEoe_rolesAUTHIDCURRENT_USERISPROCEDUREset_sales_rep_role;END;/Step2.1:CreatethePackageSpecificationTheOE_ROLESpackageisreferencedintheCREATE

ROLEcommand.TheAUTHID

CURRENT_USERclauseisrequiredtoproperlysettherole.Example:...SELECTidINTOv_idFROMoe.app_rolesWHEREusername=sys_context('userenv','current_user')ANDrole='SALES_REP'ANDip_address=sys_context('userenv','ip_address');dbms_session.set_role('oe_sales_rep');...Step2.2:CreatethePackageBodyGRANTexecuteONoe_rolesTOappsrv;Step3:GranttheEXECUTEPrivilege

onthePackageTheapplicationserverconnectsastheappsrvuser.Itsetstheroleafteritstartstheuser’ssession.Example:Step4:WritetheApplicationServer

CodeThatSetstheRoleWhenstarting,theapplicationserver:ConnectsastheAPPSRVuserCreatesaconnectionpoolWhenstartingasessionforauser,theapplicationserver:GetsaconnectionfromthepoolStartsasessionfortheuserSetstheuser’sroleSettheuser’srolebyusing:secure.oe_roles.set_sales_rep_role;DataDictionaryViewsSQL>SELECT*2FROMdba_application_roles3WHEREROLE='OE_SALES_REP';ROLESCHEMAPACKAGE----------------------------OE_SALES_REPSECUREOE_ROLESSQL>Sum