Database Reference
In-Depth Information
GRANT SELECT ON ojsaadm.instance_proxy_users TO ojs_adm_admin;
-- To let appsec read view across database link
GRANT SELECT ON ojsaadm.instance_proxy_users TO appsec;
The third command in Listing 12-29 creates a view that will bring together the results of a select on
sys.proxy_users
from both the
orcl
and
apver
instances. This view is a
UNION
of the results of two
queries, one from each instance, and includes a prefix column indicating the instance on which the
proxy was observed. Test the new view by selecting from it—this will also be a test of the grant on
apver
for
ojsaadm
to select from
sys.proxy_users
. We will grant select on this view to all users who proxy
through
ojsaadm
and acquire the
ojs_adm_admin
role.
The last command in Listing 12-29 provides one more avenue of access. You see, sometimes we
need to be able to see all the proxy users on the
orcl
instance from procedures running on the
apver
instance. On
apver
, the
appsec
user already has a link to the
orcl
instance. That link was set up initially
so that application security,
appsec
user could do two-factor authentication by selecting from
hr.v_emp_mobile_nos
on
orcl
. Here we are extending the functionality of the
appsec
private database
link from
apver
to
orcl
by allowing it to select all the proxy users from our new view. This also saves us
from having to grant
SELECT
on
sys.proxy_users
to
appsec
on the
apver
instance; she will be selecting
from
proxy_users
on
apver
by selecting the view on
orcl
which selects from
proxy_users
across a link to
apver
. If this sounds circular, that's because it is.
Well, as we have observed, selecting from
sys.proxy_users
does not meet the goals of our plan to
manage proxy grants. From the Admin Users screen, we want to be able to grant and revoke the privilege
to proxy through application users on both the
orcl
and
apver
instances. We also want to do that from a
single Oracle connection, if possible. Is it possible to do this across a database link? It certainly is. In
Listing 12-30 we see the definition of a package for just this purpose,
ojsaadm.apver_usr_adm_pkg
. We
will create the package in the
ojsaadm
schema, and the package will have access through the private
database link,
apver_link
to execute procedures on the
apver
instance.
Please notice in Listing 12-30 that we are calling the procedures across the link with a
parameterized,
EXECUTE IMMEDIATE
statement. Our PL/SQL style call is defined in the
m_stmt
string. For
the variable (e.g., user name) we don't concatenate it with the statement string; rather, we have a
placeholder for it,
:1
. We call that statement string from an
EXECUTE IMMEDIATE
command, and pass the
username in to fill the placeholder by specifying
USING username
. This is another form of parameterized
statement that is also impervious to SQL Injection like stored procedures and prepared statements in
Java, which we discussed in the section called “Avoid SQL Injection”
in Chapter 7.
Listing 12-30.
Package Definition for Executing Procedures Across a Link
CREATE OR REPLACE PACKAGE BODY ojsaadm.apver_usr_adm_pkg IS
PROCEDURE
p_create_apver_user
( username VARCHAR2 )
AS
m_stmt VARCHAR2(100);
BEGIN
m_stmt := 'BEGIN sys.usr_role_adm_pkg.
p_create_user_once@apver_link
( :1 ); END;';
EXECUTE IMMEDIATE m_stmt USING username;
m_stmt := 'BEGIN sys.usr_role_adm_pkg.
p_create_user_many@apver_link
( :1 ); END;';
EXECUTE IMMEDIATE m_stmt USING username;
END p_create_apver_user;
PROCEDURE
p_drop_apver_user
( username VARCHAR2 )
