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




Custom Search