问题：
数据泵导出的文件属主为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

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博客

通过设置目录组标志位解决dmp文件读权限问题

最新文章