Verify or Grant Oracle Database Permissions to Protect User or Upgrade Readiness User

Products

• Data Loss Prevention Enforce
• Data Loss Prevention

Issue/Introduction

To verify the PROTECT User or Upgrade Readiness(URT) User permissions for proper DLP function or prior to an installation or upgrade.

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

Environment

Release : v8, v11, v15, Oracle 19c

Resolution

There are various Oracle database permissions that the Protect User is granted during oracle install by using the "oracle_create_user.sql"  script.

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

Note:  If the permissions are already set correctly running the script will not hurt anything, and if there are permissions missing or needing to be reset the script will grant the protect user the appropriate permissions or set the permissions that are missing.

Note: you may also run the script against the Oracle 12c and 19c systems that the DLP  System uses.

The following information or grant 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 permissions 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