由impdp匯入引起的oracle使用者權限的問題____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;

至此，終於完成業務要求，也順便將該次碰到的問題及解決思路匯總。