To improve security, you might create another database schema to be used only for establishing connections, and then give only SELECT privileges on the database objects that your application queries to that schema.

As an example of where this technique might be useful, let's turn back to the example discussed in the PEAR::Auth in Action section in the preceding chapter. Looking through the testAuth.php script shown in the listing, you may notice that it authenticates users against the accounts table, which in turn is created as described in the An Example of When to Use a Stored Subprogram section in Chapter 3. Since the testAuth.php script connects to the database through the same database schema where the accounts table resides, this represents a weak spot in the application's defense.

Before you proceed with this example, make sure that you have the accounts table installed in your database. To do this, issue the following SELECT statement from SQL*Plus when connected as usr/usr:

SELECT * FROM accounts;

If you have performed the SQL statements shown in the An Example of When to Use a Stored Subprogram section in Chapter 3, you should receive results that might look as follows:

USR_ID FULL_NAME PSWD NUM_LOGONS
---------- -------------------- ---------- ----------
bob Bob Robinson pswd 32

If you still don't have the accounts table, you need to create it now, using the appropriate statements from the An Example of When to Use a Stored Subprogram section in Chapter 3.

Once you have that set up, you can create a new database schema through which the testAuth.php script will connect to the database. Then, you can grant the SELECT privileges on the accounts table to the newly created schema. The following SQL statements are required to perform these tasks.

CONN /AS sysdba

Using this SQL statement, you create a new database schema that will be used by the application to connect to the database.

CREATE USER app_conn IDENTIFIED BY appconn;

Once a database schema is created, you should grant all the required privileges to it. When granting privileges, you should always remember the least privilege principle, which implies that users should be given only the minimum privileges required to do their job and nothing else.

In this example, you grant only the default CONNECT role to the newly created schema, thus allowing users accessing only this account to connect to the database:

GRANT CONNECT TO app_conn;

Next, you grant the SELECT privilege on the usr.accounts table to the app_conn schema, so that the application users using app_conn schema to connect to the database will be able to view data stored in this table, but not alter it.

GRANT SELECT ON usr.accounts TO app_conn;

Now that you have the app_conn schema set up and granted the CONNECT role, you can use this schema instead of usr in your application in order to connect to the database. So, you should revoke the CONNECT role from the usr schema for security reasons.

REVOKE CONNECT FROM usr;

Once you have performed all the SQL statements discussed above, you need to slightly modify the testAuth.php script discussed in the PEAR::Auth in Action section in Chapter 5 to use the newly created app_conn database schema instead of usr.

In fact, all you need to modify is the array of options passed to the storage container of the Auth object, as shown below:

$auth_opts = array(
'dsn'=>'oci8://app_conn:appconn@localhost:1521/orcl',
'table'=>'usr.accounts',
'usernamecol'=>'usr_id',
'passwordcol'=>'pswd',
'db_fields' => '*',
'cryptType'=>'none'
);

Once you've done that, you can then test the updated authentication system. To do this, you might run the appPage.php script discussed in the Securing Pages with PEAR::Auth section in the preceding chapter. When testing the sample, you should note that while the above changes improve the security of the authentication scheme used here, from the user's point of view, the sample behaves as before.

Having two database schemas—one for establishing connections and the other for holding database resources used by the application—is undoubtedly a good idea from the point of view of security. This way the schema used for establishing a connection to the database has no rights to modify the data accessed by the application. However, it is still not an optimal security solution—everyone who can obtain the database connection credentials embedded in the application source code will be able to issue a SELECT query against the usr.accounts table, thus obtaining access to all application users' accounts stored in this table.

One possible solution to this problem is to create another database schema that will serve as an intermediate point of connection between the above two schemas. In this schema, you might create a set of stored procedures providing secure access to database resources. Graphically, it might look like the following figure.

Using the security solution shown in the figure, you restrict access to database data by allowing users to manipulate it only through the stored procedures that in turn have a restricted set of privileges on the data they manipulate. Residing in a separate database schema, these stored procedures are used to safely access resources stored in another schema. For better security, the schema used for establishing connections has only the rights to execute these stored procedures but not to create or alter them.

You need to execute the following SQL statements to create a new database schema for holding stored procedures that will be used to safely access database data.

CONN /AS sysdba

Next, you create the sec_adm database schema that will be used to perform the security related tasks:

CREATE USER sec_adm IDENTIFIED BY secadm;

You grant the RESOURCE role to the newly created schema so that you can create database resources in it. Note that you should not grant the CONNECT role to this schema because your application will use only the app_conn schema to connect to the database:

GRANT RESOURCE TO sec_adm;

To be able to view the usr.accounts table from sec_adm, you grant the SELECT privilege on this table to sec_adm:

GRANT SELECT ON usr.accounts TO sec_adm;

Since the SELECT privilege on usr.accounts table granted to the app_conn schema is no longer needed, you should revoke it.

REVOKE SELECT ON usr.accounts FROM app_conn;

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 following listing shows how to create the sec_pkg PL/SQL package containing 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 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.

Next, you use the CREATE PACKAGE BODY statement to define the sec_pkg package 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.

As an alternative to using a user-defined record when defining the acc_rec_typ type in the sec_pkg package discussed in the preceding example, you might define that type as usr.accounts%ROWTYPE, which could save you the trouble of needing to know whether the usr.accounts table contains the num_logons column.

The problem with this approach in this example is that you're using the %ROWTYPE attribute with the accounts table that resides in the usr database schema, while the sec_pkg PL/SQL package, which employs %ROWTYPE, resides in a different schema, namely sec_adm. Looking through the %ROWTYPE Attribute section in the PL/SQL User's Guide and Reference Oracle documentation book, you may notice that the syntax of the %ROWTYPE attribute does not imply specifying a database schema name in front of the table (or view) name used with the attribute.

One way to solve this problem is to define a view on the usr.accounts table in the sec_adm schema, and then use the name of that view with %ROWTYPE to declare a record type representing a row in the usr.accounts table. From a security perspective, it is important that the newly created view, while having the same structure as the usr.accounts base table, selects no rows from this table. This can be achieved by specifying, say, 1=0 in the WHERE clause of the view's defining query.

The following listing shows how to create a view on the usr.accounts table in the sec_adm schema, and then rewrite the sec_pkg package created as described in the preceding section so that it uses the name of the newly created view with %ROWTYPE when declaring a record type representing a row in the usr.accounts table.

CONN /AS sysdba
CREATE VIEW sec_adm.accounts_empty_v AS
SELECT * FROM usr.accounts WHERE 1=0;
CREATE OR REPLACE PACKAGE sec_adm.sec_pkg IS
TYPE acc_rec_set IS TABLE OF accounts_empty_v%ROWTYPE ;
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 accounts_empty_v%ROWTYPE;
BEGIN
SELECT * INTO acc_rec FROM usr.accounts WHERE usr_id=usrid;
PIPE ROW(acc_rec);
RETURN;
END;
security, PHP/Oracle applications%ROWTYPE attribute, usingEND sec_pkg;
/

By issuing the CREATE VIEW statement in the above listing, you create an empty view whose structure is the same as the structure of the usr.accounts table. It is important to understand that the only goal of this view is to provide information about the usr.accounts table's structure, rather than providing information stored in the table. So, you specify 1=0 in the WHERE clause of the view's query to make sure that it returns no rows from the base table and, thus, the view is empty.

Another good thing, from a security standpoint, is that you don't have to grant any privileges on the accounts_empty_v view to the app_conn schema through which your application establishes connections to the database. This is because you reference the accounts_empty_v view within the sec_pkg package, which belongs to the same database schema as the view.

In the package specification, you declare a table type of accounts_empty_v%ROWTYPE. Note the use of the accounts_empty_v view that was created earlier in this listing. The table type declared here will be used as the return type of the f_auth package function.

Next, you declare the acc_rec variable of accounts_empty_v%ROWTYPE. You will then use this variable to hold the account information retrieved from the usr.accounts table.

Then you define the SELECT statement that queries the usr.accounts table, inserting appropriate account data into the acc_rec variable.

Now that you have examined the sec_pkg package's code and learned how login credentials are processed within the f_auth function defined in the package, you might want to look at how the example will use it in the authentication process.

However, before you start using the f_auth function defined in the sec_pkg package, you need to grant the EXECUTE privilege on the sec_pkg package to the app_conn database schema so that the f_auth function can be executed from within the applications connecting to the database through that schema. This can be done with the following SQL statement:

CONN /AS sysdba
GRANT EXECUTE ON sec_adm.sec_pkg TO app_conn;

Once you've done all that, you can turn back to the testAuth.php script, making it work with the sec_adm.f_auth function rather than with the usr.accounts table directly.

One problem here is that you cannot simply specify sec_adm.f_auth instead of usr.accounts as the value for the table parameter in the array of options passed to the storage container of the Auth object. This is because the sec_adm.f_function takes the parameter, which is passed to the function when the user submits the login form.

A simple, yet effective solution to the above problem is to create a custom storage container for the Auth class that would work with a table function rather than with a regular database table or view.

In the spirit of code reuse, you don't have to write a storage container from scratch. Instead, you might take advantage of an existing one, customizing it as needed. The Auth PEAR package ships with a number of storage containers, which are implemented as classes and stored in single files in the /Auth/Container directory located within the PEAR directory. It is important to note that the /Auth/Container directory is hard-coded into the Auth class. Therefore, if you want to create a custom storage container, you have to store it in this directory.

As you may recall from Chapter 5 Object-Oriented Approach, PHP allows you to extend existing classes by adding new methods and properties and overriding the existing ones. In this example, you need to extend the class that implements the DB container used by PEAR::Auth, so that the new class can work with a table function that takes a user ID as the parameter, rather than working with a regular table or view.

Examining the files in the /Auth/Container directory, you may find that the DB container is implemented as the Auth_Container_DB class and resides in the Auth/Container/DB.php file. Now, to build a custom class upon Auth_Container_DB, create a new file, say, DB_func.php, in the Auth/Container directory and insert the following code

<?php
//File: DB_func.php
require_once 'Auth/Container/DB.php';
class Auth_Container_DB_func extends Auth_Container_DB
{
function fetchData($username, $password,
$isChallengeResponse=false){
$this->options['table']="TABLE(".
$this->options['table']."('".$username."'))";

return parent::fetchData($username, $password,
$isChallengeResponse=false);
}
}
?>

In the above script, you include the Auth/Container/DB.php file because it contains the Auth_Container_DB class that acts as the parent class for the Auth_Container_DB_func class defined in this script.

Next, you begin the class definition, specifying that the newly created class is built upon Auth_Container_DB.

The only parent class method you override in the Auth_Container_DB_func class is the fetchData method. This method is invoked immediately after a user submits the login form, using the given username to fetch the corresponding authentication information from the database.

Within the fetchData method, you override the table parameter in the array of options passed to the container. In the new value of the table parameter, you include the old value of the parameter, which in turn includes the name of the sec_adm.f_auth function, and specify the username variable as the function's input argument, which in turn is an input parameter of the fetchData method.

Once the table parameter is set up to the new value, you call the fetchData method of the parent class.

Now it's time to put the newly created authentication system into action. Note that the testAuth.php script shown below, which was originally discussed in the PEAR::Auth in Action section in Chapter 5, is modified to support user authentication against the sec_adm.sec_pkg.f_auth table function rather than the usr.accounts table.

<?php
//File: testAuth.php
require_once "Auth.php";
$auth_opts = array(
'dsn'=>'oci8://app_conn:appconn@localhost:1521/orcl',
'table'=>"sec_adm.sec_pkg.f_auth",
'usernamecol'=>'usr_id',
'passwordcol'=>'pswd',
'db_fields' => '*',
'cryptType'=>'none'
);
$auth_opts['usernamecol'] = strtoupper($auth_opts['usernamecol']);
$auth_opts['passwordcol'] = strtoupper($auth_opts['passwordcol']);
$a = new Auth('DB_func', $auth_opts);
$a->setExpire(5);
$a->start();
if ($a->getAuth()) {
print '<font face="Arial">';
print "<h2>Hello, ".$a->getAuthData('FULL_NAME')."!</h2><br/>";
print '</font>';
} else {
exit;
}
?>

In the above script, you specify a table function as the value of the table parameter in the array of options passed to the storage container in the same way you would specify a regular table. In this example, to reference the f_auth function, you use dot notation, specifying the database schema and then the package to which this function belongs.

By specifying DB_func as the first parameter of the Auth's constructor, you tell the Auth object to use the DB_func storage container discussed in the preceding section.

After you have updated the testAuth.php script as shown above, you can run the appPage.php script discussed in the Securing Pages with PEAR::Auth section in Chapter 5 to test the authentication system developed in this section. As in the example from Chapter 5, you may use the bob/pswd username/password combination to get authenticated.

After successful authentication, you will see the secure page produced by the appPage.php script. You may reload this page several times by clicking the here link on it until the authentication session expires (for testing purposes, it is set to 5 seconds in this example). When this happens, you are redirected to the login page again.

As you may conclude, from the end-user point of view, the updated example discussed here behaves exactly like the sample discussed in Chapter 5. What this means in practice is that you can apply security techniques discussed in this section not only when developing a new application but also when improving the security of an existing application.

As you may recall from Chapter 4, if you're establishing a connection to the database from PHP with the oci_connect function, then the connection is automatically closed when the script ends. The fact is that the DB storage container, which is used by the PEAR::Auth object in the sample discussed here, uses the oci_connect function in the long run to connect to the database. What this means is that once the script that performs authentication ends, the database connection used by that script and, thus, the corresponding database session are closed as well.

On the other hand, the _authsession PHP session, which is set up by the PEAR::Auth object to hold the authentication information once a user is successfully authenticated, exists until it expires or the specified amount of idle time has passed.

To handle this problem, you have to transmit the information about the current authenticated user to the database before performing any database-related operations from within PHP. In particular, you have to set appropriate database session variables to the values held in the corresponding variables of the _authsession PHP session, prior to issuing any queries against the database within a script.

The easiest way to pass a user's information to the database is to use PL/SQL packages. The fact is that the state of a package persists for the lifetime of a database session (unless you marked that package SERIALLY_REUSABLE). Moreover, the state of a package is private to each database session, which makes it possible for you to use package variables for keeping information about the current user.

Later in this section we look at how to create two PL/SQL packages that will be used to transmit and hold information about the current authenticated user in the database session. The idea is to have one package to hold the user information and the other to manipulate the variables from the first package, setting them to appropriate values and getting them when necessary.

Thus, this separation of the packages allows for better security. This is because you have to grant the EXECUTE privilege only on the package containing the 'setter' and 'getter' methods to the schema used for establishing connections to the database, while the package holding user information in its package variables will be inaccessible from that schema. This ensures that the only way for users to pass authentication information to the database is to use the 'setter' package procedures.

You start by creating the sec_adm.app_cxt_pkg package as follows:

CONN /as sysdba
CREATE OR REPLACE PACKAGE sec_adm.app_cxt_pkg IS
userid VARCHAR2(40);
END;
/

In this simple case, you define the sec_adm.app_cxt_pkg package containing only one variable, userid, which will hold the name of the application user being connected. Since you are not going to grant any privileges on the sec_adm.app_cxt_pkg package to any other database schema, the userid package variable can be referenced only within database objects belonging to the sec_adm schema.

Next, you create the sec_adm.set_cxt_pkg package as follows:

CREATE OR REPLACE PACKAGE sec_adm.set_cxt_pkg IS
FUNCTION get_userid RETURN VARCHAR2;
PROCEDURE set_userid(usrid VARCHAR2);
END;
/

In the above package, you declare the 'getter' function and the 'setter' procedure, which are then defined in the package body. Note that the function takes no parameter, while the procedure takes only one parameter, namely userid.

Then you create the package body for the sec_adm.set_cxt_pkg package:

CREATE OR REPLACE PACKAGE BODY sec_adm.set_cxt_pkg IS
FUNCTION get_userid RETURN VARCHAR2
AS
BEGIN
RETURN app_cxt_pkg.userid;
END;
PROCEDURE set_userid(usrid VARCHAR2)
AS
BEGIN
IF (SYS_CONTEXT('USERENV', 'IP_ADDRESS') = '127.0.0.1')
THEN
app_cxt_pkg.userid:=usrid;
END IF;
END;
END;
/

The get_userid package function used here simply returns the value of the app_cxt_pkg.userid package variable.

In this example, the app_cxt_pkg.userid package variable is set to the value passed to the set_cxt_pkg.set_userid procedure as the parameter only if the web/PHP server through which the user is being connected to the database resides on the same machine as the database server. To obtain the IP address of the web/PHP server, you use the SYS_CONTEXT function with the built-in USERENV namespace as the first parameter and IP_ADDRESS as the second parameter, which is associated with the USERENV namespace.

Performing this additional security check for callers of the set_cxt_pkg.set_userid procedure allows you to make sure that only users connected through a certain web/PHP server pass authorization.

Finally, you grant the EXECUTE privilege on the sec_adm.set_cxt_pkg package to the app_conn schema, thus making it possible for applications connecting to the database through this schema to call the set_userid procedure and the get_userid function.

GRANT EXECUTE ON sec_adm.set_cxt_pkg TO app_conn;

Once you have created packages to hold and manipulate information about the current authenticated user, the next step is to set up database objects that will use these packages to provide secure access to protected resources.

To be in line with the least privilege principle, which implies that each user should be given only the minimum privileges required to do his or her job and nothing else, you might implement an authorization system so that each user can see only the set of records related to his or her account record in the usr.account table. In the simplest case, you might authorize an authenticated user to access only the record representing his or her account.

Using the following statements, you create a view on a table function that will take the sec_adm.set_cxt_pkg.get_userid getter function as the parameter and retrieve the corresponding row from the usr.accounts table.

CONN /as sysdba
CREATE OR REPLACE VIEW sec_adm.accounts_v AS
SELECT usr_id, full_name
FROM TABLE(sec_adm.sec_pkg.f_auth(sec_adm.set_cxt_pkg.get_userid));
GRANT SELECT ON sec_adm.accounts_v TO app_conn;

You don't have to include all the columns from the base table when creating a view. In this example, you exclude the pswd column from the select list of the view's defining query.

As you can see, the sec_adm.accounts_v view discussed here is based on a table function, rather than a regular table. Specifically, it is based on the sec_adm.sec_pkg.f_auth table function discussed in the preceding section, which returns a row from the usr.accounts table based on the username passed in as the parameter.

Since the sec_adm.accounts_v view is supposed to contain the usr.accounts table's row representing the current authenticated user, it would be a good idea to insert a couple of new records into the usr.accounts table, before you proceed to testing the view.

You could insert rows into usr.accounts as shown below:

INSERT INTO usr.accounts (usr_id, full_name, pswd) VALUES
('maya', 'Maya Silver', 'mayapwd'),
INSERT INTO usr.accounts (usr_id, full_name, pswd) VALUES
('john', 'John Stevenson', 'johnpwd'),
COMMIT;

With that done, you can turn back to the testAuth.php script discussed in the Testing the Authentication System section earlier in this chapter. The testAuthor.php script shown below is a version of testAuth.php revised to use authorization based on the name of the current authenticated user.

<?php
//File: testAuthor.php
require_once "Auth.php";
require_once "DB.php";
$auth_opts = array(
'dsn'=>'oci8://app_conn:appconn@localhost:1521/orcl',
'table'=>"sec_adm.sec_pkg.f_auth",
'usernamecol'=>'usr_id',
'passwordcol'=>'pswd',
'db_fields' => '*',
'cryptType'=>'none'
);
$dbh = DB::connect($auth_opts['dsn']);
if(DB::isError($dbh)) {
die($dbh->getMessage());
}
$auth_opts['dsn'] = $dbh;
$auth_opts['usernamecol'] = strtoupper($auth_opts['usernamecol']);
$auth_opts['passwordcol'] = strtoupper($auth_opts['passwordcol']);
$a = new Auth('DB_func', $auth_opts);
$a->setExpire(5);
$a->start();
if ($a->getAuth()) {
$username= $a->getUsername();
$rslt =$dbh->query("BEGIN sec_adm.set_cxt_pkg.set_userid('".$username."'), END;");
if (PEAR::isError($rslt)) {
print $rslt->getMessage();
}
print '<font face="Arial">';
print "<h2>Hello, ".$a->getAuthData('FULL_NAME')."!</h2><br/>";
print '</font>';
} else {
exit;
}
?>

In the above script, you establish a connection to the database using the connect method of the PEAR::DB class, passing the DSN string defined in the array of options to be passed to the storage container used by the Auth object.

In the DSN string used here, you specify an existing instance of PEAR::DB with the dsn parameter instead of a DSN string.

In this example, you use the getUsername method of the Auth object to obtain the name of the current authenticated user.

Next, you set the sec_adm.set_cxt_pkg.userid package variable to the name of the authenticated user obtained by the getUsername method.

It is interesting to note that the script doesn't stop its execution on failure to set the sec_adm.set_cxt_pkg.userid package variable. This makes sense here, since having the sec_adm.set_cxt_pkg.userid package variable undefined simply means that the sec_adm.accounts_v view defined at the beginning of this section contains no record, and, thus, the user will see no account information on the secure page shown in the following figure:

This secure page displays account information of the current authenticated user. Information is displayed only if the user has been successfully authenticated and the sec_adm.set_cxt_pkg.userid package variable has been successfully set to the user's account name.

To see the page shown in the figure, you should run the appPageInfo.php script shown next, and then enter maya/mayapwd as the username/password combination on the login page.

<?php
//File: appPageInfo.php
require_once "testAuthor.php";
$thisPage='"'."appPageInfo.php".'"';
$rslt =$dbh->query('SELECT * FROM sec_adm.accounts_v'),
if (PEAR::isError($rslt)) {
print $rslt->getMessage();
}
print '<font face="Arial">';
print "<h3>Here's your account information</h3>";
$row = $rslt->fetchRow(DB_FETCHMODE_ASSOC);
print '<table border="1" cellpadding="5">';
print '<tr>';
print '<td><b>Account name</b></td><td>'.$row['USR_ID'].'</td>';
print '</tr>';
print '<tr>';
print '<td><b>Full name</b></td><td>'.$row['FULL_NAME'].'</td >';
print '</tr>';
print '</table>';
print '<br/>';
print "Click <a href=".$thisPage.">here</a> to reload this page";
print '</font>';
?>

Let's walk through exactly what the above script does.

Including testAuthor.php at the beginning of the appPageInfo.php script guarantees that only an authenticated user will see the page produced by appPageInfo.php.

Note the use of the $dbh variable representing the PEAR::DB instance created in the testAuthor.php script discussed earlier in this section. So, you don't need to create another instance of PEAR::DB because testAutor.php has been included in the script.

Then, you fetch the results of the query issued earlier. In this example, you specify DB_FETCHMODE_ASSOC as the fetch mode, which tells the fetchRow method of PEAR::DB to return results as an associative array.

For testing purposes, you include here the link to the same page. In a real-world application, it would be a link to another secure page of the application.

This section discusses how the built-in DECODE function can be used to implement column masking when implementing a fine-grained access with views.

Turning back to the preceding example, suppose you want to add the full_name column from the usr.accounts table to the select list of the of the orders_v view's defining query, masking the values of that column so that each user can see the employee's name only in records representing orders placed by him or her (in other words, the user will see his or her name), while the other records should contain null in this column.

By issuing the following SQL statements, you create the orders_decode_v view whose defining query employs the DECODE function for masking the full_name column derived from the usr.accounts table.

CONN /as sysdba
GRANT EXECUTE ON sec_adm.set_cxt_pkg TO usr;
CREATE OR REPLACE FUNCTION usr.f_get_userid RETURN VARCHAR2 IS
BEGIN
RETURN sec_adm.set_cxt_pkg.get_userid;
END;
/
CREATE OR REPLACE VIEW usr.orders_decode_v AS
SELECT ordno, DECODE(empno, usr.f_get_userid, full_name, NULL) empname, orddate, total
FROM usr.orders, usr.accounts
WHERE empno=usr_id;
GRANT SELECT ON usr.orders_decode_v TO app_conn;

You start by creating a function that will return the account name of the currently authenticated user held in the userid variable of the app_cxt_pkg package in the sec_adm schema.

You create this function in the usr schema so that it can be called from within the orders_decode_v view created in the same schema, when connected to the database through the app_conn schema.

As you can see, the usr.orders_decode_v view is based on two tables, namely usr.orders and usr.accounts. The DECODE function is used in the select list of the view's query to mask values of the full_name column derived from the usr.accounts table.

The following figure illustrates what a secure page based on the usr.orders_decode_v view just created might look like.

While the above page displays all the orders placed by all employees, there is no way the user can determine who exactly placed a certain order unless he or she did it.

The following listing shows the source code for the appPageEmpOrders.php script that might be used to produce the page shown in the above figure.

<?php
//File: appPageEmpOrders.php
require_once "testAuthor.php";
$infoPage='"'."appPageInfo.php".'"';
$rslt =$dbh->query("SELECT ordno, empname, TO_CHAR(orddate,
'DD-MON-YYYY') orddate, total FROM usr.orders_decode_v");
if (PEAR::isError($rslt)) {
print $rslt->getMessage();
}
print '<font face="Arial">';
print "<h3>List of orders placed by all employees</h3>";
print '<table border="1" cellpadding="5">';
print '<tr>';
print '<th>Order No</th><th>Employee</th><th>Order Date</th><th>Order Total</th>';
print '</tr>';
while($row = $rslt->fetchRow(DB_FETCHMODE_ASSOC)){
print '<tr>';
if (is_null($row['EMPNAME'])){$row['EMPNAME']='&nbsp;';}
print '<td>'.$row['ORDNO'].
'</td><td>'.$row['EMPNAME'].'</td><td>'.$row['ORDDATE'].
'</td><td align="right">'.number_format($row['TOTAL'], 2).'</td>';
print '</tr>';
};
print '</table>';
print '<br/>';
print "Click <a href=".$infoPage.">here</a> to see the account
info related to the current user";
print '</font>';
?>

In the above script, you query the usr.orders_decode_v view created earlier in this section. Note that empname in the select list of the query is an alias for the view's select list item built on the DECODE function.