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 ~

Recommended Posts

A story that is a little addicted to the authority of the directory specified by expdp (for beginners)
[AtCoder for beginners] A story about the amount of calculation that you want to know very roughly
[Python] Specify the range from the image by dragging the mouse
Get the image of "Suzu Hirose" by Google image search.
Judging the victory or defeat of Shadowverse by image recognition
A story that is a little addicted to the authority of the directory specified by expdp (for beginners)
[Image recognition] How to read the result of automatic annotation with VoTT
The result of making the first thing that works with Python (image recognition)
A story that struggled to handle the Python package of PocketSphinx
Heroku deployment of the first Django app that beginners are addicted to
[For beginners] I want to get the index of an element that satisfies a certain conditional expression
The story of IPv6 address that I want to keep at a minimum
The story of Django creating a library that might be a little more useful
How to make a Raspberry Pi that speaks the tweets of the specified user
A story that reduces the effort of operation / maintenance
A python script that gets the number of jobs for a specified condition from indeed.com
The story of Linux that I want to teach myself half a year ago
A story that I was addicted to at np.where
[Linux] Basics of authority setting by chmod for beginners
Zip 4 Gbyte problem is a story of the past
A story that analyzed the delivery of Nico Nama.
How to extract conditions (acquire all elements of Group that satisfy the conditions) for Group by Group
I thought a little because the Trace Plot of the stan parameter is hard to see.
[Python] Programming to find the number of a in a character string that repeats a specified number of times.
Python> __init__.py> Required to handle the specified directory as a package (empty file is acceptable)
The story I was addicted to when I specified nil as a function argument in Go
The story of creating a VIP channel for in-house chatwork
[Ubuntu] How to delete the entire contents of a directory
The story that the private key is set to 600 with chmod
Is there a secret to the frequency of pi numbers?
The story of when I was addicted to Caused by SSLError ("Can't connect to HTTPS URL because the SSL module is not available.")
I wrote AWS Lambda, and I was a little addicted to the default value of Python arguments
A story that verified whether the number of coronas is really increasing rapidly among young people
Hypothesis / Verification (176) How to make a textbook that is easier than "The easiest textbook for quantum computers"
[Python] How to use the for statement. A method of extracting by specifying a range or conditions.
A story about a person who uses Python addicted to the judgment of an empty JavaScript dictionary
The story that the version of python 3.7.7 was not adapted to Heroku
The story of making a standard driver for db with python.
The story of creating a site that lists the release dates of books
[For beginners] Recursive function (Tower of Hanoi is easy to understand!)
[python] A note that started to understand the behavior of matplotlib.pyplot
The story of making a module that skips mail with python
[Python] A program that rotates the contents of the list to the left
The story of creating a store search BOT (AI LINE BOT) for Go To EAT in Chiba Prefecture (1)
A story addicted to Azure Pipelines
A super introduction to Django by Python beginners! Part 2 I tried using the convenient functions of the template
A story about trying to improve the testing process of a system written in C language for 20 years
Create a bot that posts the number of people positive for the new coronavirus in Tokyo to Slack
The story of writing a program
A story about creating a program that will increase the number of Instagram followers from 0 to 700 in a week
A story that visualizes the present of Qiita with Qiita API + Elasticsearch + Kibana
[Python] A program that calculates the number of socks to be paired
[Example of Python improvement] What is the recommended learning site for Python beginners?
The story of developing a web application that automatically generates catchphrases [MeCab]
How to create a wrapper that preserves the signature of the function to wrap
I want to identify the alert email. --Is that x a wildcard? ---
Approach commentary for beginners to be in the top 1.5% (0.83732) of Kaggle Titanic_1
What is a C language library? What is the information that is open to the public?
The story of making a package that speeds up the operation of Juman (Juman ++) & KNP
I tried to verify the result of A / B test by chi-square test
Approach commentary for beginners to be in the top 1.5% (0.83732) of Kaggle Titanic_2
How to get the "name" of a field whose value is limited by the choice attribute in Django's model
I created a script to check if English is entered in the specified position of the JSON file in Python.
I tried to confirm whether the unbiased estimator of standard deviation is really unbiased by "throwing a coin 10,000 times"
The story of trying to reconnect the client
The story of adding MeCab to ubuntu 16.04