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;
 
Search WWH ::




Custom Search