Verify or Grant Oracle Database Permissions to Protect User

book

Article ID: 160076

calendar_today

Updated On:

Products

Data Loss Prevention Enforce Data Loss Prevention

Issue/Introduction

To verify the PROTECT User permissions for proper DLP function or prior to an installation or upgrade.

Note: This script can be used to revise permissions on the UPGRADE READINESS user as well. 

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 11.2.0.1,  11.2.0.2, 11.2.0.3, 11.2.0.4, and 12c 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 open 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

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;
grant select on v_$version to protect;

-- ----------------------------------------------------------------------
-- These privileges are new in v8.
-- ----------------------------------------------------------------------
grant all on dbms_monitor to protect;  
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 &username;
GRANT SELECT ON dba_registry_history TO &username;
GRANT SELECT ON dba_temp_free_space TO &username;
GRANT SELECT ON v_$version TO &username;
GRANT EXECUTE ON dbms_lob TO &username;
GRANT EXECUTE ON dbms_sql TO &username;
GRANT EXECUTE ON dbms_job TO &username;

-------------------------------------------------------------------------
-- These privileges are new in Oracle 19c
-------------------------------------------------------------------------
GRANT CREATE JOB TO &username;
exit;

Attachments

1600729113024__Grant_protect_permissions.sql get_app