Oracle database scan using Discover does not return any results
search cancel

Oracle database scan using Discover does not return any results

book

Article ID: 160170

calendar_today

Updated On:

Products

Data Loss Prevention Network Discover Data Loss Prevention

Issue/Introduction

When trying to run a Discover scan against an Oracle database, the scan completes in a few seconds and does not return any results.

Resolution

One common cause for this problem is an incorrect table query, which causes the Discover scanner to be unable to list the tables to be scanned in the Oracle database.

Check the sqldatabasecrawler.properties file to see what table query is currently being used. You can find this file on the Discover server in \Program Files\Symantec\DataLossPrevention\DetectionServer\<DLP_version>\Protect\config (Windows) or /opt/Symantec/DataLossPrevention/DetectionServer/<DLP_version>/Protect/config (Linux).

Below is a table query that works in many environments. You may need to modify this query to suit your Oracle installation; consult your database administrator if you need more information.

driver_table_query.oracle = select tp.owner || '.' || tp.table_name from table_privileges tp, all_tables at where tp.select_priv = 'Y' and tp.grantee = (select username from user_users) and tp.owner not in ('SYS', 'SYSTEM', 'WMSYS') and tp.table_name = at.table_name and (at.iot_type is null or at.iot_type != 'IOT_OVERFLOW') and tp.owner = at.owner and tp.owner != tp.grantee union all select ut.table_name from user_tables ut where not exists (select unt.table_name from user_nested_tables unt where unt.table_name = ut.table_name) and (ut.iot_type is null or ut.iot_type != 'IOT_OVERFLOW') union all select rtp.owner || '.' || rtp.table_name from role_tab_privs rtp, all_tables at where rtp.privilege = 'SELECT' and rtp.table_name = at.table_name and (at.iot_type is null or at.iot_type != 'IOT_OVERFLOW') and rtp.owner = at.owner 

To test the table query, perform the following steps:

  • Login to Sqlplus using the same account that is defined in the Discover Target properties.
  • Run the following query to find out what tables exist in Oracle: 

select tp.owner || '.' || tp.table_name from table_privileges tp, all_tables at where tp.select_priv = 'Y' and tp.grantee = (select username from user_users) and tp.owner not in ('SYS', 'SYSTEM', 'WMSYS') and tp.table_name = at.table_name and (at.iot_type is null or at.iot_type != 'IOT_OVERFLOW') and tp.owner = at.owner and tp.owner != tp.grantee union all select ut.table_name from user_tables ut where not exists (select unt.table_name from user_nested_tables unt where unt.table_name = ut.table_name) and (ut.iot_type is null or ut.iot_type != 'IOT_OVERFLOW') union all select rtp.owner || '.' || rtp.table_name from role_tab_privs rtp, all_tables at where rtp.privilege = 'SELECT' and rtp.table_name = at.table_name and (at.iot_type is null or at.iot_type != 'IOT_OVERFLOW') and rtp.owner = at.owner;

  • If no rows are returned, and there are no errors related to permissions, then the user defined in the target has no tables. If you query the user dictionary tables, nothing will be returned.

To change the query used by the Discover scan, perform the following steps:

  • Comment out the below query in sqldatabasecrawler.properties, by adding a # in front of the line:

driver_table_query.oracle = select tp.owner || '.' || tp.table_name from table_privileges tp, all_tables at where tp.select_priv = 'Y' and tp.grantee = (select username from user_users) and tp.owner not in ('SYS', 'SYSTEM', 'WMSYS') and tp.table_name = at.table_name and (at.iot_type is null or at.iot_type != 'IOT_OVERFLOW') and tp.owner = at.owner and tp.owner != tp.grantee union all select ut.table_name from user_tables ut where not exists (select unt.table_name from user_nested_tables unt where unt.table_name = ut.table_name) and (ut.iot_type is null or ut.iot_type != 'IOT_OVERFLOW') union all select rtp.owner || '.' || rtp.table_name from role_tab_privs rtp, all_tables at where rtp.privilege = 'SELECT' and rtp.table_name = at.table_name and (at.iot_type is null or at.iot_type != 'IOT_OVERFLOW') and rtp.owner = at.owner

  • Uncomment out this query and use it instead:

driver_table_query.oracle = select tp.owner || '.' || tp.table_name from table_privileges tp, all_tables at where tp.select_priv = 'Y' and tp.grantee = (select username from user_users) and tp.owner not in ('SYS', 'SYSTEM', 'WMSYS') and tp.table_name = at.table_name and (at.iot_type is null or at.iot_type != 'IOT_OVERFLOW') and tp.owner = at.owner and tp.owner != tp.grantee union all select ut.table_name from user_tables ut where not exists (select unt.table_name from user_nested_tables unt where unt.table_name = ut.table_name) and (ut.iot_type is null or ut.iot_type != 'IOT_OVERFLOW') union all select rtp.owner || '.' || rtp.table_name from role_tab_privs rtp, all_tables at where rtp.privilege = 'SELECT' and rtp.table_name = at.table_name and (at.iot_type is null or at.iot_type != 'IOT_OVERFLOW') and rtp.owner = at.owner

This will query the system dictionary rather than the user dictionary. This query requires that the scanning account be granted select on dba_tables and dba_nested_tables.

  • Save the file and restart the Symantec DLP Detection Server Service.

 

Another cause, in some cases (possibly including Oracle RAC installations), the database connection string needs to use a fully qualified name, rather than the Oracle database SID alone. This problem causes an error in the ScanDetail log (ORA-12154: TNS:could not resolve service name).

To resolve this problem, change the database connection string from the standard format (oracle:@//oracleserver.company.com:1521/mydatabase) to the following format: (oracle:@//oracleserver.company.com:1521/mydatabase.mydomain.net).

 

 

Powered by Wolken Software