When trying to run a Discover scan against an Oracle database, the scan completes in a few seconds and does not return any results.
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:
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 change the query used by the Discover scan, perform the following steps:
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
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.
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).