Oracle LMS Scripts

I am reviewing the Oracle LMS report with our dba's. They are questioning how the tool determines that certain options are being used when they insist they are not. Can someone tell us how or what scripts are being run to determine whether the options are being used?

The two major questions are how does the application determine whether Advanced Security and Advanced Compression are being used.

Any help is appreciated

  • Verified Answer

    Hi Rose,

    The advanced security is requested by the following query:

        #ADVANCED SECURITY
        sql = "SELECT OWNER, TABLE_NAME, COLUMN_NAME FROM DBA_ENCRYPTED_COLUMNS "\
              "WHERE OWNER || '#' || TABLE_NAME|| '#' || COLUMN_NAME NOT IN "\
                     "(SELECT OWNER || '#' || TABLE_NAME|| '#' || COLUMN_NAME FROM DBA_LOBS)"
        lmsOptionsList.addAll(getLMSOptions(oracleClient, sql, 'ADVANCED_SECURITY', 'COLUMN_ENCRYPTION', machine_id, instance_name, db_name, 0, 3))
    
        sql = "SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES WHERE ENCRYPTED ='YES'"
        lmsOptionsList.addAll(getLMSOptions(oracleClient, sql, 'ADVANCED_SECURITY', 'TABLESPACE_ENCRYPTION', machine_id, instance_name, db_name, 0, 2))

    Therefore if you have encrypted tablespace or a column, it is counted towards the AS license.

    Similarly Advanced Compression, if you use compression for a TABLE, PARTITION, SUBPARTITION or INDEX:

        #Advanced Compression Option (introduced in 11g r1) & SECUREFILES_COMPRESSION_AND_DEDUPLICATION
        sql = "select " \
                  "'DBA_TABLES' as SOURCE_, owner, table_name, column_name, compression, deduplication " \
                  "from DBA_LOBS " \
                  "where compression   not in ('NO', 'NONE')" \
                  "or deduplication not in ('NO', 'NONE')" \
              "union all " \
              "select " \
                  "'DBA_LOB_PARTITIONS'    as SOURCE_, table_owner, table_name, column_name, compression, deduplication " \
                  "from DBA_LOB_PARTITIONS " \
                  "where compression   not in ('NO', 'NONE') " \
                  "or deduplication not in ('NO', 'NONE') " \
              "union all " \
              "select " \
                  "'DBA_LOB_SUBPARTITIONS' as SOURCE_, table_owner, table_name, column_name, compression, deduplication " \
                  "from DBA_LOB_SUBPARTITIONS " \
                  "where compression   not in ('NO', 'NONE') " \
                  "or deduplication not in ('NO', 'NONE') "
        lmsOptionsList.addAll(getLMSOptions(oracleClient, sql, 'ADVANCED_COMPRESSION', 'SECUREFILES_COMPRESSION_AND_DEDUPLICATION', machine_id, instance_name, db_name, 1, 6))
    
        #Advanced Compression Option (introduced in 11g r1) & TABLE_COMPRESSION
        sql = "select 'DBA_TABLES' as source_, owner, table_name, '' as partition_name, compression, compress_for " \
                  "from DBA_TABLES " \
                  "where compress_for in ('FOR ALL OPERATIONS', 'OLTP', 'ADVANCED') " \
              "union all " \
              "select 'DBA_TAB_PARTITIONS' as source_, table_owner, table_name, partition_name, compression, compress_for " \
                  "from DBA_TAB_PARTITIONS " \
                  "where compress_for in ('FOR ALL OPERATIONS', 'OLTP', 'ADVANCED') " \
              "union all " \
                  "select 'DBA_TAB_SUBPARTITIONS' as source_, table_owner, table_name, partition_name, compression, compress_for " \
                  "from DBA_TAB_SUBPARTITIONS " \
                  "where compress_for in ('FOR ALL OPERATIONS', 'OLTP', 'ADVANCED')"
        lmsOptionsList.addAll(getLMSOptions(oracleClient, sql, 'ADVANCED_COMPRESSION', 'TABLE_COMPRESSION', machine_id, instance_name, db_name, 1, 6))
    
        #Advanced Compression Option (introduced in 11g r1) & DBA_INDEXES.COMPRESSION
        sql = '''
            select
                   OWNER        ,
                   INDEX_NAME   ,
                   TABLE_OWNER  ,
                   TABLE_NAME   ,
                   COMPRESSION
              from DBA_INDEXES
              where COMPRESSION like '�VANCED%'    
        '''
        lmsOptionsList.addAll(getLMSOptions(oracleClient, sql, 'ADVANCED_COMPRESSION', 'DBA_INDEXES.COMPRESSION', machine_id, instance_name, db_name, 1, 5))

    You can find all the queries in OracleLMSOptions.py file in the Adapter Management tab.

    Cheers,

    Petko Popadiyski

    Freelance Microfocus CMS UCMDB Consulting

  • Thanks for the information above, exactly what I needed.  Now for the next logical step.

    In conversations with the dba's they raise the following question. 

    All of the queries listed below do not appear to differentiate between the use of a feature by Oracle’s own product and the use by the customer.   For example, at the very least the SQL’s should not check the SYS and SYSTEM accounts as they are provided by oracle and use some of these features by default for their own processing.   

    In our analysis we seem to be getting some false postitives due to the paragraph above.  Just curious whether this has been identified before and how to address.

    Thanks

  • if you really think this is an incorrect behaviour, you should open a support case to MicroFocus, explain your reasoning and eventually they will change it.

    Regards,

    Petko