RMS系统用户的创建非常繁琐，而且需要后台处理，这里总结步骤如下：
1、在数据库后台创建DB用户。
2、对创建的用户授予角色权限、系统权限。
以上两步可使用以下SQL脚本完成：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | /*================================================== Copyright (C) HAND Enterprise Solutions Co.,Ltd. AllRights Reserved ==================================================*/ /*================================================== Program Name: CREATE RMS USER Description: This program create Oracle rms db user and grant all privileges . History: 1.00 2008-11-10 Kenneth.shao Creation 2.00 2008-11-10 Kenneth.shao Modified 修改角色授权，增加选择角色功能，可以支持授权多个角色，用“,”号分割 如：developer,select_catalog_role Version: 2.00 ==================================================*/ DECLARE l_user_name VARCHAR2(30) := '&USER_NAME' ; l_roles VARCHAR2(400) := '&ROLE' ; l_crt_user varchar2(300) := 'create user '|| l_user_name ||' identified by "' || l_user_name || '" default tablespace RETEK_DATA temporary tablespace TEMP profile DEFAULT quota unlimited on lob_data quota unlimited on retek_data quota unlimited on retek_index'; l_grant VARCHAR2(4000) ; BEGIN --Create user EXECUTE IMMEDIATE l_crt_user ; --Grant directory l_grant := 'grant read on directory SYS.OUTPUT_WORKING to '||l_user_name; EXECUTE IMMEDIATE l_grant ; l_grant := 'grant read on directory SYS.UTL_FILE_DIR to '||l_user_name||' with grant option'; EXECUTE IMMEDIATE l_grant ; --Grant role privileges l_grant := 'grant ' || l_roles || ' to '||l_user_name ; EXECUTE IMMEDIATE l_grant ; --Grant system privileges l_grant := 'grant alter session , analyze any , create any context , create any procedure , create any synonym , create any table , create any type , create database link , create library , create materialized view , create procedure , create public database link , create public synonym , create sequence , create session , create synonym , create table , create trigger , create view , debug connect session , delete any table , drop any procedure , drop any synonym , drop any table , execute any procedure , execute any type , insert any table , query rewrite , select any sequence , select any table , update any table to '||l_user_name ; EXECUTE IMMEDIATE l_grant ; END ; |
3、为新建DB用户创建RMS12DEV（现行环境下的用户）下对象的同义词。可使用如下脚本执行：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | /*================================================== Copyright (C) HAND Enterprise Solutions Co.,Ltd. AllRights Reserved ==================================================*/ /*================================================== Program Name: CREATE RMS SYNONYM Description: This program create all synonym for rms12dev to rms new user . History: 1.00 2008-11-10 Kenneth.shao Creation Version: 1.00 ==================================================*/ DECLARE l_user VARCHAR2(30) := '&USER_NAME'; BEGIN FOR syn_r IN (SELECT DISTINCT 'create or replace synonym '|| l_user || '.' || object_name || ' for rms12dev.' || object_name syn FROM dba_objects WHERE owner = 'RMS12DEV' AND object_name NOT LIKE 'BIN$%' AND object_type IN ('SEQUENCE' ,'PROCEDURE' ,'PACKAGE' ,'LOB' ,'LIBRARY' ,'MATERIALIZED VIEW' ,'TABLE' ,'VIEW' ,'FUNCTION' ,'TYPE') ) LOOP BEGIN EXECUTE IMMEDIATE syn_r.syn ; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(syn_r.syn||'-----'||SQLERRM) ; END ; END LOOP; END ; |
4、在RMS系统中设置用户属性，位置：启动->控制->设置->用户属性->新建。
5、设置用户组，位置：启动->控制->系统->地点/产品安全性->用户/组链接->编辑。（设置之前必须保证组已经建立并赋予一定的权限，这里不详细讲述）。
到这里为止，用户已经成功创建，只要组已经设置并赋权，用户便可以使用。