Administration of Security - Expert Oracle and Java Security - page 353

GRANT CREATE VIEW TO ojsaadm;
GRANT CREATE PROCEDURE TO ojsaadm;
 Note These commands are listed in the file Chapter12/OrclSecAdm.sql .
In addition to granting our new user the privilege to select from the HR views to populate the users
list, you can see in listing 12-26 that we are giving this user some additional privileges. We are granting
ojsaadm the system privileges to create database links, views, and procedures. We are going to put this
Oracle user account to work. In fact, we are enlisting this Oracle user to talk across a database link from
the orcl to the apver instance to accomplish user administration on both sides. That being said, we need
to create an identical user on the apver instance. Listing 12-27 shows the script we will use to accomplish
that. As usual, we are connecting as the SYS user to accomplish most of our work on the apver instance.
Listing 12-27. Create the OJSAAdm User on the apver Instance
GRANT create_session_role TO ojsaadm IDENTIFIED BY password;
GRANT SELECT ON sys.proxy_users TO ojsaadm;
 Note The script that is partly shown in Listing 12-27 is included in the file Chapter12/ApverSys.sql .
From what we have described for the functions of the Admin Users screen, you might imagine that
this user needs to read some unusual data from the Oracle database in order to accomplish his tasks.
One thing ojsaadm needs to do is to read from the data dictionary view, sys.proxy_users . It is from that
view that we will observe the avenue of access from the Oracle user through whom our OS user is
matched by SSO, to the application role which is aligned with the Oracle user acting as the proxy. By
adding or removing the ability to proxy through one of our application users, the individual person user
is able or not to access the application.
Not just any Oracle user can modify data in the sys.proxy_users view. In Listing 12-27, you see that
we granted SELECT on that view to the ojsaadm user on the apver instance. We are going to make the same
grant to ojsaadm on the orcl instance. It is not sufficient to accomplish our plan to just have SELECT
privilege on that view. To grant and revoke proxy privileges, we need the ALTER USER privilege.
Additionally, as I described at the end of our discussion of the Add/Modify User screen, we need the
ability to CREATE USER at the time we grant them their first application proxy. To do this, we will create a
package in the SYS schema called usr_role_adm_pkg with procedures that will create users and grant and
revoke proxy privileges. On the orcl instance, the script to create this package is shown in Listing 12-28.
An identical package will be created on the apver instance.
In the usr_role_adm_pkg package definition in Listing 12-28, you can see five procedures are defined.
There are two procedures required to create a user, p_create_user_once and p_create_user_many . We
have split that process up into two procedures because the first, where we actually create the Oracle
user, will experience an exception if the user already exists. The second procedure, p_create_user_many ,
is also part of the process to create an Oracle user account for application access. In that procedure, we
grant the create_session_role to the user and grant him the permission to proxy through the appver
user. That proxy is required in order to do SSO and two-factor authentication. Even if an Oracle user