POUWIEL|COM

[15:28:03_DBA_ORCL@TSERVER] TST 
SQL> create user sw_owner identified by sw_owner;
SQL>   grant create session, create table to sw_owner;
SQL>   grant create procedure to sw_owner;
SQL>   alter user sw_owner quota 5m on  users;
SQL>   grant grant any role to sw_owner;

SQL> create user sw_admin identified by sw_admin;
SQL>   grant create session to sw_admin;

SQL> create user sw_user1 identified by sw_user1;
SQL>   grant create session to sw_user1;

SQL> create table sw_owner.sw_table1 (a number);
SQL>   insert into sw_owner.sw_table1 (a) values (1);
SQL>   insert into sw_owner.sw_table1 (a) values (2);
SQL>   insert into sw_owner.sw_table1 (a) values (3);
SQL>   commit;

SQL> create role sw_role1;
SQL> grant select on sw_owner.sw_table1 to sw_role1;


SQL> CREATE OR REPLACE PACKAGE sw_owner.sw_package1
  2  IS
  3     PROCEDURE sw_procedure1 (p_grantee varchar2);
  4  END;
  5  /

SQL> CREATE OR REPLACE PACKAGE BODY sw_owner.sw_package1
  2  IS
  3  -- ---------------------------------------------------
  4     PROCEDURE sw_procedure1 (p_grantee  VARCHAR2)
  5     IS
  6     v_query       varchar2(200);
  7     BEGIN
  8       v_query := 'GRANT sw_role1 to '|| p_grantee;
  9       EXECUTE IMMEDIATE v_query;
 10     END;
 11  END;
 12  /
SQL> create public synonym sw_package1 for sw_owner.sw_package1;

SQL> grant execute on sw_owner.sw_package1 to sw_admin;

SQL> conn sw_admin/sw_admin@orcl
SQL> execute sw_package1.sw_procedure1('sw_user1');
SQL> conn sw_user1/sw_user1@orcl
SQL> select * from sw_owner.sw_table1;

         A
----------
         1
         2
         3