EXP-00009: no privilege to export

xjprmapp:jfexp:/jfexp>exp monitor/monitor@crmdb tables=dbcustadm.dcustdoc file=dcutdoc.dmp direct=y

Export: Release 9.2.0.1.0 - Production on Tue Apr 28 22:49:18 2009

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path ...
EXP-00009: no privilege to export DBCUSTADM's table DCUSTDOC
Export terminated successfully with warnings.

到库里查看权限

select * from dba_sys_privs where grantee='MONITOR'

MONITOR|SELECT ANY DICTIONARY|NO
MONITOR|SELECT ANY TRANSACTION|NO
MONITOR|EXPORT FULL DATABASE|NO
MONITOR|SELECT ANY SEQUENCE|NO
MONITOR|ALTER SYSTEM|NO
MONITOR|SELECT ANY TABLE|NO

都有EXPORT FULL DATABASE的权限了，太奇怪了，上网google了一把

The only possible cause is that the role granted to user has not be enabled. Therefore, we issue the following command:

SQL> alter user wstumgr default role exp_full_database,ban_default_m,ban_default_q,connect;

User altered.

The above command will set role exp_full_database as default role along with other existing default roles. We then go back the check the oracle documents.

Oracle doc say:

“When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user’s default roles.”

Here, I’d like to include part of oracle doc about granting privileges to users.

 

When Do Grants and Revokes Take Effect?

Depending on what is granted or revoked, a grant or revoke takes effect at different times:

All grants/revokes of system and object privileges to anything (users, roles, and PUBLIC) are immediately observed. All grants/revokes of roles to anything (users, other roles, PUBLIC) are only observed when a current user session issues a SET ROLE statement to re-enable the role after the grant/revoke, or when a new user session is created after the grant/revoke.

意思就是说role没有enable，我们来看看是否是真的

select * from dba_role_privs where grantee='MONITOR'

MONITOR|RESOURCE|NO|NO
MONITOR|CONNECT|NO|NO
MONITOR|EXP_FULL_DATABASE|NO|NO

DEFAULT_ROLE VARCHAR2(3)   Indicates whether the role is designated as a DEFAULT ROLE for the user (YES) or not (NO

DEFAULT_ROLE 果然都是no，

alter user monitor default role RESOURCE,CONNECT,EXP_FULL_DATABASE;

再看下视图都变成yes了，再exp，果然没有问题

 

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

转载于:http://blog.itpub.net/16158219/viewspace-592127/