由impdp导入引起的oracle用户权限的问题

      根据业务要求需要将数据库上的TEST用户下的所有对象复制到一个新建的用户TEST2上，但不需要表数据。想到的办法是先用expdp的CONTENT=METADATA_ONLY参数导出元数据，然后使用impdp的remap_schema=TEST:TEST2来转换，完成业务要求。

      提交给业务部门后，又提出要求需要将TEST2业务表使用的表空间与索引表空间分配到指定表空间并分开。

使用如下策略完成：

      首先使用expdp将TEST2导出，命令如下：

      nohup expdp system/system directory=expdp schemas=test2 dumpfile=test0213.dmp logfile=test0213.log  &

      然后修改数据的表空间并排除索引导入，命令如下：

      nohup impdp system/system directory=expdp  dumpfile=test0213.dmp logfile=test0213_table.log remap_tablespace=TEST:TEST_DATA  schemas=TEST2 exclude=index &

      再修改索引的表空间并只导入索引，命令如下：

      nohup impdp system/system directory=expdp1  dumpfile=test0213.dmp logfile=test0213_index.log remap_tablespace=TEST:TEST_INDEX schemas=TEST2 include=index  &

     导入完成后通过dba_segments查看验证发现segment_type为index的tablespace仍难有为TEST的，进一步查看是因为该索引为数据库创建主键约束时自动创建的索引，使用下面的命令进行修改：

      ALTER INDEX INDEX_NAME REBUILD TABLESPACE TEST_INDEX;

      完成后提交给业务部门，结果又提出TEST2用户拥有查询TEST用户对象的权限，要求去除掉。

     经过验证核实，TEST只赋予了connect 以及resource权限，正常情况是不能访问其他用户的信息的，但该用户确实存在该种情况。

     通过查找user_role_privs（查看用户拥有的角色权限，如connect，resource），user_tab_privs（查看用户拥有 查询、修改其他用户的下指定的表权限），仍为发现异常。可以判断问题出在以TEST用户导出时默认将该用户的权限信息导出，而导入时又将该权限赋予了新用户TEST2，导致TEST2拥有一些特殊权限。

     使用plsql develop工具查看TEST2用户的创建语句，发现有大量权限赋予，其中保护grant select any table权限，从而导致其拥有查询其他用户的权限，权限信息如下：

grant administer any sql tuning set to TEST2;
grant administer database trigger to TEST2;
grant administer sql management object to TEST2;
grant administer sql tuning set to TEST2;
grant advisor to TEST2;
grant alter any assembly to TEST2;
grant alter any cluster to TEST2;
grant alter any cube to TEST2;
grant alter any cube dimension to TEST2;
grant alter any dimension to TEST2;
grant alter any edition to TEST2;
grant alter any index to TEST2;
grant alter any indextype to TEST2;
grant alter any library to TEST2;
grant alter any materialized view to TEST2;
grant alter any mining model to TEST2;
grant alter any operator to TEST2;
grant alter any outline to TEST2;
grant alter any procedure to TEST2;
grant alter any role to TEST2;
grant alter any sequence to TEST2;
grant alter any sql profile to TEST2;
grant alter any table to TEST2;
grant alter any trigger to TEST2;
grant alter any type to TEST2;
grant alter database to TEST2;
grant alter profile to TEST2;
grant alter resource cost to TEST2;
grant alter rollback segment to TEST2;
grant alter session to TEST2;
grant alter system to TEST2;
grant alter tablespace to TEST2;
grant alter user to TEST2;
grant analyze any to TEST2;
grant audit any to TEST2;
grant audit system to TEST2;
grant backup any table to TEST2;
grant become user to TEST2;
grant change notification to TEST2;
grant comment any mining model to TEST2;
grant comment any table to TEST2;
grant create any assembly to TEST2;
grant create any cluster to TEST2;
grant create any context to TEST2;
grant create any cube to TEST2;
grant create any cube build process to TEST2;
grant create any cube dimension to TEST2;
grant create any dimension to TEST2;
grant create any directory to TEST2;
grant create any edition to TEST2;
grant create any index to TEST2;
grant create any indextype to TEST2;
grant create any job to TEST2;
grant create any library to TEST2;
grant create any materialized view to TEST2;
grant create any measure folder to TEST2;
grant create any mining model to TEST2;
grant create any operator to TEST2;
grant create any outline to TEST2;
grant create any procedure to TEST2;
grant create any sequence to TEST2;
grant create any sql profile to TEST2;
grant create any synonym to TEST2;
grant create any table to TEST2;
grant create any trigger to TEST2;
grant create any type to TEST2;
grant create any view to TEST2;
grant create assembly to TEST2;
grant create cluster to TEST2;
grant create cube to TEST2;
grant create cube build process to TEST2;
grant create cube dimension to TEST2;
grant create database link to TEST2;
grant create dimension to TEST2;
grant create external job to TEST2;
grant create indextype to TEST2;
grant create job to TEST2;
grant create library to TEST2;
grant create materialized view to TEST2;
grant create measure folder to TEST2;
grant create mining model to TEST2;
grant create operator to TEST2;
grant create procedure to TEST2;
grant create profile to TEST2;
grant create public database link to TEST2;
grant create public synonym to TEST2;
grant create role to TEST2;
grant create rollback segment to TEST2;
grant create sequence to TEST2;
grant create session to TEST2;
grant create synonym to TEST2;
grant create table to TEST2;
grant create tablespace to TEST2;
grant create trigger to TEST2;
grant create type to TEST2;
grant create user to TEST2;
grant create view to TEST2;
grant debug any procedure to TEST2;
grant debug connect session to TEST2;
grant delete any cube dimension to TEST2;
grant delete any measure folder to TEST2;
grant delete any table to TEST2;
grant drop any assembly to TEST2;
grant drop any cluster to TEST2;
grant drop any context to TEST2;
grant drop any cube to TEST2;
grant drop any cube build process to TEST2;
grant drop any cube dimension to TEST2;
grant drop any dimension to TEST2;
grant drop any directory to TEST2;
grant drop any edition to TEST2;
grant drop any index to TEST2;
grant drop any indextype to TEST2;
grant drop any library to TEST2;
grant drop any materialized view to TEST2;
grant drop any measure folder to TEST2;
grant drop any mining model to TEST2;
grant drop any operator to TEST2;
grant drop any outline to TEST2;
grant drop any procedure to TEST2;
grant drop any role to TEST2;
grant drop any sequence to TEST2;
grant drop any sql profile to TEST2;
grant drop any synonym to TEST2;
grant drop any table to TEST2;
grant drop any trigger to TEST2;
grant drop any type to TEST2;
grant drop any view to TEST2;
grant drop profile to TEST2;
grant drop public database link to TEST2;
grant drop public synonym to TEST2;
grant drop rollback segment to TEST2;
grant drop tablespace to TEST2;
grant drop user to TEST2;
grant execute any assembly to TEST2;
grant execute any class to TEST2;
grant execute any indextype to TEST2;
grant execute any library to TEST2;
grant execute any operator to TEST2;
grant execute any procedure to TEST2;
grant execute any program to TEST2;
grant execute any type to TEST2;
grant execute assembly to TEST2;
grant export full database to TEST2;
grant flashback any table to TEST2;
grant flashback archive administer to TEST2;
grant force any transaction to TEST2;
grant force transaction to TEST2;
grant global query rewrite to TEST2;
grant grant any object privilege to TEST2;
grant grant any privilege to TEST2;
grant grant any role to TEST2;
grant import full database to TEST2;
grant insert any cube dimension to TEST2;
grant insert any measure folder to TEST2;
grant insert any table to TEST2;
grant lock any table to TEST2;
grant manage scheduler to TEST2;
grant manage tablespace to TEST2;
grant merge any view to TEST2;
grant on commit refresh to TEST2;
grant query rewrite to TEST2;
grant restricted session to TEST2;
grant resumable to TEST2;
grant select any cube to TEST2;
grant select any cube dimension to TEST2;
grant select any mining model to TEST2;
grant select any sequence to TEST2;
grant select any table to TEST2;
grant select any transaction to TEST2;
grant under any table to TEST2;
grant under any type to TEST2;
grant under any view to TEST2;
grant update any cube to TEST2;
grant update any cube build process to TEST2;
grant update any cube dimension to TEST2;
grant update any table to TEST2;

解决办法：使用dba用户进行权限收回，命令如下：

revoke administer any sql tuning set from  TEST2;
revoke administer database trigger from TEST2;
revoke administer sql management object from TEST2;

至此，终于完成业务要求，也顺便将该次碰到的问题及解决思路汇总。