解决历史数据转移权限错误一例

By cui hua on March 26, 2009 1:17 PM | No Comments

礼拜天在岳母家里看孩子，接到了同事的电话，东航历史数据转移作业出错了，错误原因不明。我岳母家上网不方面，就一台7、8年前的破电脑，连MSN都没有。后来用windows自带的windows messager连上MSN后定位出错原因在于执行历史数据转移的一个java存储过程的时候报错ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException

 

这个历史数据转移方案是我做的，里面用java存储过程封装了oracle的可移动表空间，上述提示信息很友好，直接照着做一下就能解决问题了，即执行如下的三条命令就可以了：

dbms_java.grant_permission( 'DRAS','SYS:java.io.FilePermission', '/mudrasu01/app/oracle/product/9.2.0/bin/exp','execute' );

dbms_java.grant_permission( 'DRAS','SYS:java.io.FilePermission', '/mudrasu01/app/oracle/product/9.2.0/bin/imp','execute' );

dbms_java.grant_permission( 'DRAS','SYS:java.io.FilePermission', '/bin/cp','execute' );

 

但现在的问题是，同样的历史数据转移作业，为什么在国航、南航没事，单单在东航就出权限问题了？

 

呵呵，原因就出在JAVASYSPRIV上面。

 

我们来做一个例子，重现一下上述问题：

SQL> conn dras/astca@astca;

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0

Connected as dras

 

SQL> select distinct grantee_name from user_java_policy;

 

GRANTEE_NAME

------------------------------

DRAS

JAVASYSPRIV

JAVAUSERPRIV

JAVA_ADMIN

JAVA_DEPLOY

PUBLIC

 

6 rows selected

 

现在dras是有JAVASYSPRIV这个role的，所以应该可以成功执行上述java存储过程，也就是说可以用上述java存储过程调aix上的exp命令。

我们来测一下：

SQL> exec dras.pckexpimp.P_HTRANTBSEXP;

 

PL/SQL procedure successfully completed.

 

oracle:/dras20 $ls -l

total 23091960

drwxr-xr-x   2 oracle   dba            4096 Jan 27 18:56 armshis

drwxr-xr-x   2 oracle   dba             256 Nov 20 09:54 astca

-rw-r-----   1 oracle   dba      11822927872 Jan 10 2008  exp_mas.dmp

-rw-r--r--   1 oracle   dba          122880 Mar 26 11:01 exparmshistemptbs.dmp

-rw-r--r--   1 oracle   dba            3157 Mar 26 11:01 exparmshistemptbs.log

-rw-r--r--   1 oracle   dba           15082 Jan 10 2008  imp_mas.log

-rw-r--r--   1 oracle   dba             133 Jan 27 18:57 imparmshistemptbs.log

drwxr-xr-x   2 oracle   dba             256 Sep 13 2007  lost+found

 

oracle:/dras20 $cat exparmshistemptbs.log

 

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

For tablespace ARMSHISTEMPTBS ...

. exporting cluster definitions

. exporting table definitions

. . exporting table                         SALDAF

. . exporting table                         SALDCF

. . exporting table                         UPLPDF

. . exporting table                         SALDFF

. . exporting table                         SALXBF

. . exporting table               ACCOUNT_ENTRYBAS

. . exporting table               ACCOUNT_ENTRYREV

. . exporting table                         ADCBTH

. . exporting table                         ADCSBH

. . exporting table                         ADCSTK

. . exporting table                         ADCTKD

. . exporting table                         ADJDFT

. . exporting table                         ADJDPR

. . exporting table                         ADJIWC

. . exporting table                         ADJIWD

. . exporting table                         ADJLFT

. . exporting table                         ADJSAC

. . exporting table                         ADJSAD

. . exporting table                         ADJSCC

. . exporting table                         ADJSCD

. . exporting table                         ADJSVD

. . exporting table                         ADJSXC

. . exporting table                         ADJSXD

. . exporting table                         ADJTKN

. . exporting table                         ADJUDL

. . exporting table                         ADJUPL

. . exporting table                         IWBBTH

. . exporting table                         IWBPDT

. . exporting table                         IWBSUB

. . exporting table                         IWBXBG

. . exporting table                         OALTKT

. . exporting table                         PBLSAL

. . exporting table                         PBLSUB

. . exporting table                         PBLSUM

. . exporting table                         PBLUPL

. . exporting table                         PBLXBG

. . exporting table                         SALBTH

. . exporting table                         SALDAT

. . exporting table                         SALDCT

. . exporting table                         SALDFT

. . exporting table                         SALDMG

. . exporting table                         SALDVT

. . exporting table                         SALSUB

. . exporting table                         SALXBG

. . exporting table                         UPLBTH

. . exporting table                         UPLDPR

. . exporting table                         UPLPDT

. . exporting table                         UPLSCB

. . exporting table                         UPLWRK

. . exporting table                         UPLXBG

. exporting referential integrity constraints

. exporting triggers

. end transportable tablespace metadata export

Export terminated successfully without warnings.

我们可以看到上述java存储过程在dras用户具有JAVASYSPRIV这个role的情况下是能够被成功执行的，

 

我们现在把JAVASYSPRIV和JAVAUSERPRIV给revoke掉：

SQL> revoke JAVASYSPRIV from dras;

 

Revoke succeeded.

 

SQL> revoke JAVAUSERPRIV from dras;

 

Revoke succeeded.

 

再来执行上述java存储过程：

SQL> exec dras.pckexpimp.P_HTRANTBSEXP;

BEGIN dras.pckexpimp.P_HTRANTBSEXP; END;

 

*

ERROR at line 1:

ORA-29532: Java call terminated by uncaught Java exception:

java.security.AccessControlException: the Permission (java.io.FilePermission

/cadrasu01/app/oracle/product/9.2.0/bin/exp execute) has not been granted to

DRAS. The PL/SQL to grant this is dbms_java.grant_permission( 'DRAS',

'SYS:java.io.FilePermission', '/cadrasu01/app/oracle/product/9.2.0/bin/exp',

'execute' )

ORA-06512: at "DRAS.PCKEXPIMP", line 0

ORA-06512: at line 1

可以看到，这里我们重现了错误。

 

现在我们来让上述错误消失：

SQL> grant JAVASYSPRIV to dras;

 

Grant succeeded.

 

SQL> exec dras.pckexpimp.P_HTRANTBSEXP;

BEGIN dras.pckexpimp.P_HTRANTBSEXP; END;

 

*

ERROR at line 1:

ORA-29532: Java call terminated by uncaught Java exception:

java.security.AccessControlException: the Permission (java.io.FilePermission

/cadrasu01/app/oracle/product/9.2.0/bin/exp execute) has not been granted to

DRAS. The PL/SQL to grant this is dbms_java.grant_permission( 'DRAS',

'SYS:java.io.FilePermission', '/cadrasu01/app/oracle/product/9.2.0/bin/exp',

'execute' )

ORA-06512: at "DRAS.PCKEXPIMP", line 0

ORA-06512: at line 1

 

 

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

oracle:/dras20 $sqlplus '/ as sysdba';

 

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Mar 26 12:30:10 2009

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

 

SQL> exec dras.pckexpimp.P_HTRANTBSEXP;

 

PL/SQL procedure successfully completed.

 

SQL> select * from role_role_privs;

 

ROLE                           GRANTED_ROLE                   ADMIN_OPTION

------------------------------ ------------------------------ ------------

DBA                            OLAP_DBA                       NO

DBA                            XDBADMIN                       NO

DBA                            JAVA_ADMIN                     NO

DBA                            JAVA_DEPLOY                    NO

DBA                            WM_ADMIN_ROLE                  NO

DBA                            DELETE_CATALOG_ROLE            YES

DBA                            SELECT_CATALOG_ROLE            YES

DBA                            EXECUTE_CATALOG_ROLE           YES

DBA                            GATHER_SYSTEM_STATISTICS       NO

OLAP_DBA                       SELECT_CATALOG_ROLE            NO

JAVASYSPRIV                    JAVAUSERPRIV                   NO

SELECT_CATALOG_ROLE            HS_ADMIN_ROLE                  NO

EXECUTE_CATALOG_ROLE           HS_ADMIN_ROLE                  NO

 

13 rows selected

 

从上述结果中我们可以很清晰的看到有如下结论：

1、JAVASYSPRIV包含JAVAUSERPRIV。

2、要想正常的执行java存储过程，只需要JAVASYSPRIV这个role就可以了。

3、在赋予JAVASYSPRIV这个role后，如果要马上看到效果，需要断开当前session、再重新连接一下。

4、oracle存储过程的默认authid为definer，这种情况下oracle是看不到role里的权限的，但是从上面结果里我们可以看到对java存储过程，没有这个限制。

 

最后来回答一下这篇文章开头的问题，即为什么国航、南航可以，但东航就不行?

说来也简单，我问过我同事了，她告诉我国航、南航的dras用户有JAVASYSPRIV这个role，但东航没有。

Categories:

Leave a comment