<< Click to Display Table of Contents >> RayVentory Scan Engine > 12.6 u4 > User Guide > Appendix I: Prerequisites Inventory Methods > Zero-Touch/Remote Inventory for Oracle > Required Permission to Run a Zero-Touch Oracle Inventory  set_user_acl12.sql

-- ACL for Oracle DB 12

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE

 sUsername         VARCHAR2(50) := 'RVUSER';           -- Basis Username, if Container, script set C## in Front or the defined Value

 sCommonUserPrefix VARCHAR2(50) := '';                 -- Temporary Nedded, used to Store the defined User Prefix if Container Database

 c INT;

 v_count12 NUMBER;

 compile_error exception;

pragma exception_init(compile_error, -06550);

BEGIN

BEGIN

   DBMS_OUTPUT.put_line('- Check if CDB Database User needed');

   EXECUTE IMMEDIATE '

     DECLARE

       c INT;

       exCustom EXCEPTION;

       PRAGMA EXCEPTION_INIT(exCustom, -20001);

   BEGIN

       SELECT COUNT(*) INTO c FROM V$DATABASE WHERE CDB=''YES'';

       IF c = 0 THEN

         DBMS_OUTPUT.put_line(''-- CDB Database: NO'');

         raise_application_error(-20001, ''CDB Database: NO'');

       END IF;

       DBMS_OUTPUT.put_line(''-- CDB Database: YES'');

   END;

   ';

 -- It looks like it is a Server with Container Database.

 -- Get Parameter common_user_prefix if it is set, store it in sCommonUserPrefix

  SELECT VALUE INTO sCommonUserPrefix FROM V$PARAMETER WHERE NAME='common_user_prefix';

 -- If Parameter common_user_prefix is not set, set in the Variable sCommonUserPrefix the Oracle default Value C##

 IF sCommonUserPrefix IS NULL THEN

     sCommonUserPrefix := 'C##';

     DBMS_OUTPUT.put_line('-- No common_user_prefix is set, use default C##');

  ELSE

     DBMS_OUTPUT.put_line('-- Found common_user_prefix with Value: ' || sCommonUserPrefix);

  END IF;

 -- Add in Front of the sUsername the sCommonUserPrefix

   sUsername := sCommonUserPrefix || sUsername;

EXCEPTION

  WHEN OTHERS THEN

     DBMS_OUTPUT.put_line('-- No CDB_FEATURE_USAGE_STATISTICS');

END;

 

 DBMS_OUTPUT.put_line('- Check if Oracle DB 12 or higher');

SELECT COUNT(*) INTO v_count12 FROM V$INSTANCE WHERE regexp_like(VERSION,'^1[2-9]\.|^[2-9][0-9]\.');

IF v_count12 >= 1 THEN

  EXECUTE IMMEDIATE q'<

       BEGIN

     DBMS_NETWORK_ACL_ADMIN.append_host_ace (

       host       => '*',

       ace        => xs$ace_type(privilege_list => xs$name_list('resolve'),

                                 principal_name => '>' || sUsername || q'<',

                                 principal_type => xs_acl.ptype_db));

     dbms_output.put_line('  - Added ACL to Oracle DB 12 or higher');

       END;    

   >';

ELSE

   dbms_output.put_line('  - Can not find Oracle DB 12 or higher');

END IF;

EXCEPTION

WHEN compile_error THEN

   dbms_output.put_line('  - Can not find Oracle DB 12 or higher');

END;

/