Changing default group of datapump export

alone-15155_1280
Here is an example of how to change the default permissions of a datapump file so a user not in the dba group can read and use the file.
First up lets see where we are:
[oracle@server]:EMZA3:[~]# pwd
/home/oracle

If we create a file now the permissions are set based on the uid/gid of the user that creates them which for us is
[oracle@server]:EMZA3:[~]# id
uid=208(oracle) gid=301(oinstall) groups=42(trusted),300(dba),301(oinstall)
[oracle@server]:EMZA3:[~]# touch testfile
[oracle@server]:EMZA3:[~]# ls -l
[oracle@server]:EMZA3:[~]# ls -l testfile
-rw-r--r-- 1 oracle oinstall 0 2013-08-30 13:44 testfile

So any file that oracle creates is in the oinstall group by default
Now lets create a test directory:
[oracle@server]:EMZA3:[~]# mkdir test
[oracle@server]:EMZA3:[~]# ls -ld test
drwxr-xr-x 2 oracle oinstall 4096 2013-08-30 13:20 test

So the directory is created with a group of oinstall – the primary group of the oracle user. If we change this directory so that the group ownership is now ‘trusted’ it changes who can access the directory but does nothing else files will still be created with the default gid of the user
[oracle@server]:EMZA3:[~]# chgrp trusted test
[oracle@server]:EMZA3:[~]# ls -ld test
drwxr-xr-x 2 oracle trusted 4096 2013-08-30 13:20 test

Now lets do the clever bit and set the guid bit
[oracle@server]:EMZA3:[~]# chmod g+s test
Now lets go and create a file and see what happens:
[oracle@server]:EMZA3:[~]# cd test
[oracle@server]:EMZA3:[~/test]# touch test
[oracle@server]:EMZA3:[~/test]# ls -l
total 0
-rw-r--r-- 1 oracle trusted 0 2013-08-30 13:21 test

And we can see that the file has a group of trusted – so far so good – now lets try creating an oracle directory inside the database to this path and run an export:
[oracle@server]:EMZA3:[~/test]# s
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 30 13:21:51 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

SYS@EMZA3>create directory test as '/home/oracle/test';
Directory created.
SYS@EMZA3>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

Now for an export

[oracle@server]:EMZA3:[~/test]# expdp / schemas=system directory=test
Export: Release 11.2.0.3.0 - Production on Fri Aug 30 13:22:22 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "OPS$ORACLE"."SYS_EXPORT_SCHEMA_06":  /******** schemas=system directory=test
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 24.11 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
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/TABLE/PRE_TABLE_ACTION
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
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/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "SYSTEM"."READ_ONLY_AUDIT"                  20.10 MB  390203 rows
. . exported "SYSTEM"."AQ$_QUEUE_UPGRADE_TMP"            10.02 KB       4 rows
. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE"          6.328 KB       2 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES"             6.882 KB      28 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD"        5.843 KB      19 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS"          5.484 KB       3 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES"           6.289 KB       2 rows
. . exported "SYSTEM"."DEF$_AQCALL"                          0 KB       0 rows
. . exported "SYSTEM"."DEF$_AQERROR"                         0 KB       0 rows
. . exported "SYSTEM"."DEF$_CALLDEST"                        0 KB       0 rows
. . exported "SYSTEM"."DEF$_DEFAULTDEST"                     0 KB       0 rows
. . exported "SYSTEM"."DEF$_DESTINATION"                     0 KB       0 rows
. . exported "SYSTEM"."DEF$_ERROR"                           0 KB       0 rows
. . exported "SYSTEM"."DEF$_LOB"                             0 KB       0 rows
. . exported "SYSTEM"."DEF$_ORIGIN"                          0 KB       0 rows
. . exported "SYSTEM"."DEF$_PROPAGATOR"                      0 KB       0 rows
. . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS"             0 KB       0 rows
. . exported "SYSTEM"."DEF$_TEMP$LOB"                        0 KB       0 rows
. . exported "SYSTEM"."PLAN_TABLE"                           0 KB       0 rows
. . exported "SYSTEM"."PRODUCT_PROFILE"                      0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN"                 0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_COLUMN_GROUP"                 0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_CONFLICT"                     0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_DDL"                          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_EXCEPTIONS"                   0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_EXTENSION"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_FLAVORS"                      0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_GENERATED"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL"            0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_KEY_COLUMNS"                  0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_PARMS"                 0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY"                     0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES"            0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPCAT"                       0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPCATLOG"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPCOLUMN"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPOBJECT"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPPROP"                      0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPSCHEMA"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION"                   0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS"        0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS"                0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SITES_NEW"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS"                 0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"           0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows
. . exported "SYSTEM"."USER_PROFILE"                         0 KB       0 rows
Master table "OPS$ORACLE"."SYS_EXPORT_SCHEMA_06" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$ORACLE.SYS_EXPORT_SCHEMA_06 is:
  /home/oracle/test/expdat.dmp
Job "OPS$ORACLE"."SYS_EXPORT_SCHEMA_06" successfully completed at 13:23:13

So job ran OK but what are the file permissions:
[oracle@server]:EMZA3:[~/test]# ls -l
total 22256
-rw-r----- 1 oracle trusted 22753280 2013-08-30 13:23 expdat.dmp
-rw-r--r-- 1 oracle trusted     7222 2013-08-30 13:23 export.log
-rw-r--r-- 1 oracle trusted        0 2013-08-30 13:21 test
[oracle@server]:EMZA3:[~/test]#

Done! – now anyone in the trusted group can view the file











Comments

  1. Best solution to change for export file permissions as they are created. UMASK will change permissions for all files that are created.

    ReplyDelete
  2. Great solution. Thanks.

    ReplyDelete
  3. On RAC system we are getting this error

    ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CREATE_OBJECT_ROWS [USER:"DWH"]
    ORA-19505: failed to identify file "/orabkup/logical/appdba/appdba_dir_test.dmp"
    ORA-17503: ksfdopn:4 Failed to open file /orabkup/logical/appdba/appdba_dir_test.dmp
    ORA-17500: ODM err:File does not exist

    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
    ORA-06512: at "SYS.KUPW$WORKER", line 11259

    ReplyDelete
    Replies
    1. possible solution - EXPDP Fail After Upgrading to 12c Due to DNFS errors ORA-19505, ORA-17503, ORA-17500 (Doc ID 2020371.1)

      Delete

Post a Comment