|
[ Pobierz całość w formacie PDF ]
.You can write a program consisting of multiple routines, some with definer rightsand others with invoker rights.Then, you can use theEXECUTE privilege to restrictprogram entry points (controlled step-in).That way, users of an entry-point routinecan execute the other routines indirectly but not directly.An ExampleSuppose userutil grants theEXECUTE privilege on routinefft to userapp, asfollows:GRANT EXECUTE ON util.fft TO app;Now, userapp can compile functions and procedures that call routinefft.At runtime, no privilege checks on the calls are done.So, as Figure 7 5 on page 7-36shows, userutil need not grant theEXECUTE privilege to every user who mightcallfft indirectly.Notice that routineutil.fft is called directly only from invoker-rights routineapp.entry.So, userutil must grant theEXECUTE privilege only to userapp.Whenutil.fft is executed, its current user could beapp,scott, orblake eventhoughscott andblake were not granted theEXECUTE privilege.Subprograms 7-35 Invoker Rights versus Definer RightsFigure 7 5 Indirect CallsSchema SCOTTproc1Schema UTILSchema APPentryfft(IR)Schema BLAKEproc2Using RolesThe use of roles in a routine depends on whether it executes with definer rights orinvoker rights.Within a definer-rights routine, all roles are disabled.Roles are notused for privilege checking, and you cannot set roles.Within an invoker-rights routine, roles are enabled (unless the routine was calleddirectly or indirectly by a definer-rights routine).Roles are used for privilegechecking, and you can use native dynamic SQL to set roles for the session.However, you cannot use roles to grant privileges on template objects because rolesapply at run time, not at compile time.Using Views and Database TriggersFor invoker-rights routines executed within a view expression, the view owner, notthe view user, is considered to be the current user.For example, suppose userscott creates a view as shown below.The invoker-rights functionlayout alwaysexecutes with the privileges of userscott, who is the view owner.CREATE VIEW payroll AS SELECT layout(3) FROM dual;This rule also applies to database triggers.7-36 PL/SQL User s Guide and Reference Invoker Rights versus Definer RightsUsing Database LinksInvoker rights affect only one kind of database link current-user links, which arecreated as follows:CREATE DATABASE LINK link_name CONNECT TO CURRENT_USERUSING connect_string;A current-user link lets you connect to a remote database as another user, with thatuser s privileges.To connect, Oracle uses the username of the current user (whomust be a global user).Suppose an invoker-rights routine owned by userblakereferences the database link below.If global userscottcalls the routine, it connectsto the Dallas database as userscott, who is the current user.CREATE DATABASE LINK dallas CONNECT TO CURRENT_USER USING.If it were a definer-rights routine, the current user would beblake.So, the routinewould connect to the Dallas database as global userblake.Using Object TypesTo define object types for use in any schema, specify theAUTHIDCURRENT_USERclause.(For more information about object types, see Chapter 9.) Suppose userblake creates the following object type:CREATE TYPE Num AUTHID CURRENT_USER AS OBJECT (x NUMBER,STATIC PROCEDURE new_num (n NUMBER, schema_name VARCHAR2, table_name VARCHAR2));CREATE TYPE BODY Num ASSTATIC PROCEDURE new_num (n NUMBER, schema_name VARCHAR2, table_name VARCHAR2) ISsql_stmt VARCHAR2(200);BEGINsql_stmt := INSERT INTO || schema_name || .|| table_name || VALUES (blake.Num(:1)) ;EXECUTE IMMEDIATE sql_stmt USING n;END;END;Then, userblake grants theEXECUTE privilege on object typeNum to userscott:GRANT EXECUTE ON Num TO scott;Subprograms 7-37 Invoker Rights versus Definer RightsFinally, userscott creates an object table to store objects of typeNum, then callsprocedurenew_num to populate the table:CONNECT scott/tiger;CREATE TABLE num_tab OF blake.Num;/BEGINblake.Num.new_num(1001, scott , num_tab );blake.Num.new_num(1002, scott , num_tab );blake.Num.new_num(1003, scott , num_tab );END;/The calls succeed because the procedure executes with the privileges of its currentuser (scott), not its owner (blake):Calling Instance MethodsAn invoker-rights instance method executes with the privileges of the invoker, notthe creator of the instance.Suppose thatPerson is an invoker-rights object type,and that userscott createsp1, an object of typePerson.If userblake callsinstance methodchange_job to operate on objectp1, the current user of themethod isblake, notscott.Consider the following example:-- user blake creates a definer-rights procedureCREATE PROCEDURE reassign (p Person, new_job VARCHAR2) ASBEGIN-- user blake calls method change_job, so the-- method executes with the privileges of blakep.change_job(new_job);.END;-- user scott passes a Person object to the procedureDECLAREp1 Person;BEGINp1 := Person(.);blake.reassign(p1, CLERK );.END;7-38 PL/SQL User s Guide and Reference Understanding and Using RecursionUnderstanding and Using RecursionRecursion is a powerful technique for simplifying the design of algorithms.Basically, recursion means self-reference.In a recursive mathematical sequence, eachterm is derived by applying a formula to preceding terms.The Fibonacci sequence(0, 1, 1, 2, 3, 5, 8, 13, 21,.), which was first used to model the growth of a rabbitcolony, is an example.Each term in the sequence (after the second) is the sum of thetwo terms that immediately precede it.In a recursive definition, something is defined as simpler versions of itself.Considerthe definition of n factorial (n!), the product of all integers from 1 to n:n! = n * (n - 1)!What Is a Recursive Subprogram?A recursive subprogram is one that calls itself.Think of a recursive call as a call tosome other subprogram that does the same task as your subprogram.Eachrecursive call creates a new instance of any items declared in the subprogram,including parameters, variables, cursors, and exceptions.Likewise, new instances ofSQL statements are created at each level in the recursive descent.Be careful where you place a recursive call.If you place it inside a cursorFOR loopor betweenOPEN andCLOSE statements, another cursor is opened at each call.As aresult, your program might exceed the limit set by the Oracle initializationparameterOPEN_CURSORS.There must be at least two paths through a recursive subprogram: one that leads tothe recursive call and one that does not.At least one path must lead to a terminatingcondition.Otherwise, the recursion would (theoretically) go on forever.In practice, ifa recursive subprogram strays into infinite regress, PL/SQL eventually runs out ofmemory and raises the predefined exceptionSTORAGE_ERROR.Example 1To solve some programming problems, you must repeat a sequence of statementsuntil a condition is met.You can use iteration or recursion to solve such problems [ Pobierz całość w formacie PDF ] |
Archiwum
|