Rem drv: <create type="pkgbodys" pos="crypt/crypt_pkgbody.plb+"/>
Rem
Rem $Header: audit_admin_pkgbody.sql 18-may-2007.12:27:16 pshishir Exp $
Rem
Rem audit_admin_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      audit_admin_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    pshishir    05/10/07 - Fixing bug 5996233 
Rem    gsbhatia    07/23/05 - Fix repmgr header 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    shianand    03/01/05 - Added Error Codes to MGMT_GLOBAL 
Rem    shianand    02/03/05 - shianand_audit
Rem    shianand    12/13/04 - Created
Rem



CREATE OR REPLACE PACKAGE BODY MGMT_AUDIT_ADMIN IS

--global audit level is set to off (0=>all/1=>selected/2=>none)
g_audit_level NUMBER(1) := AUDIT_LEVEL_NONE;


--user_session_id_guid stores the guid generated for each http session
user_session_id_guid RAW(64) :=NULL;


--The following Procedure sets the audit on or off with the data storage 
--information - DB or file
PROCEDURE set_audit (p_audit_mode        IN NUMBER DEFAULT AUDIT_MODE_DB,
                     p_audit_destination IN VARCHAR2 DEFAULT NULL,
                     p_audit_level       IN NUMBER DEFAULT AUDIT_LEVEL_NONE)
IS

BEGIN
    IF(p_audit_mode >=AUDIT_MODE_DB AND p_audit_mode <=AUDIT_MODE_DB_FILE) THEN
        IF(p_audit_mode =AUDIT_MODE_DB AND p_audit_destination IS NOT NULL) THEN
           raise_application_error(MGMT_GLOBAL.INVALID_AUDIT_MODE_ERR, 'audit_mode: '
                                   ||to_char(p_audit_mode)||' set for DB');
        ELSIF(p_audit_mode !=AUDIT_MODE_DB AND p_audit_destination IS NULL) THEN
              raise_application_error(MGMT_GLOBAL.INVALID_AUDIT_FILE_PATH_ERR,
                                      'file path is missing ');
              --file path IS needed IN case audit mode is set to file and file/db
        ELSE
            IF(p_audit_level >=AUDIT_LEVEL_ALL AND p_audit_level <=AUDIT_LEVEL_NONE) THEN
               UPDATE MGMT_AUDIT_MASTER 
                 SET audit_level = p_audit_level;

               UPDATE MGMT_AUDIT_DESTINATION 
                 SET audit_mode = p_audit_mode,
                 audit_destination = p_audit_destination;

            ELSE
               raise_application_error(MGMT_GLOBAL.INVALID_AUDIT_LEVEL_ERR, 'audit_level: '
                                       || to_char(p_audit_level)||' invald');
            END IF;
        END IF;
    ELSE
        raise_application_error(MGMT_GLOBAL.INVALID_AUDIT_MODE_ERR, 'audit_mode: '||
                                to_char(p_audit_mode)||' invalid');
    END IF;
END set_audit;



--This Procedure registers a new operation code to include that operation for 
--auditing. 
PROCEDURE register_operation_code (p_op_code               IN NUMBER,
                                   p_operation_description IN VARCHAR2,
                                   p_operation_audit       IN NUMBER   DEFAULT OPERATION_AUDIT_OFF,
                                   p_audit_column_name1    IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name2    IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name3    IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name4    IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name5    IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name6    IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name7    IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name8    IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name9    IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name10   IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name11   IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name12   IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name13   IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name14   IN VARCHAR2 DEFAULT NULL,
                                   p_audit_column_name15   IN VARCHAR2 DEFAULT NULL,
                                   p_audit_clob_name1      IN VARCHAR2 DEFAULT NULL)
IS
l_op_code NUMBER(2);

BEGIN
    IF(p_op_code >=1) THEN
        SELECT op_code 
          INTO l_op_code
          FROM MGMT_OPERATIONS_MASTER
          WHERE op_code = p_op_code;
        raise_application_error(MGMT_GLOBAL.FOUND_AUDIT_OP_CODE_ERR, 'op_code: '
                                ||to_char(p_op_code)||' already exists');
    ELSE
        raise_application_error(MGMT_GLOBAL.INVALID_AUDIT_OP_CODE_ERR, 'op_code: '
                                ||to_char(p_op_code)||' not greater than 1');
    END IF;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            IF (p_operation_audit = OPERATION_AUDIT_ON OR p_operation_audit = OPERATION_AUDIT_OFF) THEN 
                insert into MGMT_OPERATIONS_MASTER
                (op_code, operation_description, operation_audit,
                 audit_column_name1, audit_column_name2, audit_column_name3,
                 audit_column_name4, audit_column_name5, audit_column_name6,
                 audit_column_name7, audit_column_name8, audit_column_name9,
                 audit_column_name10, audit_column_name11, audit_column_name12,
                 audit_column_name13, audit_column_name14, audit_column_name15,
                 audit_clob_name1)
                values 
                (p_op_code, p_operation_description, p_operation_audit,
                 p_audit_column_name1, p_audit_column_name2, p_audit_column_name3,
                 p_audit_column_name4, p_audit_column_name5, p_audit_column_name6,
                 p_audit_column_name7, p_audit_column_name8, p_audit_column_name9,
                 p_audit_column_name10, p_audit_column_name11, p_audit_column_name12,
                 p_audit_column_name13, p_audit_column_name14, p_audit_column_name15,
                 p_audit_clob_name1);
            ELSE
                raise_application_error(MGMT_GLOBAL.INVALID_AUDIT_OPERATION_ERR, 
                                        'operation_audit: '||to_char(p_operation_audit)||
                                        ' invalid');
            END IF;
END register_operation_code;



--This Procedure unregisters an operation code to exclude that operation from 
--auditing.
PROCEDURE unregister_operation_code (p_op_code IN NUMBER)
IS
l_op_code NUMBER(2);

BEGIN
    IF(p_op_code >=1) THEN
       DELETE FROM MGMT_OPERATIONS_MASTER
         WHERE op_code = p_op_code;

       IF(SQL%ROWCOUNT > 0) THEN
          RETURN;
       ELSE
          raise_application_error(MGMT_GLOBAL.NOT_FOUND_AUDIT_OP_CODE_ERR, 'op_code: '
                                  ||to_char(p_op_code)||' doesnot  exists');
       END IF;
    ELSE
        raise_application_error(MGMT_GLOBAL.INVALID_AUDIT_OP_CODE_ERR, 'op_code: '
                                ||to_char(p_op_code)||' not greater than 1');
    END IF;
END unregister_operation_code;



--When the global audit flag is AUDIT_LEVEL_SELECTED, then this Procedure 
--sets the audit flag on for a particular operation
PROCEDURE set_audit_on (p_op_code IN NUMBER)
IS
l_audit_level     NUMBER(1);
l_operation_audit NUMBER(1);
err_num           NUMBER;
err_msg           VARCHAR2(256);

BEGIN
   UPDATE MGMT_OPERATIONS_MASTER 
     SET operation_audit = OPERATION_AUDIT_ON
     WHERE op_code = p_op_code;

   IF(SQL%ROWCOUNT > 0) THEN
      RETURN;
   ELSE
      raise_application_error(MGMT_GLOBAL.NOT_FOUND_AUDIT_OP_CODE_ERR, 'op_code: '
                              ||to_char(p_op_code)||' not found');
   END IF;
   EXCEPTION
      WHEN OTHERS THEN
          err_num := SQLCODE;
          err_msg := SUBSTR(SQLERRM, 1, 256);
          raise_application_error(err_num, err_msg);
END set_audit_on;



--When the global audit flag is AUDIT_LEVEL_SELECTED, then this Procedure 
--unsets the audit flag on for a particular operation
PROCEDURE set_audit_off (p_op_code IN NUMBER) 
IS
l_operation_audit NUMBER(1);
err_num           NUMBER;
err_msg           VARCHAR2(256);

BEGIN
   UPDATE MGMT_OPERATIONS_MASTER 
     SET operation_audit = OPERATION_AUDIT_OFF
     WHERE op_code = p_op_code;

   IF(SQL%ROWCOUNT > 0) THEN
      RETURN;
   ELSE
      raise_application_error(MGMT_GLOBAL.NOT_FOUND_AUDIT_OP_CODE_ERR, 'op_code: '
                              ||to_char(p_op_code)||' not found');
   END IF;
   EXCEPTION
      WHEN OTHERS THEN
          err_num := SQLCODE;
          err_msg := SUBSTR(SQLERRM, 1, 256);
          raise_application_error(err_num, err_msg);
END set_audit_off;



--This procedure purges all the records from audit table, currently the purge 
--can be used by SYSMAN but it may change in the future. User with admin 
--priviledge to use this package can purge the audit records which will be other
--than SYSMAN.
PROCEDURE audit_purge (p_time_stamp IN DATE := sysdate+1) 
IS
err_num           NUMBER;
err_msg           VARCHAR2(256);

BEGIN
   DELETE FROM MGMT_AUDIT_LOGS
     WHERE time_stamp < p_time_stamp;
   DELETE FROM MGMT_USER_SESSION
     WHERE login_time < p_time_stamp;
   EXCEPTION
       WHEN OTHERS THEN
          err_num := SQLCODE;
          err_msg := SUBSTR(SQLERRM, 1, 256);
          raise_application_error(err_num, err_msg);
END audit_purge;



--This sets the audit_level when this package is called for the fisrt time and 
--stores the audit_level value in the package variable g_audit_level
PROCEDURE set_audit_level 
IS 
l_audit_level NUMBER(1);

BEGIN
    SELECT audit_level 
      INTO l_audit_level
      FROM MGMT_AUDIT_MASTER;

    g_audit_level := l_audit_level;
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            g_audit_level :=AUDIT_LEVEL_NONE;
END set_audit_level;


--This procedure returns the value of global audit flag as on or off or 
--selected, which is stored as package variable
PROCEDURE audit_level (p_audit_level OUT NUMBER)
IS

BEGIN
   p_audit_level := g_audit_level;
END audit_level;




--This procedure generates and stores the user session id guid into package 
--variable.
PROCEDURE gen_user_session_id_guid (p_user_session_id_guid OUT RAW) 
IS

BEGIN
    p_user_session_id_guid := sys_guid();
END gen_user_session_id_guid;


--This procedure sets the user session id guid into the package variable.
PROCEDURE set_user_session_id_guid (p_user_session_id_guid IN RAW) 
IS
BEGIN
    user_session_id_guid := p_user_session_id_guid;
END set_user_session_id_guid;


--This function returns the user session id guid from package variable.
FUNCTION get_user_session_id_guid RETURN RAW 
IS
BEGIN
   RETURN  user_session_id_guid;
END get_user_session_id_guid;



--This Procedure sets the user session information into mgmt_user_session table
PROCEDURE set_user_info(p_user_session_id_guid  IN RAW,
                        p_user_session_id       IN RAW,
                        p_em_user               IN VARCHAR2 DEFAULT NULL,
                        p_em_user_type          IN VARCHAR2 DEFAULT NULL,
                        p_em_user_host_name     IN VARCHAR2 DEFAULT NULL,
                        p_oms_host_name         IN VARCHAR2 DEFAULT NULL,
                        p_browser_type          IN VARCHAR2 DEFAULT NULL,
                        p_login_time            IN DATE DEFAULT NULL,
                        p_ip_address            IN VARCHAR2 DEFAULT NULL,
                        p_session_status        IN VARCHAR2 DEFAULT NULL,
                        p_session_type          IN VARCHAR2 DEFAULT NULL,
                        p_time_zone             IN VARCHAR2 DEFAULT NULL)
IS

BEGIN
    INSERT INTO MGMT_USER_SESSION (user_session_id_guid,
                                   user_session_id,
                                   em_user,
                                   em_user_type,
                                   em_user_host_name,
                                   oms_host_name,
                                   browser_type,
                                   login_time,
                                   ip_address,
                                   session_status,
                                   session_type,
                                   time_zone)
    VALUES (p_user_session_id_guid,
            p_user_session_id,
            p_em_user,
            p_em_user_type,
            p_em_user_host_name,
            p_oms_host_name,
            p_browser_type,
            sysdate,
            p_ip_address,
            p_session_status,
            p_session_type,
            p_time_zone);

    EXCEPTION
        WHEN OTHERS THEN
          RETURN;
   
END set_user_info;



--This Procedure updates the user record in mgmt_user_session table with logoff
--information
PROCEDURE update_user_info(p_user_session_id_guid IN RAW,
                           p_logoff_time          IN DATE DEFAULT NULL,
                           p_session_status       IN VARCHAR2 DEFAULT NULL)
IS

BEGIN
   UPDATE MGMT_USER_SESSION
     SET logoff_time = sysdate,
         session_status = p_session_status
     WHERE user_session_id_guid = p_user_session_id_guid;

   EXCEPTION
        WHEN OTHERS THEN
          RETURN;
END update_user_info;

--This Procedure updates the user record in mgmt_user_session table with custom attributes
--information

PROCEDURE update_ca_for_user(
    p_user_session_id_guid IN RAW,
    p_ca_name_1          IN   VARCHAR2 DEFAULT NULL,
    p_ca_value_1         IN   VARCHAR2 DEFAULT NULL,
    p_ca_name_2          IN   VARCHAR2 DEFAULT NULL,
    p_ca_value_2         IN   VARCHAR2 DEFAULT NULL,
    p_ca_name_3          IN   VARCHAR2 DEFAULT NULL,
    p_ca_value_3         IN   VARCHAR2 DEFAULT NULL)
IS

BEGIN
   IF(p_user_session_id_guid IS NOT NULL) THEN
       UPDATE MGMT_USER_SESSION
          SET ca_name_1  = p_ca_name_1,
              ca_value_1 = p_ca_value_1,
              ca_name_2  = p_ca_name_2,
              ca_value_2 = p_ca_value_2,
              ca_name_3  = p_ca_name_3,
              ca_value_3 = p_ca_value_3
          WHERE user_session_id_guid = p_user_session_id_guid;
     END IF;
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END update_ca_for_user;

---------------------------------------------------------------
--    Registration and DeRestration APIs for Custom attribute
---------------------------------------------------------------

-- This procedure is to be used by the user to register custom attributes 
-- with Audit framework. This procedure throws a DUPLICATE_AUDIT_OBJECT
-- exception, if the custom attribute name being registered already exists.

PROCEDURE register_audit_custom_attrib(
    p_ca_name           IN VARCHAR2,
    p_ca_display_name   IN VARCHAR2  DEFAULT NULL,
    p_ca_description    IN VARCHAR2  DEFAULT NULL,
    p_ca_required       IN NUMBER    DEFAULT G_CA_NOT_REQUIRED)
IS
l_count NUMBER(2) := 0;
l_ca_display_name MGMT_AUDIT_CUSTOM_ATTRIBS.ca_display_name%TYPE;

BEGIN
    -- Check for not null
    EM_CHECK.check_not_null(p_ca_name, 'Audit Custom attribute Name');

    IF(p_ca_display_name IS NOT NULL) THEN 
        l_ca_display_name := p_ca_display_name;
    ELSE
        l_ca_display_name := p_ca_name;
    END IF;

    EM_CHECK.check_range(p_value   => p_ca_required,
                         p_min_value => G_CA_NOT_REQUIRED,
                         p_max_value => G_CA_REQUIRED,
                         p_param_name => 'Audit custom attribute required field',
                         p_error_msg => 'Value should be either 0 or 1');

    -- Check whether the maximimum number of CA (which is 3) is registered or not
    SELECT count(*)
      INTO l_count
      FROM MGMT_AUDIT_CUSTOM_ATTRIBS;

    IF(l_count < G_MAX_CA_ALLOWED) THEN
        -- Insert in audit custom attribute
        INSERT INTO MGMT_AUDIT_CUSTOM_ATTRIBS(
                                   ca_name,
                                   ca_display_name,
                                   ca_description,
                                   ca_required)
                            VALUES(upper(p_ca_name),
                                   p_ca_display_name,
                                   p_ca_description,
                                   p_ca_required);
    ELSE
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
        'Audit Custom Attribute ' ||p_ca_name||' cannot be registered 
         because maximum number of custom attributes,which is 3, are already registered');

    END IF;
EXCEPTION   
    WHEN DUP_VAL_ON_INDEX THEN  
        raise_application_error(MGMT_GLOBAL.DUPLICATE_AUD_OBJ_ERR,
        'Audit Custom Attribute: '||p_ca_name||' already exists');
END;

-- This procedure is to be used by the user to register custom attributes 
-- with Audit framework. This procedure throws a DUPLICATE_AUDIT_OBJECT
-- exception, if the custom attribute name being registered already exists.

PROCEDURE register_audit_custom_attribs(
    p_ca_array  IN MGMT_AUDIT_CUSTOM_ATTRIB_ARRAY)
IS
BEGIN
    -- Call the internal API to register audit custom attributes
    IF (p_ca_array IS NOT NULL) THEN
        FOR j IN 1..p_ca_array.COUNT
          LOOP
            register_audit_custom_attrib(
                                 p_ca_array(j).ca_name,
                                 p_ca_array(j).ca_display_name,
                                 p_ca_array(j).ca_description,
                                 p_ca_array(j).ca_required);
          END LOOP;
    END IF;
END;

-- This procedure is to be used by the user to deregister custom attributes 
-- from Audit framework. This procedure throws a AUDIT_OBJECT_DOES_NOT_EXIST
-- exception, if the custom attribute name being registered not found.

PROCEDURE deregister_audit_custom_attrib( p_ca_name IN VARCHAR2)
IS
BEGIN
    -- Check for not null
    EM_CHECK.check_not_null(p_ca_name, 'Audit Custom attribute Name');

    -- delete audit custom attribute
    DELETE FROM MGMT_AUDIT_CUSTOM_ATTRIBS
    WHERE ca_name = upper(p_ca_name);

    IF SQL%ROWCOUNT=0 THEN
        raise_application_error(MGMT_GLOBAL.AUD_OBJ_DOES_NOT_EXIST_ERR,
        'Audit Object Class: '||p_ca_name||' not found');
    END IF;
END;

-- This procedure is to be used by the user to get custom attributes 
-- which has been registered with Audit framework. This procedure also
-- return the total count of audit custom attributes registered with Audit
-- Framework.

PROCEDURE get_audit_custom_attribs(
    p_ca_array  OUT MGMT_AUDIT_CUSTOM_ATTRIB_ARRAY,
    p_ca_count  OUT NUMBER)
IS
BEGIN
    SELECT count(*)
      INTO p_ca_count
      FROM MGMT_AUDIT_CUSTOM_ATTRIBS;

    IF(p_ca_count > 0) THEN
      SELECT MGMT_AUDIT_CUSTOM_ATTRIB_OBJ(
             ca_name, ca_display_name, ca_description, ca_required)
       BULK COLLECT
       INTO p_ca_array
       FROM MGMT_AUDIT_CUSTOM_ATTRIBS;
    ELSE
       p_ca_array := NULL;
    END IF;
END;


BEGIN

   set_audit_level;

END MGMT_AUDIT_ADMIN;

/
show errors;