Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 4.2 SPL Programs

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

4.2 SPL Programs

SPL is a procedural, block-structured language. There are four different types of programs that can be created using SPL, namely procedures, functions, triggers, and packages.

Procedures and functions are discussed in more detail later in this section. Triggers are discussed in Chapter 5 and packages are addressed in Chapter 6.

4.2.1 SPL Block Structure

Regardless of whether the program is a procedure, function, or trigger, an SPL program has the same block structure. A block consists of up to three sections - an optional declaration section, a mandatory executable section, and an optional exception section. Minimally, a block has an executable section that consists of one or more SPL statements within the keywords, BEGIN and END.

There may be an optional declaration section that is used to declare variables, cursors, and types that are used by the statements within the executable and exception sections. Declarations appear just prior to the BEGIN keyword of the executable section. Depending upon the context of where the block is used, the declaration section may begin with the keyword DECLARE.

Finally, there may be an optional exception section which appears within the BEGIN - END block. The exception section begins with the keyword, EXCEPTION, and continues until the end of the block in which it appears. If an exception is thrown by a statement within the block, program control goes to the exception section where the thrown exception may or may not be handled depending upon the exception and the contents of the exception section.

The following is the general structure of a block:

[ [ DECLARE ]
      declarations ]
    BEGIN
      statements
  [ EXCEPTION
      WHEN exception_condition THEN
        statements [, ...] ]
    END;

declarations are one or more variable, cursor, or type declarations that are local to the block. Each declaration must be terminated by a semicolon. The use of the keyword DECLARE depends upon the context in which the block appears.

statements are one or more SPL statements. Each statement must be terminated by a semicolon. The end of the block denoted by the keyword END must also be terminated by a semicolon.

If present, the keyword EXCEPTION marks the beginning of the exception section. exception_condition is a conditional expression testing for one or more types of exceptions. If a thrown exception matches one of the exceptions in exception_condition, the statements following the WHEN exception_condition clause are executed. There may be one or more WHEN exception_condition clauses, each followed by statements.

Note: A BEGIN/END block in itself, is considered a statement; thus, blocks may be nested. The exception section may also contain nested blocks.

The following is the simplest possible block consisting of the NULL statement within the executable section. The NULL statement is an executable statement that does nothing.

BEGIN
    NULL;
END;

The following block contains a declaration section as well as the executable section.

DECLARE
    v_numerator     NUMBER(2);
    v_denominator   NUMBER(2);
    v_result        NUMBER(5,2);
BEGIN
    v_numerator := 75;
    v_denominator := 14;
    v_result := v_numerator / v_denominator;
    DBMS_OUTPUT.PUT_LINE(v_numerator || ' divided by ' || v_denominator ||
        ' is ' || v_result);
END;

In this example, three numeric variables are declared of data type NUMBER. In the executable section, values are assigned to two of the variables and then one number is divided by the other, storing the results in a third variable which is then displayed. If this block is executed the output would be as follows.

75 divided by 14 is 5.36

The following block consists of all three sections - the declaration, executable, and exception sections.

DECLARE
    v_numerator     NUMBER(2);
    v_denominator   NUMBER(2);
    v_result        NUMBER(5,2);
BEGIN
    v_numerator := 75;
    v_denominator := 0;
    v_result := v_numerator / v_denominator;
    DBMS_OUTPUT.PUT_LINE(v_numerator || ' divided by ' || v_denominator ||
        ' is ' || v_result);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('An exception occurred');
END;

The following output shows that the statement within the exception section is executed as a result of the division by zero.

An exception occurred

4.2.2 Anonymous Blocks

The preceding section demonstrated the basic structure of a block. A block can simply be executed in Postgres Plus Advanced Server.

A block of this type is called an anonymous block. An anonymous block is unnamed and is not stored in the database. Once the block has been executed and erased from the application buffer, it cannot be re-executed unless the block code is re-entered into the application.

Anonymous blocks are useful for quick, one-time programs such as for testing.

Typically, however, the same block of code would be re-executed many times. In order to run a block of code repeatedly without the necessity of re-entering the code each time, with some simple modifications, an anonymous block can be turned into a procedure or function. The following sections discuss how to create a procedure or function that can be stored in the database and invoked repeatedly by another procedure, function, or application program.

4.2.3 Procedures Overview

Procedures are SPL programs that are invoked or called as an individual SPL program statement. When called, procedures may optionally receive values from the caller in the form of input parameters and optionally return values to the caller in the form of output parameters.

4.2.3.1 Creating a Procedure

The CREATE PROCEDURE command defines and names a procedure that will be stored in the database.

CREATE [ OR REPLACE ] PROCEDURE name [ (parameters) ]
[ AUTHID { DEFINER | CURRENT_USER } ]
{ IS | AS }
  [ declarations ]
  BEGIN
    statements
  END [ name ];

name is the identifier of the procedure. If [ OR REPLACE ] is specified and a procedure with the same name already exists in the schema, the new procedure replaces the existing one. If [ OR REPLACE ] is not specified, the new procedure will not be allowed to replace an existing one with the same name in the same schema. parameters is a list of formal parameters. If the AUTHID clause is omitted or DEFINER is specified, the rights and search path of the procedure owner are used to determine access privileges to database objects and resolve unqualified database object references, respectively. If CURRENT_USER is specified, the rights and search path of the current user executing the procedure are used to determine access privileges and resolve unqualified object references. declarations are variable, cursor, or type declarations. statements are SPL program statements. The BEGIN - END block may contain an EXCEPTION section.

The following is an example of a simple procedure that takes no parameters.

CREATE OR REPLACE PROCEDURE simple_procedure
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('That''s all folks!');
END simple_procedure;

The procedure is stored in the database by entering the procedure code in Postgres Plus Advanced Server.

See the CREATE PROCEDURE command for more information on creating a procedure.

4.2.3.2 Calling a Procedure

The procedure can be invoked from another SPL program by simply specifying the procedure name followed by its parameters, if any, followed by a semicolon.

name [ ([ parameters ]) ];

name is the identifier of the procedure. parameters is a list of actual parameters.

Note: If there are no actual parameters to be passed, the procedure may be called with an empty parameter list, or the opening and closing parenthesis may be omitted entirely.

Note: The syntax for calling a procedure is the same as in the preceding syntax diagram when executing it with the EXEC command in PSQL or EDB*Plus. See Section 11.1.2.12 for information on the EXEC command.

The following is an example of calling the procedure from an anonymous block:

BEGIN
    simple_procedure;
END;

That's all folks!

Note: Each application has its own unique way to call a procedure. In a Java application, the application programming interface, JDBC, is used.

4.2.3.3 Deleting a Procedure

A procedure can be deleted from the database using the DROP PROCEDURE command.

DROP PROCEDURE name;

name is the name of the procedure to be dropped.

The previously created procedure is dropped in this example:

DROP PROCEDURE simple_procedure;

See the DROP PROCEDURE command for more details.

4.2.4 Functions Overview

Functions are SPL programs that are invoked as expressions. When evaluated, a function returns a value that is substituted in the expression in which the function is embedded. Functions may optionally take values from the calling program in the form of input parameters. In addition to the fact that the function, itself, returns a value, a function may optionally return additional values to the caller in the form of output parameters. The use of output parameters in functions, however, is not an encouraged programming practice.

4.2.4.1 Creating a Function

The CREATE FUNCTION command defines and names a function that will be stored in the database.

CREATE [ OR REPLACE ] FUNCTION name [ (parameters) ]
  RETURN data_type
[ AUTHID { DEFINER | CURRENT_USER } ]
{ IS | AS }
  [ declarations ]
  BEGIN
    statements
  END [ name ];

name is the identifier of the function. If [ OR REPLACE ] is specified and a function with the same name already exists in the schema, the new function replaces the existing one. If [ OR REPLACE ] is not specified, the new function will not be allowed to replace an existing one with the same name in the same schema. parameters is a list of formal parameters. data_type is the data type of the value that is returned by the function. If the AUTHID clause is omitted or DEFINER is specified, the rights and search path of the function owner are used to determine access privileges to database objects and resolve unqualified database object references, respectively. If CURRENT_USER is specified, the rights and search path of the current user executing the function are used to determine access privileges and resolve unqualified object references. declarations are variable, cursor, or type declarations. statements are SPL program statements. The BEGIN - END block may contain an EXCEPTION section.

The following is an example of a simple function that takes no parameters.

CREATE OR REPLACE FUNCTION simple_function
    RETURN VARCHAR2
IS
BEGIN
    RETURN 'That''s All Folks!';
END simple_function;

The following is another function that takes two input parameters. Parameters will be discussed in more detail in subsequent sections.

CREATE OR REPLACE FUNCTION emp_comp (
    p_sal           NUMBER,
    p_comm          NUMBER
) RETURN NUMBER
IS
BEGIN
    RETURN (p_sal + NVL(p_comm, 0)) * 24;
END emp_comp;

See the CREATE FUNCTION command for more information.

4.2.4.2 Calling a Function

A function can be used anywhere an expression can appear within an SPL statement. A function is invoked by simply specifying its name followed by its parameters enclosed in parenthesis, if any.

name [ ([ parameters ]) ]

name is the name of the function. parameters is a list of actual parameters.

Note: If there are no actual parameters to be passed, the function may be called with an empty parameter list, or the opening and closing parenthesis may be omitted entirely.

The following shows how the function can be called from another SPL program.

BEGIN
    DBMS_OUTPUT.PUT_LINE(simple_function);
END;

That's All Folks!

A function is typically used within a SQL statement as shown in the following.

SELECT empno "EMPNO", ename "ENAME", sal "SAL", comm "COMM",
    emp_comp(sal, comm) "YEARLY COMPENSATION" FROM emp;

 EMPNO | ENAME  |   SAL   |  COMM   | YEARLY COMPENSATION
-------+--------+---------+---------+---------------------
  7369 | SMITH  |  800.00 |         |            19200.00
  7499 | ALLEN  | 1600.00 |  300.00 |            45600.00
  7521 | WARD   | 1250.00 |  500.00 |            42000.00
  7566 | JONES  | 2975.00 |         |            71400.00
  7654 | MARTIN | 1250.00 | 1400.00 |            63600.00
  7698 | BLAKE  | 2850.00 |         |            68400.00
  7782 | CLARK  | 2450.00 |         |            58800.00
  7788 | SCOTT  | 3000.00 |         |            72000.00
  7839 | KING   | 5000.00 |         |           120000.00
  7844 | TURNER | 1500.00 |    0.00 |            36000.00
  7876 | ADAMS  | 1100.00 |         |            26400.00
  7900 | JAMES  |  950.00 |         |            22800.00
  7902 | FORD   | 3000.00 |         |            72000.00
  7934 | MILLER | 1300.00 |         |            31200.00
(14 rows)

4.2.4.3 Deleting a Function

A function can be deleted from the database using the DROP FUNCTION command.

DROP FUNCTION name [ (parameters) ];

name is the name of the function to be dropped.

Note: The specification of the parameter list is required in Postgres Plus Advanced Server under certain circumstances. Oracle requires that the parameter list always be omitted.

The previously created function is dropped in this example:

DROP FUNCTION simple_function;

See the DROP FUNCTION command for more details.

4.2.5 Procedure and Function Parameters

An important aspect of using procedures and functions is the capability to pass data from the calling program to the procedure or function and to receive data back from the procedure or function. This is accomplished by using parameters.

Parameters are declared in the procedure or function definition, enclosed within parenthesis following the procedure or function name. Parameters declared in the procedure or function definition are known as formal parameters. When the procedure or function is invoked, the calling program supplies the actual data that is to be used in the called program’s processing as well as the variables that are to receive the results of the called program’s processing. The data and variables supplied by the calling program when the procedure or function is called are referred to as the actual parameters.

The following is the general format of a formal parameter declaration.

(name [ IN | OUT | IN OUT ] data_type [ DEFAULT value ])

name is an identifier assigned to the formal parameter. If specified, IN defines the parameter for receiving input data into the procedure or function. An IN parameter can also be initialized to a default value. If specified, OUT defines the parameter for returning data from the procedure or function. If specified, IN OUT allows the parameter to be used for both input and output. If all of IN, OUT, and IN OUT are omitted, then the parameter acts as if it were defined as IN by default. Whether a parameter is IN, OUT, or IN OUT is referred to as the parameter’s mode. data_type defines the data type of the parameter. value is a default value assigned to an IN parameter in the called program if an actual parameter is not specified in the call.

The following is an example of a procedure that takes parameters:

CREATE OR REPLACE PROCEDURE emp_query (
    p_deptno        IN     NUMBER,
    p_empno         IN OUT NUMBER,
    p_ename         IN OUT VARCHAR2,
    p_job           OUT    VARCHAR2,
    p_hiredate      OUT    DATE,
    p_sal           OUT    NUMBER
)
IS
BEGIN
    SELECT empno, ename, job, hiredate, sal
        INTO p_empno, p_ename, p_job, p_hiredate, p_sal
        FROM emp
        WHERE deptno = p_deptno
          AND (empno = p_empno
           OR  ename = UPPER(p_ename));
END;

In this example, p_deptno is an IN formal parameter, p_empno and p_ename are IN OUT formal parameters, and p_job, p_hiredate, and p_sal are OUT formal parameters.

Note: In the previous example, no maximum length was specified on the VARCHAR2 parameters and no precision and scale were specified on the NUMBER parameters. It is illegal to specify a length, precision, scale or other constraints on parameter declarations. These constraints are automatically inherited from the actual parameters that are used when the procedure or function is called.

The emp_query procedure can be called by another program, passing it the actual parameters. The following is an example of another SPL program that calls emp_query.

DECLARE
    v_deptno        NUMBER(2);
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_job           VARCHAR2(9);
    v_hiredate      DATE;
    v_sal           NUMBER;
BEGIN
    v_deptno := 30;
    v_empno  := 7900;
    v_ename  := '';
    emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal);
    DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
    DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || v_job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || v_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || v_sal);
END;

In this example, v_deptno, v_empno, v_ename, v_job, v_hiredate, and v_sal are the actual parameters.

The output from the preceding example is shown as follows:

Department : 30
Employee No: 7900
Name       : JAMES
Job        : CLERK
Hire Date  : 03-DEC-81
Salary     : 950

4.2.5.1 Positional vs. Named Parameter Notation

You can use either positional or named parameter notation when passing parameters to a function or procedure. If you specify parameters using positional notation, you must list the parameters in the order that they are declared; if you specify parameters with named notation, the order of the parameters is not significant.

To specify parameters using named notation, list the name of each parameter followed by an arrow (=>) and the parameter value. Named notation is more verbose, but makes your code easier to read and maintain.

A simple example that demonstrates using positional and named parameter notation follows:

      CREATE OR REPLACE PROCEDURE emp_info (    
          p_deptno        IN     NUMBER,    
          p_empno         IN OUT NUMBER,    
          p_ename         IN OUT VARCHAR2,    
      )    
      IS    
      BEGIN    
          dbms_output.put_line('Department Number =' || p_deptno);    
          dbms_output.put_line('Employee Number =' || p_empno);    
          dbms_output.put_line('Employee Name =' || p_ename;    
      END;

To call the procedure using positional notation, pass the following:

      emp_info(30, 7455, 'Clark');

To call the procedure using named notation, pass the following:

      emp_info(p_ename =>'Clark', p_empno=>7455, p_deptno=>30);

Using named notation can alleviate the need to re-arrange a procedure’s parameter list if the parameter list changes, if the parameters are reordered or if a new optional parameter is added.

In a case where you have a default value for an argument and the argument is not a trailing argument, you must use named notation to call the procedure or function. The following case demonstrates a procedure with two, leading, default arguments.

CREATE OR REPLACE PROCEDURE check_balance (
    p_customerID  IN NUMBER DEFAULT NULL,
    p_balance     IN NUMBER DEFAULT NULL,
    p_amount      IN NUMBER
)
IS 
DECLARE  
    balance NUMBER;
BEGIN
   IF (p_balance IS NULL AND p_customerID IS NULL) THEN
      RAISE_APPLICATION_ERROR 
          (-20010, 'Must provide balance or customer');
   ELSEIF (p_balance IS NOT NULL AND p_customerID IS NOT NULL) THEN
      RAISE_APPLICATION_ERROR 
          (-20020,'Must provide balance or customer, not both');
   ELSEIF (p_balance IS NULL) THEN
      balance := getCustomerBalance(p_customerID);
   ELSE 
      balance := p_balance;
   END IF;
   
   IF (amount > balance) THEN
      RAISE_APPLICATION_ERROR 
        (-20030, 'Balance insufficient');
   END IF;
END;

You can only omit non-trailing argument values (when you call this procedure) by using named notation; when using positional notation, only trailing arguments are allowed to default. You can call this procedure with the following arguments:

      check_balance(p_customerID => 10, p_amount = 500.00)
          
      check_balance(p_balance => 1000.00, p_amount = 500.00)

You can use a combination of positional and named notation (mixed notation) to specify parameters. A simple example that demonstrates using mixed parameter notation follows:

      CREATE OR REPLACE PROCEDURE emp_info (    
          p_deptno        IN     NUMBER,    
          p_empno         IN OUT NUMBER,    
          p_ename         IN OUT VARCHAR2,    
      )    
      IS    
      BEGIN    
          dbms_output.put_line('Department Number =' || p_deptno);    
          dbms_output.put_line('Employee Number =' || p_empno);    
          dbms_output.put_line('Employee Name =' || p_ename;    
      END;

You can call the procedure using mixed notation:

      emp_info(30, p_ename =>'Clark', p_empno=>7455);

If you do use mixed notation, remember that named arguments cannot precede positional arguments.

4.2.5.2 Parameter Modes

As previously discussed, a parameter has one of three possible modes - IN, OUT, or IN OUT. The following characteristics of a formal parameter are dependent upon its mode:

    ● Its initial value when the procedure or function is called.

    ● Whether or not the called procedure or function can modify the formal parameter.

    ● How the actual parameter value is passed from the calling program to the called program.

    ● What happens to the formal parameter value when an unhandled exception occurs in the called program.

The following table summarizes the behavior of parameters according to their mode.

Table 4-4-1 Parameter Modes

Mode Property

IN

IN OUT

OUT

Formal parameter initialized to:

Actual parameter value

Actual parameter value

Actual parameter value

Formal parameter modifiable by the called program?

No

Yes

Yes

Actual parameter contains: (after normal called program termination)

Original actual parameter value prior to the call

Last value of the formal parameter

Last value of the formal parameter

Actual parameter contains: (after a handled exception in the called program)

Original actual parameter value prior to the call

Last value of the formal parameter

Last value of the formal parameter

Actual parameter contains: (after an unhandled exception in the called program)

Original actual parameter value prior to the call

Original actual parameter value prior to the call

Original actual parameter value prior to the call

As shown by the table, an IN formal parameter is initialized to the actual parameter with which it is called unless it was explicitly initialized with a default value. The IN parameter may be referenced within the called program, however, the called program may not assign a new value to the IN parameter. After control returns to the calling program, the actual parameter always contains the same value as it was set to prior to the call.

The OUT formal parameter is initialized to the actual parameter with which it is called. The called program may reference and assign new values to the formal parameter. If the called program terminates without an exception, the actual parameter takes on the value last set in the formal parameter. If a handled exception occurs, the value of the actual parameter takes on the last value assigned to the formal parameter. If an unhandled exception occurs, the value of the actual parameter remains as it was prior to the call.

Like an IN parameter, an IN OUT formal parameter is initialized to the actual parameter with which it is called. Like an OUT parameter, an IN OUT formal parameter is modifiable by the called program and the last value in the formal parameter is passed to the calling program’s actual parameter if the called program terminates without an exception. If a handled exception occurs, the value of the actual parameter takes on the last value assigned to the formal parameter. If an unhandled exception occurs, the value of the actual parameter remains as it was prior to the call.

4.2.5.3 Using Default Values in Parameters

You can set a default value of a formal parameter by including the DEFAULT clause or using the assignment operator (:=) in the CREATE PROCEDURE or CREATE FUNCTION statement.

The general form of a formal parameter declaration is:

(name [ IN|OUT|IN OUT ] data_type [{DEFAULT | := } expr ])

name is an identifier assigned to the parameter.

IN|OUT|IN OUT specifies the parameter mode.

data_type is the data type assigned to the variable.

expr is the default value assigned to the parameter. If you do not include a DEFAULT clause, the caller must provide a value for the parameter.

The default value is evaluated every time the function or procedure is invoked. For example, assigning SYSDATE to a parameter of type DATE causes the parameter to have the time of the current invocation, not the time when the procedure or function was created.

The following simple procedure demonstrates using the assignment operator to set a default value of SYSDATE into the parameter, hiredate:

CREATE OR REPLACE PROCEDURE hire_emp (
    p_empno         NUMBER,
    p_ename         VARCHAR2,
    p_hiredate      DATE := SYSDATE
) RETURN 
IS
BEGIN
    INSERT INTO emp(empno, ename, hiredate)
                   VALUES(p_empno, p_ename, p_hiredate);

    DBMS_OUTPUT.PUT_LINE('Hired!');
END emp_comp;

If the parameter declaration includes a default value, you can omit the parameter from the actual parameter list when you call the function. Calls to the sample procedure (hire_emp) must include two arguments: the employee number (p_empno) and employee name (p_empno). The third parameter (p_hiredate) defaults to the value of SYSDATE:

 hire_emp 7575, Clark

If you do include a value for the actual parameter when you call the function, that value takes precedence over the default value:

hire_emp 7575, Clark, 15-FEB-2010 

Adds a new employee with a hiredate of February 15, 2010, regardless of the current value of SYSDATE.

You can write the same function by substituting the DEFAULT keyword for the assignment operator:

CREATE OR REPLACE PROCEDURE hire_emp (
    p_empno         NUMBER,
    p_ename         VARCHAR2,
    p_hiredate      DATE DEFAULT SYSDATE
) RETURN 
IS
BEGIN
    INSERT INTO emp(empno, ename, hiredate)
                   VALUES(p_empno, p_ename, p_hiredate);

    DBMS_OUTPUT.PUT_LINE('Hired!');
END emp_comp;

4.2.6 Program Security

Security over what user may execute an SPL program and what database objects an SPL program may access for any given user executing the program is controlled by the following:

    ● Privilege to execute a program.

    ● Privileges granted on the database objects (including other SPL programs) which a program attempts to access.

    ● Whether the program is defined with definer’s rights or invoker’s rights.

These aspects are discussed in the following sections.

4.2.6.1 EXECUTE Privilege

An SPL program (function, procedure, or package) can begin execution only if any of the following are true:

    ● The current user is a superuser, or

    ● The current user has been granted EXECUTE privilege on the SPL program, or

    ● The current user inherits EXECUTE privilege on the SPL program by virtue of being a member of a group which does have such privilege, or

    EXECUTE privilege has been granted to the PUBLIC group.

Whenever an SPL program is created in Postgres Plus Advanced Server, EXECUTE privilege is automatically granted to the PUBLIC group by default, therefore, any user can immediately execute the program.

This default privilege can be removed by using the REVOKE EXECUTE command. See th REVOKE command for details. The following is an example:

REVOKE EXECUTE ON PROCEDURE list_emp FROM PUBLIC;

Explicit EXECUTE privilege on the program can then be granted to individual users or groups.

GRANT EXECUTE ON PROCEDURE list_emp TO john;

Now, user, john, can execute the list_emp program; other users who do not meet any of the conditions listed at the beginning of this section cannot.

Once a program begins execution, the next aspect of security is what privilege checks occur if the program attempts to perform an action on any database object including:

    ● Reading or modifying table or view data.

    ● Creating, modifying, or deleting a database object such as a table, view, index, or sequence.

    ● Obtaining the current or next value from a sequence.

    ● Calling another program (function, procedure, or package).

Each such action can be protected by privileges on the database object either allowed or disallowed for the user.

Note that it is possible for a database to have more than one object of the same type with the same name, but each such object belonging to a different schema in the database. If this is the case, which object is being referenced by an SPL program? This is the topic of the next section.

4.2.6.2 Database Object Name Resolution

A database object inside an SPL program may either be referenced by its qualified name or by an unqualified name. A qualified name is in the form of schema.name where schema is the name of the schema under which the database object with identifier, name, exists. An unqualified name does not have the “schema.” portion. When a reference is made to a qualified name, there is absolutely no ambiguity as to exactly which database object is intended – it either does or does not exist in the specified schema.

Locating an object with an unqualified name, however, requires the use of the current user’s search path. When a user becomes the current user of a session, a default search path is always associated with that user. The search path consists of a list of schemas which are searched in left-to-right order for locating an unqualified database object reference. The object is considered non-existent if it can’t be found in any of the schemas in the search path. The default search path can be displayed in PSQL using the SHOW search_path command.

SHOW search_path;

     search_path
----------------------
 $user,public,sys,dbo
(1 row)

$user in the above search path is a generic placeholder that refers to the current user so if the current user of the above session is enterprisedb, an unqualified database object would be searched for in the following schemas in this order – first, enterprisedb, then public, then sys, and finally, dbo.

Once an unqualified name has been resolved in the search path, it can be determined if the current user has the appropriate privilege to perform the desired action on that specific object.

Note: The concept of the search path is not Oracle compatible. For an unqualified reference, Oracle simply looks in the schema of the current user for the named database object. It also important to note that in Oracle, a user and his or her schema is the same entity while in Postgres Plus Advanced Server, a user and a schema are two distinct objects.

4.2.6.3 Database Object Privileges

Once an SPL program begins execution, any attempt to access a database object from within the program results in a check to ensure the current user has the authorization to perform the intended action against the referenced object. Privileges on database objects are bestowed and removed using the GRANT and REVOKE commands, respectively. If the current user attempts unauthorized access on a database object, then the program will throw an exception. See Section 4.5.7 for information on exception handling.

The final topic discusses exactly who is the current user.

4.2.6.4 Definer’s vs. Invokers Rights

When an SPL program is about to begin execution, a determination is made as to what user is to be associated with this process. This user is referred to as the current user. It is the current user’s search path that will be used to resolve any unqualified object references. The current user’s database object privileges are used to determine whether or not access to database objects referenced in the program will be permitted.

The selection of the current user is influenced by whether the SPL program was created with definer’s right or invoker’s rights. The AUTHID clause determines that selection. Appearance of the clause AUTHID DEFINER gives the program definer’s rights. This is also the default if the AUTHID clause is omitted. Use of the clause AUTHID CURRENT_USER gives the program invoker’s rights. The difference between the two is summarized as follows:

    ● If a program has definer’s rights, then the owner of the program becomes the current user when program execution begins. The program owner’s search path is used to resolve unqualified object references and the program owner’s database object privileges are used to determine if access to a referenced object is permitted. In a definer’s rights program, it is irrelevant as to which user actually invoked the program.

    ● If a program has invoker’s rights, then the current user at the time the program is called remains the current user while the program is executing (but not necessarily within called subprograms – see the following bullet points). When an invoker’s rights program is invoked, the current user is typically the user that started the session (i.e., made the database connection) although it is possible to change the current user after the session has started using the SET ROLE command. In an invoker’s rights program, it is irrelevant as to which user actually owns the program.

From the previous definitions, the following observations can be made:

    ● If a definer’s rights program calls a definer’s rights program, the current user changes from the owner of the calling program to the owner of the called program during execution of the called program.

    ● If a definer’s rights program calls an invoker’s rights program, the owner of the calling program remains the current user during execution of both the calling and called programs.

    ● If an invoker’s rights program calls an invoker’s rights program, the current user of the calling program remains the current user during execution of the called program.

    ● If an invokers’ rights program calls a definer’s rights program, the current user switches to the owner of the definer’s rights program during execution of the called program.

The same principles apply if the called program in turn calls another program in the cases cited above.

This section on security concludes with an example using the sample application.

4.2.6.5 Security Example

In the following example, a new database will be created along with two users – hr_mgr who will own a copy of the entire sample application in schema, hr_mgr; and sales_mgr who will own a schema named, sales_mgr, that will have a copy of only the emp table containing only the employees who work in sales.

The procedure list_emp, function hire_clerk, and package emp_admin will be used in this example. All of the default privileges that are granted upon installation of the sample application will be removed and then be explicitly re-granted so as to present a more secure environment in this example.

Programs list_emp and hire_clerk will be changed from the default of definer’s rights to invoker’s rights. It will be then illustrated that when sales_mgr runs these programs, they act upon the emp table in sales_mgr’s schema since sales_mgr’s search path and privileges will be used for name resolution and authorization checking.

Programs get_dept_name and hire_emp in the emp_admin package will then be executed by sales_mgr. In this case, the dept table and emp table in hr_mgr’s schema will be accessed as hr_mgr is the owner of the emp_admin package which is using definer’s rights.

Step 1 – Create Database and Users

As user enterprisedb, create the hr database:

CREATE DATABASE hr;

Switch to the hr database and create the users:

\c hr enterprisedb
CREATE USER hr_mgr IDENTIFIED BY password;
CREATE USER sales_mgr IDENTIFIED BY password;

Step 2 – Create the Sample Application

Create the entire sample application, owned by hr_mgr, in hr_mgr’s schema.

\c - hr_mgr
\i C:/Program Files/PostgresPlus/9.0AS/installer/server/edb-sample.sql

BEGIN
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE VIEW
CREATE SEQUENCE
        .
        .
        .
CREATE PACKAGE
CREATE PACKAGE BODY
COMMIT

Step 3 – Create the emp Table in Schema sales_mgr

Create a subset of the emp table owned by sales_mgr in sales_mgr’s schema.

\c – hr_mgr
GRANT USAGE ON SCHEMA hr_mgr TO sales_mgr;
\c – sales_mgr
CREATE TABLE emp AS SELECT * FROM hr_mgr.emp WHERE job = 'SALESMAN';

In the above example, the GRANT USAGE ON SCHEMA command is given to allow sales_mgr access into hr_mgr’s schema to make a copy of hr_mgr’s emp table. This step is required in Postgres Plus Advanced Server and is not Oracle compatible since Oracle does not have the concept of a schema that is distinct from its user.

Step 4 – Remove Default Privileges

Remove all privileges to later illustrate the minimum required privileges needed.

\c – hr_mgr
REVOKE USAGE ON SCHEMA hr_mgr FROM sales_mgr;
REVOKE ALL ON dept FROM PUBLIC;
REVOKE ALL ON emp FROM PUBLIC;
REVOKE ALL ON next_empno FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION new_empno() FROM PUBLIC;
REVOKE EXECUTE ON PROCEDURE list_emp FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION hire_clerk(VARCHAR2,NUMBER) FROM PUBLIC;
REVOKE EXECUTE ON PACKAGE emp_admin FROM PUBLIC;

Step 5 – Change list_emp to Invoker’s Rights

While connected as user, hr_mgr, add the AUTHID CURRENT_USER clause to the list_emp program and resave it in Postgres Plus Advanced Server. When performing this step, be sure you are logged on as hr_mgr, otherwise the modified program may wind up in the public schema instead of in hr_mgr’s schema.

CREATE OR REPLACE PROCEDURE list_emp
AUTHID CURRENT_USER
IS
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    CURSOR emp_cur IS
        SELECT empno, ename FROM emp ORDER BY empno;
BEGIN
    OPEN emp_cur;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_cur INTO v_empno, v_ename;
        EXIT WHEN emp_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || v_ename);
    END LOOP;
    CLOSE emp_cur;
END;

Step 6 – Change hire_clerk to Invoker’s Rights and Qualify Call to new_empno

While connected as user, hr_mgr, add the AUTHID CURRENT_USER clause to the hire_clerk program.

Also, after the BEGIN statement, fully qualify the reference, new_empno, to hr_mgr.new_empno. In order to force the hire_clerk function to call the new_empno function in the hr_mgr schema, the call must be changed to a fully qualified name. Since hire_clerk is now an invoker’s rights program, an unqualified call to new_empno would result in a search for new_empno in a schema in the search path of hire_clerk’s caller rather than specifically in schema, hr_mgr, where this program actually resides.

When resaving the program, be sure you are logged on as hr_mgr, otherwise the modified program may wind up in the public schema instead of in hr_mgr’s schema.

CREATE OR REPLACE FUNCTION hire_clerk (
    p_ename         VARCHAR2,
    p_deptno        NUMBER
) RETURN NUMBER
AUTHID CURRENT_USER
IS
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_job           VARCHAR2(9);
    v_mgr           NUMBER(4);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_deptno        NUMBER(2);
BEGIN
    v_empno := hr_mgr.new_empno;
    INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782,
        TRUNC(SYSDATE), 950.00, NULL, p_deptno);
    SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO
        v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
        FROM emp WHERE empno = v_empno;
    DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
    DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || v_job);
    DBMS_OUTPUT.PUT_LINE('Manager    : ' || v_mgr);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || v_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || v_sal);
    DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm);
    RETURN v_empno;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:');
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
        DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:');
        DBMS_OUTPUT.PUT_LINE(SQLCODE);
        RETURN -1;
END;

Step 7 – Grant Required Privileges

While connected as user, hr_mgr, grant the privileges needed so sales_mgr can execute the list_emp procedure, hire_clerk function, and emp_admin package. Note that the only data object sales_mgr has access to is the emp table in the sales_mgr schema. sales_mgr has no privileges on any table in the hr_mgr schema.

GRANT USAGE ON SCHEMA hr_mgr TO sales_mgr;
GRANT EXECUTE ON PROCEDURE list_emp TO sales_mgr;
GRANT EXECUTE ON FUNCTION hire_clerk(VARCHAR2,NUMBER) TO sales_mgr;
GRANT EXECUTE ON FUNCTION new_empno() TO sales_mgr;
GRANT EXECUTE ON PACKAGE emp_admin TO sales_mgr;

Step 8 – Run Programs list_emp and hire_clerk

Connect as user, sales_mgr, and run the following anonymous block:

\c – sales_mgr
DECLARE
    v_empno         NUMBER(4);
BEGIN
    hr_mgr.list_emp;
    DBMS_OUTPUT.PUT_LINE('*** Adding new employee ***');
    v_empno := hr_mgr.hire_clerk('JONES',40);
    DBMS_OUTPUT.PUT_LINE('*** After new employee added ***');
    hr_mgr.list_emp;
END;

EMPNO    ENAME
-----    -------
7499     ALLEN
7521     WARD
7654     MARTIN
7844     TURNER
*** Adding new employee ***
Department : 40
Employee No: 8000
Name       : JONES
Job        : CLERK
Manager    : 7782
Hire Date  : 08-NOV-07 00:00:00
Salary     : 950.00
*** After new employee added ***
EMPNO    ENAME
-----    -------
7499     ALLEN
7521     WARD
7654     MARTIN
7844     TURNER
8000     JONES

The table and sequence accessed by the programs of the anonymous block are illustrated in the following diagram. The gray ovals represent the schemas of sales_mgr and hr_mgr. The current user during each program execution is shown within parenthesis in bold red font.


Figure 3 - Invoker's Rights Programs

Selecting from sales_mgr’s emp table shows that the update was made in this table.

SELECT empno, ename, hiredate, sal, deptno, hr_mgr.emp_admin.get_dept_name(deptno) FROM sales_mgr.emp;

empno | ename  |      hiredate      |   sal   | deptno | get_dept_name
-------+--------+--------------------+---------+--------+---------------
  7499 | ALLEN  | 20-FEB-81 00:00:00 | 1600.00 |     30 | SALES
  7521 | WARD   | 22-FEB-81 00:00:00 | 1250.00 |     30 | SALES
  7654 | MARTIN | 28-SEP-81 00:00:00 | 1250.00 |     30 | SALES
  7844 | TURNER | 08-SEP-81 00:00:00 | 1500.00 |     30 | SALES
  8000 | JONES  | 08-NOV-07 00:00:00 |  950.00 |     40 | OPERATIONS
(5 rows)

The following diagram shows that the SELECT command references the emp table in the sales_mgr schema, but the dept table referenced by the get_dept_name function in the emp_admin package is from the hr_mgr schema since the emp_admin package has definer’s rights and is owned by hr_mgr.


Figure 4 Definer's Rights Package

Step 9 – Run Program hire_emp in the emp_admin Package

While connected as user, sales_mgr, run the hire_emp procedure in the emp_admin package.

EXEC hr_mgr.emp_admin.hire_emp(9001, 'ALICE','SALESMAN',8000,TRUNC(SYSDATE),1000,7369,40);

This diagram illustrates that the hire_emp procedure in the emp_admin definer’s rights package updates the emp table belonging to hr_mgr.


Figure 5 Definer's Rights Package

Now connect as user, hr_mgr. The following SELECT command verifies that the new employee was added to hr_mgr’s emp table since the emp_admin package has definer’s rights and hr_mgr is emp_admin’s owner.

\c – hr_mgr
SELECT empno, ename, hiredate, sal, deptno, hr_mgr.emp_admin.get_dept_name(deptno) FROM hr_mgr.emp;

empno | ename  |      hiredate      |   sal   | deptno | get_dept_name
-------+--------+--------------------+---------+--------+---------------
  7369 | SMITH  | 17-DEC-80 00:00:00 |  800.00 |     20 | RESEARCH
  7499 | ALLEN  | 20-FEB-81 00:00:00 | 1600.00 |     30 | SALES
  7521 | WARD   | 22-FEB-81 00:00:00 | 1250.00 |     30 | SALES
  7566 | JONES  | 02-APR-81 00:00:00 | 2975.00 |     20 | RESEARCH
  7654 | MARTIN | 28-SEP-81 00:00:00 | 1250.00 |     30 | SALES
  7698 | BLAKE  | 01-MAY-81 00:00:00 | 2850.00 |     30 | SALES
  7782 | CLARK  | 09-JUN-81 00:00:00 | 2450.00 |     10 | ACCOUNTING
  7788 | SCOTT  | 19-APR-87 00:00:00 | 3000.00 |     20 | RESEARCH
  7839 | KING   | 17-NOV-81 00:00:00 | 5000.00 |     10 | ACCOUNTING
  7844 | TURNER | 08-SEP-81 00:00:00 | 1500.00 |     30 | SALES
  7876 | ADAMS  | 23-MAY-87 00:00:00 | 1100.00 |     20 | RESEARCH
  7900 | JAMES  | 03-DEC-81 00:00:00 |  950.00 |     30 | SALES
  7902 | FORD   | 03-DEC-81 00:00:00 | 3000.00 |     20 | RESEARCH
  7934 | MILLER | 23-JAN-82 00:00:00 | 1300.00 |     10 | ACCOUNTING
  9001 | ALICE  | 08-NOV-07 00:00:00 | 8000.00 |     40 | OPERATIONS
(15 rows)

Previous PageTable Of ContentsNext Page