Database Reference
In-Depth Information
already exists and was created with a password, he will not be able to use our applications unless he is
able to proxy through appver . Notice that when we create new users strictly for application access, we do
not give them a password; rather, we direct that they are IDENTIFIED EXTERNALLY . Usually, that would
mean that we use the OS or a naming service to authenticate the user, but in our case, we authenticate
our one, big application user ( appver ) and allow this IDENTIFIED EXTERNALLY user to proxy through. No
authentication for the individual person Oracle user ever occurs.
There is also a procedure for revoking user access to our applications, p_drop_user . This name is a
bit of an overstatement, because we do not actually drop the Oracle user account. That account may
exist for some reason other than to access our applications. It may even have an associated password
and allow the user to use the Oracle database in her own schema. All we really want to accomplish is to
keep the user from accessing our applications, and we can do that by simply revoking the privilege to
proxy through the appver user. That is all the p_drop_user procedure does.
Listing 12-28. Create the sys.usr_role_adm_pkg Package
CREATE OR REPLACE PACKAGE BODY sys.usr_role_adm_pkg IS
PROCEDURE p_create_user_once ( username sys.proxy_users.client%TYPE )
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE ' CREATE USER ' || username || ' IDENTIFIED EXTERNALLY ';
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
appsec.app_sec_pkg.p_log_error( SQLCODE, SQLERRM,
'sys.usr_role_adm_pkg.p_create_user_once for ' || username );
END p_create_user_once;
PROCEDURE p_create_user_many ( username sys.proxy_users.client%TYPE )
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'GRANT create_session_role TO ' || username;
EXECUTE IMMEDIATE 'ALTER USER ' || username || ' GRANT CONNECT THROUGH appver';
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
appsec.app_sec_pkg.p_log_error( SQLCODE, SQLERRM,
'sys.usr_role_adm_pkg.p_create_user_many for ' || username );
END p_create_user_many;
PROCEDURE p_drop_user ( username sys.proxy_users.client%TYPE )
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'ALTER USER ' || username || ' REVOKE CONNECT THROUGH appver';
COMMIT;
EXCEPTION
 
Search WWH ::




Custom Search