Oracle 数据逻辑恢复及权限控制一例

近日，应公司内部东南亚Regional Integrated Management Information System项目组需求，完成下述测试环境的搭建：

1 在现有开发库中，创建一个新的名为RIMISADMIN的schema，将现有RIMIS这个schema下的所有数据逻辑备份出来并导入到新建的RIMISADMIN这个schema下；

2 另外创建一个新的测试schema 名为RIMISTEST，该测试schema与RIMISADMIN公用一份数据，并限制其权限仅可以执行DML语句，对数据表有增删改查功能，不允许执行DDL语句，同时不允许对如下表执行DML语句操作：
cd_menu;cd_functionpoint;cd_menu_function;cd_office_fmenu;cd_role_fmenu;cd_office_biz_config;
cd_serial_no;cd_codedict;cd_codetype;edi_code_table;edi_code_type;pr_report_config;
上述内容是公司同事发给我的一封邮件，当然是我与需求同事沟通确认后，整理出来的需求信息。

我的解决思路：
1 在现有RAC数据库中将RIMIS schema用户下的所有数据用EXPDP按照schema模式全部逻辑导出；

2 准备使用IMPDP工具附带remap_schema=rimis:rimisadmin参数全部导入，在这一步执行之前确认RAC数据库的存储信息时，发现ASM磁盘组的总空间为236G，剩余空间只有区区的30G左右。而原有RIMIS schema下的所有数据在数据库中占用将近55G的空间，看来不能直接在这台RAC数据库上搭建这个测试环境，除非先给ASM磁盘组添加磁盘，而开发库又需要给各个项目组使用，不好直接停服务加磁盘，怕影响其他项目组进度。只好选择一种迂回的方案，先将测试环境搭建到另外一套数据库中，等有机会的话，再停RAC数据库，然后添加存储之后另作处理。

3 在新的测试环境上执行逻辑导入，导入之前，在新环境下创建必要的表空间，表空间名需要与原环境下RIMIS用户对象所在表空间名相同，否则，在导入时就得指定REMAP_TABLESPACE选项了，不然，就不可能顺利导入。具体的导入的命令如下：

impdp directory=mig_dir dumpfile=expdp_rimis_20111114.dmp logfile=impdp_rimis.log  remap_schema=rimis:rimisadmin exclude=table_statistics

4 导入之后，发现有些对象编译失效，图省事，执行下述命令直接编译RIMISADMIN schema下的所有对象：

SQL> exec dbms_utility.compile_schema('RIMISADMIN');

5 以SYS用户登录数据库，修改RIMISADMIN schema的口令，并确认RIMISADMIN 可以正常访问数据库；

[oracle@OEL511gR2 mig_dir]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Nov 16 16:10:42 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn / as sysdba
Connected.
SQL> alter user rimisadmin identified by fjxm;
User altered.
SQL> conn rimisadmin/fjxm;
Connected.
SQL>

6 新环境下创建RIMIS schema,原来需求是创建RIMISTEST，又经更改需求要命名为RIMIS，并只给RIMIS用户赋予最小CREATE SESSION的权限及CREATE SYNONYM(见第8步，具体用处)的权限：

SQL> conn / as sysdba
Connected.
SQL> create user rimis identified by rimis;
User created.
SQL> grant create session to rimis;
Grant succeeded.
SQL> conn rimis/rimis
Connected.
SQL>

7 给RIMIS用户赋予可以对RIMISADMIN对象的增删改查的权限，见下述SQL：

SQL> conn rimisadmin/fjxm
Connected.
SQL> spool /home/oracle/privs.sql
SQL> select 'grant select,insert,update,delete on '||table_name||' to rimis;' from user_tables;
SQL> spool off;

简单修改/home/oracle/privs.sql后，由RIMISADMIN用户执行/home/oracle/privs.sql赋权脚本。

8 RIMIS用户创建一系列的同义词：
RIMISADMIN先生成创建同义词的脚本：

SQL> conn rimisadmin/fjxm
Connected.
SQL> spool /home/oracle/create_synonym.sql
SQL> select 'create synonym '||table_name||' for rimisadmin.'||table_name||';' from user_tables;
SQL> spool off;

然后交由RIMIS用户执行上述创建同义词的脚本；
至此，RIMIS用户就可以正常访问RIMISADMIN用户下的对象。只是，还有一个特殊的上述那些表的权限控制，不能执行DML操作。

9 移除对特定表的增删改的权限：

SQL> conn rimisadmin/fjxm
Connected.
SQL> select 'revoke insert,update,delete on '||table_name||' from rimis;'
   2 from user_tables
   3 where lower(table_name) in ('cd_menu','cd_functionpoint','cd_menu_function',
   4 'cd_office_fmenu','cd_role_fmenu','cd_office_biz_config','cd_serial_no',
   5 'cd_codedict','cd_codetype','edi_code_table','edi_code_type','pr_report_config');

至此，完成RIMIS项目组的数据逻辑恢复、及相关权限的控制。以前，在数据库日常管理中，很少有遇到权限比较细化的场景，所以简单做一记录。
在发布本博文前，收到同事的邮件，说权限已经控制OK！