Database Reference
In-Depth Information
WHEN OTHERS
THEN
appsec.app_sec_pkg.p_log_error( SQLCODE, SQLERRM,
'sys.usr_role_adm_pkg.p_drop_user for ' || username );
END p_drop_user;
PROCEDURE
p_set_proxy_through
(
username sys.proxy_users.client%TYPE,
proxyname sys.proxy_users.proxy%TYPE )
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'ALTER USER ' || username ||
' GRANT CONNECT THROUGH ' || proxyname;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
appsec.app_sec_pkg.p_log_error( SQLCODE, SQLERRM,
'sys.usr_role_adm_pkg.p_set_proxy_through for ' ||
username || '/' || proxyname );
END p_set_proxy_through;
PROCEDURE
p_drop_proxy_through
(
username sys.proxy_users.client%TYPE,
proxyname sys.proxy_users.proxy%TYPE )
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'ALTER USER ' || username ||
' REVOKE CONNECT THROUGH ' || proxyname;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
appsec.app_sec_pkg.p_log_error( SQLCODE, SQLERRM,
'sys.usr_role_adm_pkg.p_drop_proxy_through for ' ||
username || '/' || proxyname );
END p_drop_proxy_through;
END usr_role_adm_pkg;
/
GRANT EXECUTE ON sys.usr_role_adm_pkg TO ojs_adm_admin;
In order to grant and revoke privilege to access specific applications, we need to execute commands
to
ALTER USER
and either
GRANT
or
REVOKE CONNECT THROUGH
the specific application proxy user that is
used for the application. Those steps are accomplished by the
p_set_proxy_through
and
p_drop_proxy_through
procedures.
In each of these procedures, we concatenate a SQL command that represents what we want to
accomplish and call
EXECUTE IMMEDIATE
, which is extremely handy for executing dynamic data definition