Issue of Oracle user permissions caused by IMPDP import ____oracle

According to business requirements, all objects under the test user on the database are replicated to a new user TEST2, but no table data is required. The idea is to first use the EXPDP content=metadata_only parameters to export metadata, and then use IMPDP remap_schema=test:test2 to convert to complete the business requirements.

Once submitted to the business unit, it is also requested that the table space used by the TEST2 business table be assigned to the specified table space and separated from the index table space.

Complete using the following policy:

First use EXPDP to export TEST2, the following command:

Nohup EXPDP system/system directory=expdp schemas=test2 dumpfile=test0213.dmp Logfile=test0213.log &

The table space for the data is then modified and the index import is excluded, as follows:

Nohup IMPDP system/system DIRECTORY=EXPDP dumpfile=test0213.dmp logfile=test0213_table.log: Test_data schemas=test2 Exclude=index &

To modify the table space of the index and import only the index, the command is as follows:

Nohup IMPDP system/system directory=expdp1 dumpfile=test0213.dmp logfile=test0213_index.log: Test_index schemas=test2 Include=index &

After the import is complete through dba_segments view verify that the tablespace of Segment_type index is still difficult to test, further view is because the index, which is automatically created when a primary key constraint is created for the database, is modified using the following command:

ALTER INDEX index_name REBUILD tablespace test_index;

Submitted to the business unit after completion, the result is that TEST2 users have the right to query test user object, request to remove.

After verification, test only gives connect and resource permissions, the normal situation is not access to other users of information, but the user does exist in this situation.

By looking up user_role_privs (viewing user-owned role permissions, such as Connect,resource), User_tab_privs (viewing users owning queries, modifying the table permissions specified by other users) is still discovering an exception. The problem can be judged by default to export the user's permission information when the test user exports, and when importing this permission to the new user TEST2, causing TEST2 to have some special permissions.

Using the Plsql Develop tool to view the creation statements of the TEST2 user, a large number of permissions are found to protect the grant select any table permission, which causes it to have permissions to query other users, with the following permissions information:

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 is to TEST2;
Grant ALTER any sequence to TEST2;
Grant alter any SQL profiles to TEST2;
Grant alter any table to TEST2;
Grant ALTER any trigger to TEST2;
Grant alter any of 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 builds 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 the 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 profiles 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 the 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 builds 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 the 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 profiles 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 the 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 builds process to TEST2;
Grant update any cube dimension to TEST2;
Grant update any table to TEST2;

Workaround: Use the DBA user for permission recovery, the following command:

Revoke administer any SQL tuning set from TEST2;
Revoke administer database trigger from TEST2;
Revoke administer SQL Management object from TEST2;

At this point, the completion of the business requirements, but also by the way to the problems encountered and solutions summed up.