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

Listing 12-49. Application Verification Connections Role
CREATE ROLE appver_conns NOT IDENTIFIED;
GRANT EXECUTE ON appsec.appsec_admin_pkg TO appver_conns ;
GRANT appver_conns TO osadmin;
Notice that we grant appver_conns to the osadmin user. We will make that same grant to all
application administrators who will be maintaining the connection strings for their applications through
this security administration interface. This gives them access to the appsec_admin_pkg , which they will
need to update connection strings.
When we add administrators, we will need to grant both the privilege to connect through avadmin ,
and the grant to appver_conns role. For this reason, we have additional procedures in a package on apver
instance named sys.appver_conns_role_pkg . The package code is given in Listing 12-50. There are two
procedures, p_grant_appver_conns_role and p_revoke_appver_conns_role for both granting and
revoking appver_conns .
Listing 12-50. Grant Appver Conns Role on Apver Instance
CREATE OR REPLACE PACKAGE BODY sys.appver_conns_role_pkg IS
PROCEDURE p_grant_appver_conns_role (
username sys.proxy_users.client%TYPE )
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE ' GRANT appver_conns TO ' || username;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
appsec.app_sec_pkg.p_log_error( SQLCODE, SQLERRM,
'sys.p_grant_appver_conns_role for ' ||
username );
END p_grant_appver_conns_role;
PROCEDURE p_revoke_appver_conns_role (
username sys.proxy_users.client%TYPE )
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE ' REVOKE appver_conns FROM ' || username;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
appsec.app_sec_pkg.p_log_error( SQLCODE, SQLERRM,
'sys.p_revoke_appver_conns_role for ' ||
username );
END p_revoke_appver_conns_role;