-- DBMS_SQL패키지를 사용하기 위해서는 아래의 스크립트를 실행해야 한다. 
-- SYS user로 접속해서 스크립트를 실행 시키고 대상 유저한테 권한을 부여 한다. 

C:\> SQLPLUS / NOLOG
SQL> CONN / AS SYSDBA
SQL> @$ORACLE_HOME$/rdbms/admin/dbmssql.sql  
 
SQL> GRANT create any table TO scott;
SQL> GRANT execute on dbms_sql TO scott;
    

 
SQL> CREATE OR REPLACE PROCEDURE grant_on_table 
        (privs IN VARCHAR2, user_name IN VARCHAR2)

     AS
        cursor1  INTEGER;
        cursor2  INTEGER;
  
        t_name VARCHAR2(30);
        rows_proces1  INTEGER;
        rows_processed  INTEGER;
 
     BEGIN
 
        cursor1 := DBMS_SQL.OPEN_CURSOR;
        cursor2 := DBMS_SQL.OPEN_CURSOR;

        DBMS_SQL.PARSE (cursor1, 'select table_name from user_tables', dbms_sql.v7);
        DBMS_SQL.DEFINE_COLUMN (cursor1, 1, t_name, 30);

        rows_processed := DBMS_SQL.EXECUTE (cursor1);
 
        LOOP
          if DBMS_SQL.FETCH_ROWS (cursor1) > 0 then
            DBMS_SQL.COLUMN_VALUE (cursor1, 1, t_name);
            DBMS_SQL.PARSE(cursor2,'grant '|| privs ||'  on '|| t_name || ' to ' 
                        || user_name,dbms_sql.native);
            rows_proces1 := DBMS_SQL.EXECUTE(cursor2);
          else
            exit;
          end if;
        END LOOP;
  
        DBMS_SQL.CLOSE_CURSOR (cursor2);
        DBMS_SQL.CLOSE_CURSOR (cursor1);
 
    EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(sqlerrm);
         if DBMS_SQL.IS_OPEN (cursor1) then
            DBMS_SQL.CLOSE_CURSOR (cursor1);
         end if;
    END;
    /
    

 
먼저 권한을 부여할 유저로 접속을 해서 위의 프로시저를 생성 합니다. 

C:\> SQLPLUS scott/tiger

-- soctt유저의모든 객체 권한을 test유저에게 부여 합니다.  
SQL> EXEC grant_on_table('all', 'test'); 
 
-- test유저로 접속한 다음에 권한이 부여？는지 확인 합니다. 
SQL> COMM test/test
 
SQL> SET PAGESIZE 1000 
SQL> SET LINESIZE  300 
SQL> COL grantee FORMAT A15
SQL> COL privilege FORMAT A10
SQL> COL owner FORMAT A10
SQL> COL table_name FORMAT A10


SQL> SELECT grantee, privilege, owner, table_name 
     FROM  USER_TAB_PRIVS;
 
GRANTEE   PRIVILEGE  OWNER      TABLE_NAME
--------- ---------- ---------- ----------
TEST      ALTER      SCOTT      EMP
TEST      DELETE     SCOTT      EMP
TEST      INDEX      SCOTT      EMP
...
위와 같이 권한이 부여된 것을 확인 할 수 있다.