Data Loss Prevention

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

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

set pagesize 100

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

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

Your 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 MATERIALIZED VIEW
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
UNLIMITED TABLESPACE

14 rows selected.

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

grant to protect;

For example:

grant CREATE OPERATOR to protect;

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

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

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

Table permissions:

PRIVILEGE                                TABLE_NAME
---------------------------------------- ------------------------------
DEBUG                                    DBMS_MONITOR
EXECUTE                                  DBMS_MONITOR
SELECT                                   DBA_DATA_FILES
SELECT                                   DBA_EXTENTS
SELECT                                   DBA_SEGMENTS
SELECT                                   DBA_TABLESPACES
SELECT                                   DBA_TEMP_FILES
SELECT                                   V_$DATABASE
SELECT                                   V_$INSTANCE
SELECT                                   V_$PARAMETER
SELECT                                   V_$SESSION
SELECT                                   V_$SESSION_LONGOPS
SELECT                                   V_$SQLAREA

13 rows selected.

Please note, DLP v11 will also list the following 5 permissions for the second query:

EXECUTE                                  DBMS_LOCK
EXECUTE                                  DBMS_SESSION
SELECT                                   V_$SQL
SELECT                                   V_$SQL_PLAN
SELECT                                   V_$SQL_PLAN_STATISTICS_ALL

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

grant on

 to ;

For example:

grant EXECUTE on DBMS_LOCK to protect;

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

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

Note: The protect user should have SELECT privileges to some other system tables by default, but the permissions 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