Oracle英文版培训课件之Security：L12-fgac

UsingFine-GrainedAccessControlObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:Describehowfine-grainedaccesscontrol(FGAC)andtheVirtualPrivateDatabase(VPD)workImplementFGACortheVPDGrouppoliciesFine-GrainedAccessControl:OverviewLimitsrowaccessUsesapredicateIsreturnedfromafunctionIsassociatedwithatableorviewIsautomaticallyenforcedSELECT*FROMordersWHERE

sales_rep_id

=

406;ORDERSSELECT*FROMorders;SELECT*FROMordersWHEREsales_rep_id=152;SELECT*FROMorders;BenefitsSecurity:FGACisalwaysapplied.Simplicity:DefineonceIndependentofapplicationFlexibility:ApplydifferentaccesstodifferentSQLstatements.Grouppolicies.Highperformance:StaticanddynamicpoliciesActivepoliciesstoredinmemoryVirtualPrivateDatabaseAVirtualPrivateDatabase(VPD)combinesanapplicationcontextandFGACto:EnforcebusinessrulestolimitrowaccessUseasecureapplicationcontexttoprovidehighperformanceresolutionofuserattributes.ExamplesoftheVirtualPrivateDatabaseTheVPDallowsmultiplepoliciesonthesametable:Customerexample:Contextattribute:cust_idPredicate:customer_id=sys_context('oeapp','cust_id')Salesrepresentativeexample:Contextattribute:emp_idPredicate:sales_rep_id=sys_context('oeapp','emp_id')HowFine-GrainedAccessControlWorks1. Theuseraccessesatableorviewwithapolicy.2. Thedataservercallsthepolicyfunction.3. Thepolicyfunctionreturnsapredicate.4. Thedataserveraddsthepredicatetothestatement.5. Thedataserverexecutesthemodifiedstatement.becomesSELECT*FROMordersWHEREcustomer_id=sys_context('oeapp','cust_id');SELECT*FROMorders;ToolsThePL/SQLproceduresandpackages,suchas:SYS_CONTEXTreturnscontextattributesDBMS_SESSIONmanages:-Contexts-GlobalidentifiersDBMS_RLSmanages:-Contexts-Policies-PolicygroupsOraclePolicyManagerisaGUIthat:UsesDBMS_RLSProvidessecuritypolicyadministrationManagestheVPDandOracleLabelSecurityOraclePolicyManagerDBMS_RLSAssociatepolicieswithtablesorviews:ADD_POLICYADD_GROUPED_POLICYEnableanddisablepolicies:ENABLE_POLICYENABLE_GROUPED_POLICYRefreshpolicies:REFRESH_POLICYGrouppolicies:CREATE_POLICY_GROUPManagedrivingcontexts:ADD_POLICY_CONTEXT

DROP_POLICYDROP_GROUPED_POLICY

DISABLE_GROUPED_POLICY

REFRESH_GROUPED_POLICY

DELETE_POLICY_GROUP

DROP_POLICY_CONTEXTColumn-LevelVPDStatementsarenotalwaysrewritten.Example:ApolicyprotectstheSALARYandtheCOMMISSION_PCTcolumnsoftheEMPLOYEEStable.TheFGACis:Notenforcedforthisquery:Enforcedforthesequeries:SQL>SELECTlast_name,salary2FROMemployees;SQL>SELECTlast_nameFROMemployees;SQL>SELECT*FROMemployees;Column-LevelVPD:ExampleBEGIN dbms_rls.add_policy(object_schema=>'hr',object_name=>'employees',policy_name=>'hr_policy',function_schema=>'hr',policy_function=>'hrsec',statement_types=>'select,insert',

sec_relevant_cols=>'salary,commission_pct'

sec_relevant_col_opts=>dbms_rls.ALL_ROWS);END;/PolicyTypes:OverviewThepolicytypesspecifyhowoftenapolicyfunctionshouldbereevaluated.Thetypesare:DynamicDBMS_RLS.DYNAMIC(Default)StaticDBMS_RLS.STATICDBMS_RLS.SHARED_STATICContextsensitiveDBMS_RLS.CONTEXT_SENSITIVEDBMS_RLS.SHARED_CONTEXT_SENSITIVEShared:SharedpoliciesallowyoutosharethesamepolicyfunctionwithdifferentobjectsStaticPoliciesThepolicyfunctionisevaluatedonce.Theresultingpolicypredicateiscachedinmemory.Everystatementaccessingprotectedobjectsusesthesamepolicypredicate.exec dbms_rls.add_policy(object_schema=>'hr',object_name=>'employees',-policy_name=>'hr_policy',-function_schema=>'hr',policy_function=>'hrsec',-statement_types=>'select,insert',-policy_type=>dbms_rls.static,-sec_relevant_cols

=>'salary,commission_pct');Context-SensitivePoliciesThepolicyfunctionisevaluatedforeachsessionwhen:ThestatementisfirstparsedThereisarelatedchangeinthelocalapplicationcontextTheresultingpolicypredicateiscachedintheuser’ssessionmemory.exec dbms_rls.add_policy(object_schema=>'hr',object_name=>'employees2',-policy_name=>'hr_policy2',-function_schema=>'hr',policy_function=>'hrsec2',-statement_types=>'select,insert',-policy_type=>dbms_rls.context_sensitive,-sec_relevant_cols

=>'salary,commission_pct');SharingPolicyFunctionsdepartmentscountriesemp_vemployeesSamepolicy

functionExceptionstoFGACPoliciesPoliciesarenotenforcedfor:DIRECTpathexportUserswithDBAprivileges(AS

SYSDBA)UsersgrantedEXEMPT_ACCESS_POLICYImplementingaVPD1. CreateaPL/SQLpackagethatsetsthecontext.2. Createanapplicationcontext:Isassociatedwiththepackagecreatedinstep1Preventsthecontextfrombeingchanged3. Writethefunctionthatcreatesapredicate:Usetheapplicationcontextcreatedinstep2.ReturnapredicateforaWHEREclause.4.Createapolicy:AssociatesthefunctionwithatableCausesthepredicatetobeaddedtotheWHEREclausesStep3:WritetheFunctionThat

CreatesaPredicateCREATEPACKAGEBODYoe_securityASFUNCTIONcust_order(object_schema VARCHAR2,object_name VARCHAR2)RETURNVARCHAR2ISBEGINRETURN'customer_id=sys_context(''oeapp'',''cust_id'')';ENDcust_order;ENDoe_security;TestingtheSecurityFunctionSQL>SELECToe_security.cust_order('a','b')

FROMdual;OE_SECURITY.CUST_ORDER('A','B')---------------------------------------------customer_id=SYS_CONTEXT('oeapp','cust_id')WritingaFunctionThatReturns

DifferentPredicatesTheownerofthetablehasaccesstoallrows:Salesrepresentativesseeonlytheirorders:Customerscanseeonlytheirownorders:Otherusershavenoaccess:RETURN'sales_rep_id= sys_context(''hrapp'',''emp_id'')';RETURN'customer_id =sys_context(''oeapp'',''cust_id'')';RETURN'1=2';RETURN'1=1';Step4:CreateaPolicyCreatethepolicyasfollows:Argumentsincludethefollowing:Associatedtable:OE.ORDERSPolicyname:OE_POLICYFunction:SECURE.OE_SECURITY.CUST_ORDERAppliesto:SELECTdbms_rls.add_policy( object_schema=>'oe',object_name=>'orders',

policy_name=>'oe_policy', function_schema=>'secure',

policy_function=>'oe_security.cust_order', statement_types=>'select')PartitionedFine-GrainedAccessControlApplication-drivensecuritypoliciesDifferentpoliciesapply,dependingontheactivedrivingcontextPoliciescanbedevelopedindependently.Thedefaultpolicyalwaysapplies.UsefulwhenhostingapplicationsDefaultpolicyOrder-entrypolicygroupInventorypolicygroupANDANDOrdersGroupingPolicies1. Determinethedefaultpolicies.2. Setupadrivingcontextforeachtable:a. Createthecontext.b. Createthefunctionthatsetsthecontext.c. Makethecontextthedrivingcontext.3. Createapolicygroupforeachapplication.4. Addeachpolicytotheappropriategroup.DefaultPolicyGroupApredefineddefaultpolicygroupisalwaysapplied.ItisnamedSYS_DEFAULT.Eachobjecthasadefaultgroup.CreatingaDrivingContextCreatethecontext:Createtheprocedurethatsetsthecontext:CREATECONTEXTapp_driverUSINGsecure.apps_cxt_pkg;CREATEORREPLACEPACKAGEBODYsecure.apps_cxt_pkg PROCEDUREset_driver(policy_groupVARCHAR2)...APP_DRIVERSECURE.APPS_CXT_PKGMakingtheContextaDrivingContextAssociatethedrivingcontextwithatable:dbms_rls.add_policy_context(object_schema=>'OE',object_name=>'ORDERS',namespace=>'APP_DRIVER',

attribute=>'ACTIVE_APP')APP_DRIVEROrdersCreatingaPolicyGroupCreatetheOEgroup:CreatetheACgroup:dbms_rls.create_policy_group(object_schema=>'OE',object_name=>'ORDERS',policy_group=>'OE_GRP');dbms_rls.create_policy_group ('OE','ORDERS','AC_GRP');AddingaPolicytoaGroup1. AddtheOE_SECURITYpolicytotheOEgroup:2. AddtheAC_SECURITYpolicytotheACgroup:dbms_rls.add_grouped_policy(object_schema=>'oe',object_name=>'orders',policy_group=>'oe_grp',policy_name=>'oe_security',function_schema=>'secure',policy_function=>'oe_context');dbms_rls.add_grouped_policy( 'oe','orders','ac_grp','ac_security', 'secure','ac_context');GuidelinesRestrictSELECTandDMLwiththesamepolicy.Ifthereisnocontext,SYS_CONTEXTreturnsNULL.Youcannotselectfromthetablewithinthepolicy.Avoidrecursivecontexts.Lookinthesessiontracefiletoresolveerrors.PerformanceForbestperformance:ConsiderindexingthecolumninthepredicateDonotusesubqueriesinthepredicateDonotuseliteralsinthepredicateUseSTATIC_POLICY=TRUEwhenpossibleUseDBMS_RLS.STATIC_POLICYorSHARED_STATIC_POLICYwhenpossibleExportandImportForexportandimport,considerthefollowingguidelines:

Torestorethepolicies,theusermusthavetheexecuteprivilegeontheDBMS_RLSpackage.Ifauserattemptstoexportatablewithfine-grainedaccesspoliciesenabled,thenonlythoserowsthattheexporterisprivilegedtoreadareexported.OnlySYSoraus