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
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.