한 Schema의 모든 Table에 대해 권한을 부여하는 프로시저

Bulletin no : 11984번을 참조했습니다.
 
 
한 Schema의 모든 table에 대한 객체 권한을 부여하는 프로시저로,  DBMS_SQL을 이용해서 구현되어 있습니다.
 
 
DBMS_SQL패키지를 사용하기 위해서는 아래의 스크립트를 실행해야 합니다.
SYS user로 접속해서 스크립트를 실행 시키고 대상 유저한테 권한을 부여 합니다.

> @$ORACLE_HOME$/rdbms/admin/dbmssql.sql 
 
> grant create any table to [user_name];
> grant execute on dbms_sql to [user_name];
 
 
첫번째 IN 파라미터는  'select', 'insert', 'update'...와 같은 privilege를 지정 합니다.
두번째 IN 파라미터는 권한을 부여할 schema를 지정 합니다.
 
실행 예)
 -- grant_on_table(권한명, 유저명)
SQL>EXEC grant_on_table('select', 'to_user');
 
 

▣ 프로시저

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;

 

▣ test유저한테 모든 객체 권한을 부여하는 예제

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

SQLPLUS scott/tiger

-- soctt유저의모든 객체 권한을 test유저에게 부여 합니다. 
SQL>EXEC grant_on_table('all', 'test');
PL/SQL 처리가 정상적으로 완료되었습니다.
 

test유저로 접속한 다음에 권한이 부여？는지 확인 합니다.
SQL>conn 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
...
위와 같이 권한이 부여된 것을 확인 할 수 있습니다.