A story that is a little addicted to the authority of the directory specified by expdp (for beginners)

Hello. My name is hrkii123.

This entry is the 18th day article of JPOUG Advent Calendar 2019.

Qiita's first post is JPOUG Advent Calendar 2019.

JPOUG Advent Calendar 2019 https://adventar.org/calendars/4154

Introduction

This time, I'm addicted to datapump's expdp utility.

One day. We designed around the DB with the requirement that OS users, DB users, OS directories, etc. be separated for each development team so that data cannot be referenced by each other. Among them, I received an order to prepare an OS directory to be exported by datapump for each team so that they can not refer to each other's dump files.

I thought it would be easy to control with the permissions of such an OS directory. (It wasn't so easy.)

So, I summarized the verification contents that I tried variously.

Schematic diagram

Export the user_A schema to / var / work / Adir with datapump. After the export, only Aadmin users can access the dump files under / var / work / Adir, and Badmin users cannot access them. Access is possible by setting the permissions of the OS directory. Also, although not shown in the figure, the DB used the 12.1 RAC environment. (Because I happened to have a virtual machine ..)

無題のプレゼンテーション (3).png

Create the DB user "user_A" and the directory object "dirA" for verification. Grant the necessary privileges to the DB user "user_A".

create user user_A identified by welcome1;
CREATE DIRECTORY dirA AS '/var/work/Adir';
grant create session,resource,unlimited tablespace to user_A;
grant read, write on directory dirA to user_A;

Now, can expdp be executed in this state?

/u01/app/oracle/product/12.1.0/dbhome_1/bin/expdp user_A/welcome1@testdb directory=dirA dumpfile=expdp_A.log schemas=user_A

An error occurred as shown in the output below.

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

The above error is output when the permissions of the OS directory are not appropriate.

The OS directory specified in the directory object is the owner Aadmin and the owning group Aadmin. The dump file exported by the datapump specification is owned by oracle and owned by asmadmin. In other words, to execute datapump, access from the oracle user and the asmadmin group must be allowed. If the OS directory permissions are 777, you don't have to worry about anything, but in this case the oracle user doesn't belong to the Aadmin group, so you can't access the OS directory.

As a workaround, this time I'll add Aadmin to the secondary groups for the oracle and grid users.

id oracle
uid=54321(oracle) gid=54321(oinstall)groups=54321(oinstall),54322(dba),1101(oper),1102(backupdba),1103(dgdba),1104(kmdba),1201(asmdba),54323(Aadmin)

id grid
uid=1100(grid) gid=54321(oinstall)groups=54321(oinstall),1200(asmadmin),1201(asmdba),1202(asmoper),54323(Aadmin)

Will it succeed this time?

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

It came out again. This error! !! I should be able to access the OS directory, but ...

By the way, if you change the permissions of the OS directory to 777, it works fine.

$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/expdp user_A/welcome1@testdb directory=dirA 
dumpfile=expdp_A.log schemas=user_A

Export: Release 12.1.0.2.0 - Production on Sun Dec 15 03:53:01 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting "USER_A"."SYS_EXPORT_SCHEMA_01":  user_A/********@testdb directory=dirA dumpfile=expdp_A.log schemas=user_A
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Master table "USER_A"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER_A.SYS_EXPORT_SCHEMA_01 is:
  /var/work/Adir/expdp_A.log
Job "USER_A"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Dec 15 03:53:42 2019 elapsed 0 00:00:38

[oracle@node1 ~]$

How did you do it after all

When adding a secondary group of oracle, grid users in a RAC environment, it is necessary to make the clusterware aware! It will be recognized by restarting the clusterware after adding the group. You can check if the clusterware is aware that a group of users has been added by checking the pmon process.

After restarting CRS

ps -ef | grep pmon
    grid     14417     1  0 05:37 ?        00:00:00 asm_pmon_+ASM1
    oracle   15136     1  0 05:37 ?        00:00:00 ora_pmon_ctestdb1
    grid     15143     1  0 05:37 ?        00:00:00 mdb_pmon_-MGMTDB
    oracle   30528 27106  0 06:06 pts/1    00:00:00 grep pmon

cat /proc/14417/status |grep Groups

Groups: 1200 1201 1202 54321 54323 ★ 54323 (Aadmin) is recognized

cat /proc/15136/status |grep Groups

Groups: 1101 1102 1103 1104 1201 54321 54322 54323 ★ 54323 (Aadmin) is recognized

Now expdp is finally possible.

$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/expdp user_A/welcome1@testdb directory=dirA dumpfile=expdp_A.log schemas=user_A

Export: Release 12.1.0.2.0 - Production on Sun Dec 15 05:58:12 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting "USER_A"."SYS_EXPORT_SCHEMA_01":  user_A/********@testdb directory=dirA dumpfile=expdp_A.log schemas=user_A
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Master table "USER_A"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER_A.SYS_EXPORT_SCHEMA_01 is:
  /var/work/Adir/expdp_A.log
Job "USER_A"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Dec 15 05:58:36 2019 elapsed 0 00:00:23

We also have access control that we wanted to achieve.

$ id
uid=54323(Badmin) gid=54324(Badmin) groups=54324(Badmin)
$ cd /var/work/Adir/
-bash: cd: /var/work/Adir/: Permission denied
$ ls -l /var/work/Adir/
ls: cannot open directory /var/work/Adir/: Permission denied

That's it.

Stay tuned for the article on the 19th day of JPOUG Advent Calendar 2019 ~