通過設定目錄組標誌位解決dmp檔案讀許可權問題

問題：
資料泵匯出的檔案屬主為oracle,其他使用者無讀取許可權。在沒有oracle使用者許可權的情況下，只能看到該檔案，卻無法複製/載入，需要找DBA修改檔案許可權，實在有些麻煩。
通過設定目錄屬組標誌位（setgid bit），可以繞過該限制。本文測試如下：

1，目錄組屬性標誌位設定，setgid bit (octal 2000)，用於目錄
*該許可權只對目錄有效. 目錄被設定該位後, 任何使用者在此目錄下建立的檔案都具有和該目錄所屬的組相同的組.

2，問題重現：
bnet@ZT0NET1:/bnet$ sqlplus bnet/bnet

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 22 09:08:24 2013
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> create directory dir1 as '/bnet';
Directory created.

SQL> CREATE TABLE currency_xt
  2  ORGANIZATION EXTERNAL
  3  (
  4    TYPE ORACLE_DATAPUMP
  5    DEFAULT DIRECTORY dir1
  6    LOCATION ('currency.dmp')
  7  )
  8  AS SELECT * FROM currency;
Table created.

SQL> exit

bnet@ZT0NET1:/bnet$ ls -lrt
-rw-r-----    1 oracle   dba           16384 Jul 22 09:10 currency.dmp
-rw-r--r--    1 oracle   dba              41 Jul 22 09:10 CURRENCY_XT_4562980.log

bnet@ZT0NET1:/bnet$ cp currency.dmp a.dmp
cp: currency.dmp: The file access permissions do not allow the specified action.

 

3，設定目錄組屬性，重新匯出：
bnet@ZT0NET1:/bnet$ mkdir gbit
bnet@ZT0NET1:/bnet$ ls -lrt
drwxr-xr-x    2 bnet   bnet          256 Jul 22 09:05 gbit
bnet@ZT0NET1:/bnet$ chmod a+w gbit
bnet@ZT0NET1:/bnet$ chmod g+s gbit
bnet@ZT0NET1:/bnet$ ls -lrt
drwxrwsrwx    2 bnet   bnet          256 Jul 22 09:05 gbit

bnet@ZT0NET1:/bnet/gbit$ sqlplus bnet/bnet

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 22 09:08:24 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> create directory dir2 as '/bnet/gbit';

Directory created.

SQL> CREATE TABLE currency_xt2
  2  ORGANIZATION EXTERNAL
  3  (
  4    TYPE ORACLE_DATAPUMP
  5    DEFAULT DIRECTORY dir2
  6    LOCATION ('currency.dmp')
  7  )
  8  AS SELECT * FROM currency;

Table created.

SQL> desc currency;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CURCDE                                    NOT NULL VARCHAR2(3)
 NAME                                      NOT NULL VARCHAR2(300)
 NAME_EN                                            VARCHAR2(300)
 DOMCUR                                             VARCHAR2(3)
 INTCUR                                    NOT NULL VARCHAR2(3)
 DECPOS                                    NOT NULL NUMBER(2)
 CURUNT                                             NUMBER(7)
 EXCFLG                                             VARCHAR2(1)
 EXCUNT                                             NUMBER(6,3)
 MINUNT                                             NUMBER(6,3)
 CUTFLG                                             VARCHAR2(1)
 SPEFLG                                             VARCHAR2(1)
 HOLFLG                                             VARCHAR2(1)
 CLRFLG                                             VARCHAR2(1)

SQL> CREATE TABLE currency_xt3
  2  (CURCDE                                             VARCHAR2(3),
  3   NAME                                               VARCHAR2(300),
  4   NAME_EN                                            VARCHAR2(300),
  5   DOMCUR                                             VARCHAR2(3),
  6   INTCUR                                             VARCHAR2(3),
  7   DECPOS                                              NUMBER(2),
  8   CURUNT                                             NUMBER(7),
  9   EXCFLG                                             VARCHAR2(1),
 10   EXCUNT                                             NUMBER(6,3),
 11   MINUNT                                             NUMBER(6,3),
 12   CUTFLG                                             VARCHAR2(1),
 13   SPEFLG                                             VARCHAR2(1),
 14   HOLFLG                                             VARCHAR2(1),
 15   CLRFLG                                             VARCHAR2(1)
 16  )
 17  ORGANIZATION EXTERNAL
 18  (
 19    TYPE ORACLE_DATAPUMP
 20    DEFAULT DIRECTORY dir2
 21    LOCATION ('currency.dmp')
 22  );

SQL> select count(*) from currency_xt3;

  COUNT(*)
----------
        45

SQL> exit

bnet@ZT0NET1:/bnet/gbit$ ls -lrt
total 40
-rw-r-----    1 oracle   bnet        16384 Jul 22 09:12 currency.dmp
-rw-r--r--    1 oracle   bnet           41 Jul 22 09:12 CURRENCY_XT2_4562980.log
==>檔案屬組為oracle，而bnet組有讀許可權

bnet@ZT0NET1:/bnet/gbit$ cp currency.dmp a.dmp
bnet@ZT0NET1:/bnet/gbit$ ls -lrt
total 80
-rw-r-----    1 oracle   bnet        16384 Jul 22 09:12 currency.dmp
-rw-r--r--    1 oracle   bnet           41 Jul 22 09:12 CURRENCY_XT2_4562980.log
-rw-r--r--    1 oracle   bnet           41 Jul 22 09:19 CURRENCY_XT3_4562980.log
-rw-r-----    1 bnet   bnet        16384 Jul 22 09:31 a.dmp
==>現在有讀許可權，a.dmp屬主變為bnet