Database Reference
In-Depth Information
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