- that the entire target database is going to be scanned,

- that the target database account is either SYS (if the whole database is to be scanned),

 OR a single user if only that user's tables are to be scanned,

 OR the non-DBA user account has fantastic, DBA-like privileges, with access to all specifically Oracle's data dictionary tables.

This will present one way of designating Database Discover scanning targets without compromising the datadictionary tables, Sys or any other DBA related accounts.

This solution is fairly straight forward and is based on how database scanning content is determined from outside the targeted database.

The information that gets stored on the enforce server is the connection string (hostname, database name, port), the account to use (for the least number of obstacles this would be SYS, but that would also mean observing a reduced amount of security), and the script with which to identify the tables to scan.

The default script can be found on the Discover Server in the product installation location and subpath \Protect\config\sqldatabasecrawler.properties file.  The configuration appears as below:

driver_table_query.oracle = select dt.owner||'.'||dt.table_name from dba_tables dt where not exists (select dnt.table_name from dba_nested_tables dnt where dnt.table_name = dt.table_name and dnt.owner = dt.owner) and (dt.iot_type is null or not dt.iot_type = 'IOT_OVERFLOW')

The product of this script is a list of ALL tables prefaced with their owners for the entire database.  The references to nested tables and 'IOTs' are to screen out those tables that can't be scanned; they are reference tables for other table-types and contain no readable data.  This script is reliant on access to data dictionary tables.

The following instructions provide an alternate means of targeting database tables without dynamically generating an owner/table list:

1. Logged into SQLPlus using sys as sysdba, create an account on the target database:

create user target_mgt identified by target_mgt;   -- this is an example user account.

2. Grant the account the following privileges:

grant connect, resource to target_mgt;

grant select any table to target_mgt;

3. Login into the new account and create a driver_table:

conn target_mgt

password:

create table driver_table (owner varchar2(40), table_name varchar2(40));

4. Insert the table owner/table_name records that are to be targeted for scanning.  Remember that datadictionary tables cannot be selected from.  Also, the owner and table_names have to actually exist, else an error will be generated.  These are just example entries:

insert into driver_table values ('PROTECT','INCIDENT');

insert into driver_table values ('MANAGER','ORDER_ENTRY');

insert into driver_table values ('COMMANDERNCHIEF','MISSLECODES');

commit; --- this MUST happen!

5. Change the entry in the enforce Vontu\Protect\config\sqldatabasecrawler.properties file to use the driver table:

Using "#", comment out current script, like so:

#driver_table_query.oracle = select dt.owner||'.'||dt.table_name from dba_tables dt where not exists (select dnt.table_name from dba_nested_tables dnt where dnt.table_name = dt.table_name and dnt.owner = dt.owner) and (dt.iot_type is null or not dt.iot_type = 'IOT_OVERFLOW')

Create a new entry that uses the new table:

driver_table_query.oracle = select owner||'.'||table_name from driver_table

Save the file and exit.

6. From enforce, modify or create a target specifying the user account used to create the driver_table.

When the scan is run, it will only scan those tables that appear in the driver table.  Because the user account possesses "Select Any Table" privilege, it will be able to scan the specified tables as long as they don't belong to the data dictionary.

To add ALL tables that belong to a particular user, use the following script, bearing in mind that a user account that does not have access to data dictionary tables has no way of knowing if a table is 'nested' or is an 'IOT', both of which are unscanable.

This will load the driver table with all entries for user account "username":

insert into driver_table select 'username', table_name from all_tables where owner='username';

commit;

Now all tables owned by Jiffypop will be scanned.

Be advised: this does not cover any changes within the database.  If the account owner adds or removes tables, the driver_table will need to be updated.

Use this solution at your own risk.  It is not a replacement for documented instructions provided by the Symantec DLP administration guides.