Oracle Database scans finish without any data being scanned in 15.5

[Nick Schaut]

Has anyone ever seen an oracle database scan finish scanning after a min or so without having any bytes or information scanned? The scan finishes successfully but shows that it only runs for 1 or 2 seconds and shows zeros for bytes and information scanned.

[Iusco Pavel]

Hello,

I have encountered the same issue, and maybe my fix can help you.
The first thing you need is to look into is the driver_table_querry.<vendor> property in your sqldatabasecrawler.properties file found in "Program Files\Symantec\DataLossPrevention\DetectionServer\15.5\Protect\config" or "<installation path>\DataLossPrevention\DetectionServer\15.5\Protect\config"

For the oracle driver the querry is:
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

First and foremost you should try to connect to the database using sqlplus as the user you want to use in order to run the discover scan and try and run this query (remember to add the ";" at the end of the query). If you encounter the same problem as i did, this should return no tables, therefore your scan will run for 2-3-4-5 seconds and then finish without any errors (since both the connection and the query returned no errors).

Now, what i did in order to fix this is I changed the query as such:

select tp.owner || '.' || tp.table_name from table_privileges tp, all_tables at where tp.select_priv = 'Y' 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

Notice that I removed the "tp.grantee = (select username from user_users)" from the query (found in the first 100 something characters). Now my query returns table names properly and my scans can run without any issues.

Hope this helps.

[Nick Schaut]

Hello,

I tried removing that line and it still did not work for me. I have a question about your user account that you scan the database with. Does that account just have read only permissions? Or does it have write permissions as well?

[Iusco Pavel]

Hello,
the user has read only permissions.

I might still suggest trying to run the query using sqlplus (I started with select table_name from table_privileges) and start going from the results that gives.
Also, make sure to recycle the discover server after changing the  properties file

[Nick Schaut]

Actually your fix did work. I didnt realize my account was removed from the database. Thank you for your help