[ Pobierz całość w formacie PDF ]
.So, as Figure 7 2 shows, the procedure must also resideat each regional site.This causes a maintenance problem.Figure 7 2 Definer-Rights ProblemSchema WEST Schema HQ Schema EASTanalyze analyze(DR) (DR)sales salesTo solve the problem, the company installs an invoker-rights (IR) version ofprocedureanalyze at headquarters.Now, as Figure 7 3 shows, all regional sitescan use the same procedure to query their ownsales tables.Figure 7 3 Invoker-Rights SolutionSchema WEST Schema HQ Schema EASTanalyze(IR)sales sales7-30 PL/SQL User s Guide and Reference Invoker Rights versus Definer RightsTo restrict access to sensitive data, you can have an invoker-rights routine call adefiner-rights routine.Suppose headquarters would like procedureanalyze tocalculate sales commissions and update a centralpayroll table.That presents a problem because current users ofanalyze should not have directaccess to thepayroll table, which stores employee salaries and other sensitivedata.As Figure 7 4 shows, the solution is to have procedureanalyze calldefiner-rights procedurecalc_comm, which in turn updates thepayroll table.Figure 7 4 Controlled AccessSchema WEST Schema HQ Schema EASTanalyze(IR)calc_comm(DR)sales salespayrollUsing the AUTHID ClauseTo implement invoker rights, use theAUTHID clause, which specifies whether aroutine executes with the privileges of its owner or its current user.It also specifieswhether external references (that is, references to objects outside the routine) areresolved in the schema of the owner or the current user.TheAUTHID clause is allowed only in the header of a stand-alone subprogram, apackage spec, or an object type spec.The header syntax is-- stand-alone functionCREATE [OR REPLACE] FUNCTION [schema_name.]function_name[(parameter_list)] RETURN datatype[AUTHID {CURRENT_USER | DEFINER}] {IS | AS}Subprograms 7-31 Invoker Rights versus Definer Rights-- stand-alone procedureCREATE [OR REPLACE] PROCEDURE [schema_name.]procedure_name[(parameter_list)][AUTHID {CURRENT_USER | DEFINER}] {IS | AS}-- package specCREATE [OR REPLACE] PACKAGE [schema_name.]package_name[AUTHID {CURRENT_USER | DEFINER}] {IS | AS}-- object type specCREATE [OR REPLACE] TYPE [schema_name.]object_type_name[AUTHID {CURRENT_USER | DEFINER}] {IS | AS} OBJECTwhereDEFINER is the default option.In a package or object type, theAUTHIDclause applies to all routines.Note: Most supplied PL/SQL packages (such asDBMS_LOB,DBMS_PIPE,DBMS_ROWID,DBMS_SQL, andUTL_REF) are invoker-rights packages.Who Is the Current User?In a sequence of calls, if an invoker-rights routine is the first routine called, thecurrent user is the session user.That remains true until a definer-rights routine iscalled, in which case the owner of that routine becomes the current user.If thedefiner-rights routine calls any invoker-rights routines, they execute with theprivileges of the owner.When the definer-rights routine exits, control reverts to theprevious current user.How External References Are ResolvedIf you specifyAUTHIDCURRENT_USER, the privileges of the current user arechecked at run time, and external references are resolved in the schema of thecurrent user.However, this applies only to external references inSELECT,INSERT,UPDATE, andDELETE data manipulation statementstheLOCKTABLE transaction control statementOPEN andOPEN-FOR cursor control statementsEXECUTEIMMEDIATE andOPEN-FOR-USING dynamic SQL statementsSQL statements parsed usingDBMS_SQL.PARSE()7-32 PL/SQL User s Guide and Reference Invoker Rights versus Definer RightsFor all other statements, the privileges of the owner are checked at compile time,and external references are resolved in the schema of the owner.For example, theassignment statement below refers to the packaged functionbalance.This externalreference is resolved in the schema of the owner of procedurereconcile.CREATE PROCEDURE reconcile (acc_id IN INTEGER)AUTHID CURRENT_USER ASbal NUMBER;BEGINbal := bank_ops.balance(acct_id);.END;The Need for Template ObjectsExternal references in an invoker-rights routine are resolved in the schema of thecurrent user at run time.However, the PL/SQL compiler must resolve all referencesat compile time.So, the owner must create template objects in his or her schemabeforehand.At run time, the template objects and the actual objects must match.Otherwise, you get an error or unexpected results.For example, suppose userscott creates the following database table andstand-alone procedure:CREATE TABLE emp (empno NUMBER(4),ename VARCHAR2(15));/CREATE PROCEDURE evaluate (my_empno NUMBER)AUTHID CURRENT_USER ASmy_ename VARCHAR2(15);BEGINSELECT ename INTO my_ename FROM emp WHERE empno = my_empno;.END;/Now, suppose userblake creates a similar database table, then calls procedureevaluate, as follows:CREATE TABLE emp (empno NUMBER(4),ename VARCHAR2(15),my_empno NUMBER(4)); -- note extra column/Subprograms 7-33 Invoker Rights versus Definer RightsDECLARE.BEGIN.scott.evaluate(7788);END;/The procedure call executes without error but ignores columnmy_empno in thetable created by userblake.That is because the actual table in the schema of thecurrent user does not match the template table used at compile time.Overriding Default Name ResolutionOccasionally, you might want to override the default invoker-rights behavior.Suppose userscott defines the stand-alone procedure below.Notice that theSELECT statement calls an external function.Normally, this external referencewould be resolved in the schema of the current user.CREATE PROCEDURE calc_bonus (emp_id INTEGER)AUTHID CURRENT_USER ASmid_sal REAL;BEGINSELECT median(sal) INTO mid_sal FROM emp;.END;To have the reference resolved in the schema of the owner, create a public synonymfor the function using theCREATESYNONYM statement, as follows:CREATE PUBLIC SYNONYM median FOR scott.median;This works unless the current user has defined a function or private synonymnamedmedian.Alternatively, you can fully qualify the reference, as follows:BEGINSELECT scott.median(sal) INTO mid_sal FROM emp;.END;This works unless the current user has defined a package namedscott thatcontains a function namedmedian.7-34 PL/SQL User s Guide and Reference Invoker Rights versus Definer RightsGranting PrivilegesTo call a routine directly, users must have theEXECUTEprivilege on that routine.Bygranting the privilege, you allow a user tocall the routine directlycompile functions and procedures that call the routineFor external references resolved in the current user s schema (such as those in DMLstatements), the current user must have the privileges needed to access schemaobjects referenced by the routine.For all other external references (such as functioncalls), the owner s privileges are checked at compile time, and no run-time check isdone.A definer-rights routine operates under the security domain of its owner, no matterwho is executing it
[ Pobierz całość w formacie PDF ]
