Database Reference
In-Depth Information
language (DDL) statements like
CREATE USER
and
ALTER USER
. All of these procedures are defined with the
modifier
PRAGMA AUTONOMOUS_TRANSACTION
. That allows each procedure to commit its own transaction.
The question arises, “why didn't we just grant the system privileges to
CREATE USER
and
ALTER USER
to
ojsaadm
?” First, we would still want to wrap these commands in procedures to control the parameters
and avoid SQL injection attacks. And second, we want to limit what
ojsaadm
can actually do. Notice that
in these procedures,
ojsaadm
can only create users that are
IDENTIFIED EXTERNALLY
. Beyond that, he can
only grant and revoke the privilege to proxy through other users—that is much more limited ability than
the grant of
ALTER USER
provides.
I mentioned that an identical package is to be created on the
apver
instance. The script for that is in
the file
Chapter12/ApverSys.sql
. There is one difference between the instances in this regard. On the
orcl
instance, we grant execute on
usr_role_adm_pkg
to the
ojs_adm_admin
role; while on the
apver
instance,
we grant it directly to the
ojsaadm
user. Now the challenge for us is in how we will execute the commands
on both database instances at one time. We will do this across a database link.
Enable the OJSAAdm User Across a Database Link
Connect as the
ojsaadm
user to the
orcl
instance. While there, you can test your access to the
sys.proxy_users
data dictionary view. That command is shown first in Listing 12-29. The second
command in Listing 12-29 is the command to create the database link. Change the password
placeholder to the password you used to create
ojsaadm
on the
apver
instance.
Caution
Take a moment to assure that the password for
ojsaadm
on the
apver
instance in particular is very
complex. We have opened another avenue of attack on our perimeter by creating another user on the
apver
instance. With a very strong password (for example 15 random characters, mixed case with numeric and special
characters) we can shore up our defenses. This is a password that will be used in the database link, but rarely
typed in. We would prefer to disable the password for
ojsaadm
or to create a login trigger that denies him access,
but in order to use our database link,
ojsaadm
needs to be able to log in on the
apver
instance.
Listing 12-29.
Create Link as OJSAAdm User
SELECT * FROM sys.proxy_users;
-- Private database link to apver as ojsaadm
CREATE DATABASE LINK apver_link
CONNECT TO ojsaadm
IDENTIFIED BY password
USING 'apver';
CREATE OR REPLACE VIEW ojsaadm.instance_proxy_users AS
SELECT 'APVER' INSTANCE, proxy, client FROM sys.proxy_users@apver_link
UNION
SELECT 'ORCL' INSTANCE, proxy, client FROM sys.proxy_users;
-- Test the link and view
SELECT * FROM ojsaadm.instance_proxy_users;