Verify or Grant Oracle Database Privileges (Permissions) to Protect User or Upgrade Readiness User

Products

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

Issue/Introduction

To verify the "Protect" User privileges for proper DLP functionality or prior to an installation or upgrade.

Note: This script can be used to revise privileges on the URT user as well. Copy the script and change "Protect" to the URT user name you created.

Environment

All DLP Database releases that are current with DLP release

Resolution

There are various Oracle database privileges that the "Protect" User (DLP default) is granted during Oracle install by using the "oracle_create_user.sql"  script.

To verify privileges (permissions) or to make sure the correct privileges are set for the "Protect" user,  you can run the attached "Grant_protect_permissions.sql"  script against the Oracle database while being logged in as sys as sysdba to the Oracle system.

Note:  If the privileges are already set correctly running the script will not alter anything. If there are privileges missing or needing to be reset the script will grant the "Protect" user the appropriate privileges or set the privileges that are missing.

The following statements are in the SQL script that you can run.  If your Oracle user created during Oracle install is different than the default "Protect" user, you can edit the script in a text editor and replace the "Protect" user with the user you created during Oracle install while running the oracle_create_user.sql  file.

Grant  Statements to correctly set privileges for the "Protect" user

ALTER USER protect DEFAULT TABLESPACE users;
ALTER USER protect TEMPORARY TABLESPACE temp;
-- ----------------------------------------------------------------------
-- These grants make up the connect role:
-- ----------------------------------------------------------------------
GRANT 
create session,
alter session,
create synonym,
create view,
create table,
create sequence 
TO protect;

-- ----------------------------------------------------------------------
-- These grants make up the resource role:
-- ----------------------------------------------------------------------
GRANT 
create table,
create cluster,
create sequence,
create trigger,
create procedure,
create type,
create indextype,
create operator
TO protect;

-- ----------------------------------------------------------------------
-- Grant privileges to the protect user.
-- ----------------------------------------------------------------------
GRANT create materialized view TO protect;
GRANT unlimited tablespace     TO protect;

grant select on dba_tablespaces to protect;
grant select on dba_data_files to protect;
grant select on dba_temp_files to protect;
grant select on dba_extents to protect;

grant select on v_$session to protect;
grant select on v_$database to protect;
grant select on v_$instance to protect;

-- ----------------------------------------------------------------------
-- These privileges are new in v8.
-- ----------------------------------------------------------------------
grant select on v_$parameter to protect;
grant select on dba_segments to protect;
grant select on v_$sqlarea to protect;
grant select on v_$session_longops to protect;

-- ----------------------------------------------------------------------
-- These privileges are new in v11.
-- ----------------------------------------------------------------------
GRANT SELECT ON v_$sql TO protect;
GRANT SELECT ON v_$sql_plan TO protect;
GRANT SELECT ON v_$sql_plan_statistics_all TO protect;
GRANT EXECUTE ON dbms_session TO protect;
grant execute on dbms_lock to protect;

-- ----------------------------------------------------------------------
-- These privileges are new in v15.
-- ----------------------------------------------------------------------
GRANT read, write ON directory data_pump_dir TO protect;
GRANT SELECT ON dba_registry_history TO protect;
GRANT SELECT ON dba_temp_free_space TO protect;
GRANT SELECT ON v_$version TO protect;
GRANT EXECUTE ON dbms_lob TO protect;
GRANT EXECUTE ON dbms_sql TO protect;
GRANT EXECUTE ON dbms_job TO protect;

-------------------------------------------------------------------------
-- These privileges are new in Oracle 19c
-------------------------------------------------------------------------
GRANT CREATE JOB TO protect;

exit;

Additional Information

Reference KB: 160062  Verify the protect database user has the correct privileges in DLP