将ORACLE用户的对象赋权另外ORACLE用户操作

环境：
E3JAVA5.8应用需要将oracle用户E3ssscc的表、存储过程、触发器的查询和执行权限赋予E3sss.
1.表的赋权：
A.执行如下存储过程（需要赋予参数为：E3SSS）即可拼凑出赋予查询权限的SQL并执行该SQL：
create or replace procedure synon(p_touser varchar2) as
begin
  declare
    v_sql varchar2(300) ;
    cursor mycur(touser varchar2) is
      select 'grant select on ' || s.table_name || ' to ' || touser
        from user_tables s;
  begin
    open mycur(p_touser);
    loop
      fetch mycur
        into v_sql;
      exit when mycur%notfound;
      execute immediate v_sql;
    end loop;
  end;
end;

B.在E3SSS用户下创建同义词的操作：
在E3ssscc用户下执行如下语句：
grant creat synonym to E3SSS;
select 'create synonym '||table_name||' for E3SSSCC.'||table_name||';' from user_tables;
拷贝查询的结果到E3sss用户下执行，到此完成了表的赋权。可以以E3SSS用户由P/L登录
2.存储过程的赋权：
A.执行如下存储过程（需要明确几个参数）即可将E3ssscc用户下的存储过程，赋权给E3sss去执行:

create or replace procedure synon_2(p_type     varchar2,
                                      p_privelege varchar2,
                                      p_fromuser varchar2,
                                      p_touser   varchar2) as
begin
  declare
    v_sql varchar2(300) default 'grant create synonym to ' || p_touser;

    cursor mycur(vtype varchar2, privelege varchar2, fromuser varchar2, touser varchar2) is
      select privelege||' on ' || t.name || ' to ' || touser
        from ALL_SOURCE t
       where t.type = upper(vtype)
         and t.owner = upper(fromuser)
       group by t.name
       order by t.name;
 
  begin
    execute immediate v_sql;
    open mycur(p_type, p_privelege,p_fromuser, p_touser);
    loop
      fetch mycur
        into v_sql;
      exit when mycur %notfound;
      execute immediate v_sql;
    end loop;
  end;
end;
说明：
  p_privelege可选值：grant create|select|insert|update|delete|execute.....操作SQL;
  p_type可选值：PROCEDURE、PACKAGE、PACKAGE BODY、TYPE BODY、TRIGGER、FUNCTION、TYPE;
  p_fromuser：源ORACLE用户
  P_touser:被目的ORACLE用户
B、在E3ssscc用户下拼凑出创建同义词的sql：
select distinct 'create synonym '||t.name||' for E3SSSCC.'||t.name||';' from All_Source t
where t.owner='E3SSSCC'and TYPE='PROCEDURE';
C、在E3SSS下执行B中sql的结果。

3.触发器赋权
A.执行如下存储过程（需要明确几个参数）即可将E3ssscc用户下的触发器赋权给E3sss去执行:

create or replace procedure synon_2(p_type     varchar2,
                                      p_privelege varchar2,
                                      p_fromuser varchar2,
                                      p_touser   varchar2) as
begin
  declare
    v_sql varchar2(300) default 'grant create synonym to ' || p_touser;

    cursor mycur(vtype varchar2, privelege varchar2, fromuser varchar2, touser varchar2) is
      select privelege||' on ' || t.name || ' to ' || touser
        from ALL_SOURCE t
       where t.type = upper(vtype)
         and t.owner = upper(fromuser)
       group by t.name
       order by t.name;
 
  begin
    execute immediate v_sql;
    open mycur(p_type, p_privelege,p_fromuser, p_touser);
    loop
      fetch mycur
        into v_sql;
      exit when mycur %notfound;
      execute immediate v_sql;
    end loop;
  end;
end;
说明：
  p_privelege可选值：grant create|select|insert|update|delete|execute.....操作SQL;
  p_type可选值：PROCEDURE、PACKAGE、PACKAGE BODY、TYPE BODY、TRIGGER、FUNCTION、TYPE;
  p_fromuser：源ORACLE用户
  P_touser:目的ORACLE用户
B在E3ssscc用户下拼凑出创建同义词的sql：
select distinct 'create synonym '||t.name||' for E3SSSCC.'||t.name||';' from All_Source t
where t.owner='E3SSSCC'and TYPE='TRIGGER';
C、在E3SSS下执行B中sql的结果。
4.视图赋权
A.在E3SSSCC用户下执行如下SQL,并将SQl得查询结果在E3SSSCCC用户下执行：
select 'grant select on '||OBJECT_NAME||' to E3SSS'||';' from dba_objects where owner = 'E3SSSCC' and OBJECT_TYPE='VIEW';

B.创建同义词

grant creat synonym to E3SSS;

select 'create synonym '||OBJECT_NAME||' for E3SSSCC.'||OBJECT_NAME ||';' from dba_objects where owner = 'E3SSSCC' and OBJECT_TYPE='VIEW';
C.将B中的查询结构，在E3SSS用户下执行。