通过设置目录组标志位解决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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-766835/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-766835/