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

OR SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ) = (
SELECT GRANTEE FROM SYS.DBA_TAB_PRIVS
WHERE TABLE_NAME='V_APPLICATION_ADMINS'
AND OWNER='APPSEC'
AND PRIVILEGE='UPDATE'
AND GRANTEE=SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' )));
END p_get_class_conns;
PROCEDURE p_set_class_conns (
m_class_name v_app_conn_registry.class_name%TYPE,
m_class_version v_app_conn_registry.class_version%TYPE,
m_class_instance v_app_conn_registry.class_instance%TYPE,
m_connections v_app_conn_registry.connections%TYPE )
IS
v_count INTEGER;
v_count_able INTEGER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM appsec.v_app_conn_registry
WHERE class_name = m_class_name
AND class_version = m_class_version;
SELECT COUNT(*) INTO v_count_able
FROM appsec.v_app_conn_registry
WHERE class_name = m_class_name
AND class_version = m_class_version
AND class_name IN (
SELECT class_name FROM appsec.v_application_admins
WHERE user_id = SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' )
OR SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ) = (
SELECT GRANTEE FROM SYS.DBA_TAB_PRIVS
WHERE TABLE_NAME='V_APPLICATION_ADMINS'
AND OWNER='APPSEC'
AND PRIVILEGE='UPDATE'
AND GRANTEE=SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' )));
IF v_count = 0 THEN
INSERT INTO v_app_conn_registry ( class_name, class_version,
class_instance, connections ) VALUES
( m_class_name, m_class_version, m_class_instance, m_connections );
ELSE
IF v_count_able > 0 THEN
UPDATE v_app_conn_registry
SET class_instance = m_class_instance,
connections = m_connections, update_dt = SYSDATE
WHERE class_name = m_class_name
AND class_version = m_class_version;
END IF;