After you have created a single database schema for performing security-related tasks, the next step is to create appropriate database objects in that schema. In this example, you might create a single stored function that will take a user name as the parameter and then return the corresponding row from the usr.accounts table if any.
the f_auth function that will be used to securely access the usr.accounts table.
CONN /AS sysdba
CREATE OR REPLACE PACKAGE sec_adm.sec_pkg IS
TYPE acc_rec_typ IS RECORD (
USR_ID VARCHAR2(10),
FULL_NAME VARCHAR2(20),
PSWD VARCHAR2(10),
NUM_LOGONS NUMBER(38));
TYPE acc_rec_set IS TABLE OF acc_rec_typ;
FUNCTION f_auth(usrid VARCHAR2) RETURN acc_rec_set PIPELINED; END sec_pkg; / CREATE OR REPLACE PACKAGE BODY sec_adm.sec_pkg IS
FUNCTION f_auth(usrid VARCHAR2)
RETURN acc_rec_set PIPELINED IS
acc_rec acc_rec_typ;
BEGIN SELECT * INTO acc_rec FROM usr.accounts WHERE usr_id=usrid; PIPE ROW(acc_rec);
RETURN; END;
END sec_pkg; /
Now let's discuss the statements in the above listing, step by step.
As you might recall from the preceding section, the sec_adm database schema is granted only the RESOURCE role. Therefore, you cannot connect to the sec_adm database schema directly, because it lacks the CREATE SESSION privilege required for this operation. In this example, you connect to the database as sysdba and then create the desired database objects in sec_adm.
With the help of the CREATE PACKAGE statement shown in the above listing, you create a new PL/SQL package specification, declaring types, variables, and subprograms that can be referenced from both inside the package and outside it. Note that in this example you create a package specification from outside the database schema to which that package will belong. That is why in this case, the parameter of the CREATE PACKAGE statement must contain that schema name followed by the package name.
In the Performing Authorization Based on the User Identity section later in this chapter, you will see how package variables can be used to hold authentication information during a database session.
In the package specification, you first define the acc_rec_typ user-defined record type, which should reflect the structure of a usr.accounts table row. To see the exact structure of the usr.accounts table, you might issue the following SQL statement from SQL*Plus when connected as sysdba:
DESC usr.accounts
If you have performed the SQL statements described in the An Example of When to Use a Stored Subprogram section in Chapter 3, as well as the statements in the
Controlling Transactions from PHP section in Chapter 4, then the above statement should produce the following results:
Name Null? Type
--- --- --- USR_ID NOT NULL VARCHAR2(10) FULL_NAME VARCHAR2(20) PSWD NOT NULL VARCHAR2(10) NUM_LOGONS NUMBER(38)
If you did not perform the SQL statements from the Controlling Transactions from PHP
section in Chapter 4, you will not see num_logons field in the above output. If so, you must not include this field in the acc_rec_typ record type either.
Next, you declare the acc_rec_set table type of acc_rec_typ. You will use this type as the return type of the f_auth package function declared in the next line.
The f_auth function will be called from outside the package. Note the use of the PIPELINED keyword when declaring the f_auth function. By declaring a function as PIPELINED, you specify that this function will return a collection of rows like a database table or view when queried.
Pipelined functions, also known as pipelined table functions, may come in very handy when you need to protect sensitive database data from unauthorized access. Unlike regular database tables and views that may be queried with SELECT statements containing no WHERE clause, and, thus, returning all the rows from the queried object, a table function can be organized so that it never returns all the rows from the table or view queried inside the function. For more information on pipelined functions, refer to Oracle documentation: Tuning PL/SQL Applications for Performance chapter in the Oracle Database PL/SQL User's Guide and Reference book.
body, in which you define the code for the f_auth function declared in the package specification.
You begin creating the f_auth function by declaring its header, as defined earlier in the package specification. In this example, the f_auth function takes only one parameter, namely usrid, and then returns the corresponding row from the usr.accounts table if the SELECT statement executed within this function finds one. Then, you define the acc_rec variable of the acc_rec_typ type declared earlier in the package specification. This variable will be used to hold the row retrieved by the SELECT statement.
Next, you query the usr.accounts table to obtain the row whose usr_id field value is equal to the value of the parameter passed to the f_auth function. It is important to note that since the usr_id column in the usr.accounts table is the primary key column, the SELECT statement used here cannot return more than one row. Otherwise, you would have to process the retrieved rows individually in a loop. Now, look at the following statement. With it, you might create the sec_pkg.f_auth function so that it could theoretically return more than one row to the caller:
CREATE OR REPLACE PACKAGE BODY sec_adm.sec_pkg IS FUNCTION f_auth(usrid VARCHAR2)
RETURN acc_rec_set PIPELINED IS BEGIN
FOR acc_rec IN (SELECT * FROM usr.accounts WHERE usr_id=usrid) LOOP PIPE ROW(acc_rec); END LOOP; RETURN; END; END sec_pkg; /
In this particular example, the sec_pkg.f_auth function will always return either one row or none, regardless of whether you perform the SELECT operation in a loop or not. This is because a given username cannot be associated with more than one row in the usr.accounts table.
You use the PIPE ROW statement to pipeline the row retrieved out of the f_auth function to the caller. Once an appropriate row has been pipelined, you use the RETURN statement to end the execution of the function and return control to the caller.