Oracle Data Pump tool series: Data Pump permission configuration details

I. Concepts of Data Pump:
Oracle Data Pump technology can quickly migrate data and metadata from one database to another.
Oracle Data Pump consists of three unique parts:

1. command line Client: expdp and impdp
2. DBMS_DATAPUMP PL/SQL package (also called Data Pump API)
3. DBMS_METADATA PL/SQL package (also called Metadata API)

Compared with the traditional client-based export/import tool, data pump is a server-based utility.
Oracle Data Pump and export/import functions are not compatible.

Oracle Data Pump can also use the network_link function, without dumping files, in two databases
Use Network Technology to migrate data and metadata from a remote database.

Ii. installation and configuration of the Data Pump Utility

You must have certain permissions to run data pump. Run export DataPump or
The minimum permissions required for the import DataPump job are as follows:
-System permission: Create SESSION (or connect role)
-System permission: Create Table
-Read and write permissions on a valid directory object (create directory privilege with which a valid directory object was created)
-You must have enough quota for the user's default tablespace (the primary sampling table used by DataPump job needs to be created ).

In addition, data pump also requires the exp_full_database role to execute the following tasks:
-Run the full-Database Export DataPump job
-Run the transfer tablespace export DataPump job.
-Run an export DataPump job with the trace parameter.
-Run the export DataPump job to export other schemas.

Note: The preceding permission requirements apply to users who run the export DataPump or import DataPump job in a linked database, rather than those who are exported or imported.

These permissions can be explicitly granted or granted by roles as follows:

Connect system/Manager
Create directory my_dir as 'full _ pre_existing_directory_path_here ';
Grant create session, create table to Scott identified by tiger;
Grant read, write on directory my_dir to Scott;
Alter user Scott quota unlimited on users;
 
Or:

Connect system/Manager
Create directory my_dir as 'full _ pre_existing_directory_path_here> ';
Create role expdp_role;
Grant create session, create table to expdp_role;
Grant read, write on directory my_dir to expdp_role;
Grant expdp_role to Scott;
Alter user Scott default role all;
Alter user Scott quota unlimited on users;

Privileged users:

A privileged user is an exp_full_database role that has the export DataPump job to execute and
Import the imp_full_database of the DataPump job or the DBA role that includes the two roles:
Set lines 80
Col privilege for A40
Select grantee, granted_role, default_role
From dba_role_privs
Where granted_role in ('dba ', 'exp _ full_database', 'Imp _ full_database ')
Order by 1, 2;
 
Grantee granted_role def
-----------------------------------------------------
DBA exp_full_database Yes
DBA imp_full_database Yes
Scott exp_full_database Yes
Scott imp_full_database Yes
Sys DBA Yes
Sys exp_full_database Yes
Sys imp_full_database Yes
Sysman DBA Yes
System DBA Yes

You can use the DataPump logfile parameter to write all ongoing and completed work and error-related messages to a log file.
Check the Data Pump file first.

You can also specify the unconfirmed public metrics = y parameter to record the number of additional objects and time-related information in the log file.

The error message reported in the log file does not mean that the Data Pump job fails. Some messages only report warning and notification information. For example,
Information about the existing and skipped objects. In this case, you can adjust the impdp directory parameters to recreate these objects or
Existing object additional data.

Practical SQL scripts related to DataPump job Permissions

-- Check whether the user connecting to the database to execute the DataPump job has the create session and Create Table permissions.
Set lines 80 pages 50
Col privilege for A40
Select grantee, privilege
From dba_sys_privs
Where (grantee in ('Scott ', 'public ')
Or grantee in (select granted_role from dba_role_privs
Where grantee in ('Scott ', 'public ')))
And privilege in ('create session', 'create table ')
Order by 1, 2;

Grantee privilege
----------------------------------------------------------------------
Scott create session
Scott CREATE TABLE

-- View the roles and default roles granted by the user who runs the DataPump job in the Linked database.

Set lines 80
Select grantee, granted_role, default_role
From dba_role_privs
Where grantee in ('Scott ', 'public') order by 1, 2;

Grantee granted_role def
---------------------------------------------------------------
Scott exp_full_database Yes
Scott imp_full_database Yes

Queries the directory permissions granted by the user who is connected to the database and runs the DataPump job.

Set lines 100
Col privilege for A10
Col grantee for A20
Col owner for A20
Select P. Grantee, P. privilege, P. Owner, D. directory_name
From dba_tab_privs P, dba_directories d
Where p. table_name = D. directory_name
And (grantee in ('Scott ', 'public ')
Or grantee in (select granted_role from dba_role_privs
Where grantee in ('Scott ', 'public ')))
Order by 4, 3, 2;

Grantee privilege owner directory_name
------------------------------------------------------------------------
Imp_full_database read sys data_pump_dir
Exp_full_database read sys data_pump_dir
Exp_full_database write sys data_pump_dir
Imp_full_database write sys data_pump_dir
Scott read sys my_dir
Scott write sys my_dir

-- View the default tablespace of the user who connects to the database and runs the DataPump job

Set lines 80
Select username, default_tablespace
From dba_users where username in ('Scott ');

Username default_tablespace
------------------------------------------------------------
Scott users

-- View the tablespace quota of the user who runs the DataPump job in the Linked database

Set lines 100 numwidth 12
Select Q. username, Q. tablespace_name, Q. bytes, Q. max_bytes
From dba_ts_quotas Q, dba_users u
Where Q. Username = U. username and Q. username in ('Scott ');

Username tablespace_name bytes max_bytes
----------------------------------------------------------------

Scott users 393216-1

For reprint, please indicate the source and original article links:

Http://blog.csdn.net/xiangsir/article/details/8627354