Highlighted
Super Contributor.. Super Contributor..
Super Contributor..
533 views

Oracle LMS Scripts

Jump to solution

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

0 Likes
1 Solution

Accepted Solutions
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Oracle LMS Scripts

Jump to solution

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 '%ADVANCED%'    
    '''
    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

Likes are appreciated!

View solution in original post

3 Replies
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Oracle LMS Scripts

Jump to solution

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 '%ADVANCED%'    
    '''
    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

Likes are appreciated!

View solution in original post

Highlighted
Super Contributor.. Super Contributor..
Super Contributor..

Re: Oracle LMS Scripts

Jump to solution

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

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Oracle LMS Scripts

Jump to solution

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

Likes are appreciated!
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.