Verify the protect database user has the correct privileges in DLP

Products

• Data Loss Prevention Enforce
• Data Loss Prevention
• Data Loss Prevention Oracle Standard Edition 2

Issue/Introduction

You suspect the Oracle protect database user does not have the correct privileges and you need to verify this.

Environment

Symantec Data Loss Prevention (DLP) Database

Resolution

Run the following scripts using SQLPlus while logged in "sys as sysdba" and compare the results to those listed below:

set pagesize 100

SELECT privilege, inherited
FROM   dba_sys_privs
WHERE  grantee = 'PROTECT'
ORDER  by privilege;

SELECT privilege, table_name, inherited
FROM   dba_tab_privs
WHERE  grantee = 'PROTECT'
ORDER  by privilege, table_name;

 

First query output ought to look like this:

SQL> SELECT privilege
  2  FROM   dba_sys_privs
  3  WHERE  grantee = 'PROTECT'
  4  ORDER  by privilege;

PRIVILEGE
----------------------------------------
ALTER SESSION
CREATE CLUSTER
CREATE INDEXTYPE
CREATE JOB
CREATE MATERIALIZED VIEW
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
UNLIMITED TABLESPACE

15 rows selected.

If the privileges are not correct, run the following in SQLPlus using "sys as sysdba":

grant <privilege> to protect;

For example:

grant CREATE OPERATOR to protect;

The response ought to be "granted".  Repeat for each missing privilege.

 

Second query output ought to look like this:

SQL> SELECT privilege, table_name, inherited
  2  FROM   dba_tab_privs
  3  WHERE  grantee = 'PROTECT'
  4  ORDER  by privilege, table_name;

================

Table permissions/privileges:

PRIVILEGE                                TABLE_NAME
---------------------------------------- ------------------------------
EXECUTE                                  DBMS_LOB
EXECUTE                                  DBMS_LOCK
EXECUTE                                  DBMS_SESSION
EXECUTE                                  DBMS_SQL
READ                                     DATA_PUMP_DIR
SELECT                                   DBA_DATA_FILES
SELECT                                   DBA_EXTENTS
SELECT                                   DBA_REGISTRY_HISTORY
SELECT                                   DBA_SEGMENTS
SELECT                                   DBA_TABLESPACES
SELECT                                   DBA_TEMP_FILES
SELECT                                   DBA_TEMP_FREE_SPACE
SELECT                                   V_$DATABASE
SELECT                                   V_$INSTANCE
SELECT                                   V_$PARAMETER
SELECT                                   V_$SESSION
SELECT                                   V_$SESSION_LONGOPS
SELECT                                   V_$SQL
SELECT                                   V_$SQLAREA
SELECT                                   V_$SQL_PLAN
SELECT                                   V_$SQL_PLAN_STATISTICS_ALL
SELECT                                   V_$VERSION
WRITE                                    DATA_PUMP_DIR


23 rows selected.

 

If the table privileges are not correct, run the following in sqlplus sys as sysdba:

grant <privilege> on <table>

 to <schema_user>;

 

For example:

grant EXECUTE on DBMS_LOCK to protect;

 

The response ought to be "granted".  Repeat for each missing privilege.

 

Rerun above queries to recheck that all necessary privileges are in place.

 

Note: The protect user should have SELECT privileges to some system tables by default, but the permissions/privileges may have been removed if there has been any "hardening" of the Oracle instance. These include:

V_$VERSION 
ROLE_ROLE_PRIVS
USER_ROLE_PRIVS
USER_TAB_PRIVS

DBMS_MONITOR (DEBUG, EXECUTE)

 

 

 

Additional Information

Refer to our KB article 160076 for a script to grant all the required privileges.

Verify or Grant Oracle Database Permissions to Protect User