Permission errors in the URT during datapump import
search cancel

Permission errors in the URT during datapump import

book

Article ID: 430919

calendar_today

Updated On:

Products

Data Loss Prevention Core Package

Issue/Introduction

After running the URT, the resulting 'updateReadinessTool-details.txt' shows the URT completed successfully, however user permission errors were reported during the first data pump import command.

Starting "<Example_URT_User>"."SYS_IMPORT_FULL_01": "<Example_URT_User>/********@<database_instance_name>" dumpfile=sandbox.dmp remap_schema=<database_name>:<Example_URT_User> directory=data_pump_dir logfile=imp_sandbox.log transform=oid:n,disable_archive_logging:y exclude=STATISTICS
Processing object type SCHEMA_EXPORT/USER
ORA-31685: Object type USER:"<Example_URT_User>" failed due to insufficient privileges. Failing sql is:
CREATE USER "<Example_URT_User>" IDENTIFIED BY VALUES '<values>' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFILE "<Profile_name>"
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE JOB" failed due to insufficient privileges. Failing sql is:
GRANT CREATE JOB TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."ADMINISTER DATABASE TRIGGER" failed due to insufficient privileges. Failing sql is:
GRANT ADMINISTER DATABASE TRIGGER TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE INDEXTYPE" failed due to insufficient privileges. Failing sql is:
GRANT CREATE INDEXTYPE TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE OPERATOR" failed due to insufficient privileges. Failing sql is:
GRANT CREATE OPERATOR TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE TYPE" failed due to insufficient privileges. Failing sql is:
GRANT CREATE TYPE TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE MATERIALIZED VIEW" failed due to insufficient privileges. Failing sql is:
GRANT CREATE MATERIALIZED VIEW TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE ANY TRIGGER" failed due to insufficient privileges. Failing sql is:
GRANT CREATE ANY TRIGGER TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE TRIGGER" failed due to insufficient privileges. Failing sql is:
GRANT CREATE TRIGGER TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE PROCEDURE" failed due to insufficient privileges. Failing sql is:
GRANT CREATE PROCEDURE TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE SEQUENCE" failed due to insufficient privileges. Failing sql is:
GRANT CREATE SEQUENCE TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE VIEW" failed due to insufficient privileges. Failing sql is:
GRANT CREATE VIEW TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE SYNONYM" failed due to insufficient privileges. Failing sql is:
GRANT CREATE SYNONYM TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE CLUSTER" failed due to insufficient privileges. Failing sql is:
GRANT CREATE CLUSTER TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE TABLE" failed due to insufficient privileges. Failing sql is:
GRANT CREATE TABLE TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."UNLIMITED TABLESPACE" failed due to insufficient privileges. Failing sql is:
GRANT UNLIMITED TABLESPACE TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."ALTER SESSION" failed due to insufficient privileges. Failing sql is:
GRANT ALTER SESSION TO "<Example_URT_User>"
ORA-31685: Object type SYSTEM_GRANT:"<Example_URT_User>"."CREATE SESSION" failed due to insufficient privileges. Failing sql is:
GRANT CREATE SESSION TO "<Example_URT_User>"
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
ORA-31685: Object type DEFAULT_ROLE:"<Example_URT_User>" failed due to insufficient privileges. Failing sql is:
ALTER USER "<Example_URT_User>" DEFAULT ROLE ALL
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
ORA-31685: Object type TABLESPACE_QUOTA:"<Example_URT_User>"."SMYDLP_TS" failed due to insufficient privileges. Failing sql is:
DECLARE TEMP_COUNT NUMBER; SQLSTR VARCHAR2(200); BEGIN SQLSTR := 'ALTER USER "<Example_URT_User>" QUOTA UNLIMITED ON "SMYDLP_TS"'; EXECUTE IMMEDIATE SQLSTR;EXCEPTION WHEN OTHERS THEN IF SQLCODE = -30041 THEN SQLSTR := 'SELECT COUNT FROM USER_TABLESPACES WHERE TABLESPACE_NAME = ''SMYDLP_TS'' AND CONTENTS = ''TEMPORARY'''; EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT; IF TEMP_COUNT = 1 THEN RETURN; ELSE RAISE; END IF; ELSE RAISE; END IF;END;
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
ORA-39112: Dependent object type PASSWORD_HISTORY:"<Example_URT_User>" skipped, base object type USER:"<Example_URT_User>" creation failed
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39082: Object type PACKAGE BODY:"<Example_URT_User>"."UPGRADESCHEMA_PRELOAD_V14_6_0" created with compilation warnings
ORA-39082: Object type PACKAGE BODY:"<Example_URT_User>"."UPGRADESCHEMA_PRELOAD_V15_0_0" created with compilation warnings
ORA-39082: Object type PACKAGE BODY:"<Example_URT_User>"."UPGRADESCHEMA_PRELOAD_V15_1_0" created with compilation warnings
Job "<Example_URT_User>"."SYS_IMPORT_FULL_01" completed with 24 error(s) at <date> elapsed <time>

Environment

Symantec Data Loss Prevention
Oracle database

Cause

These errors are cosmetic and are triggered  due to an old Oracle bug with data pump.
Data pump ignores the grant excludes we use and tries to use the user ("<Example_URT_User>" in the example above) that was running the data pump import to grant the target user (again "<Example_URT_User>") the permissions that have been granted to the source user in the dump, even when those permissions are already granted.

Resolution

Upgrade both the Oracle server and the Oracle client on Enforce with the latest Oracle 19c updates. This will allow the URT to run without generating the permissions errors.

Powered by Wolken Software