Oracle Database TIP
한 Schema의 모든 Table에 대해 권한을 부여하는 프로시저 7 1 20,679

by 김정식 DBMS_SQL 권한 [2002.05.08]


  한 Schema의 모든 테이블에 대한 객체 권한을 부여하는 프로시저로, DBMS_SQL을 이용해서 구현되어 있다.

 
-- 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;
    

프로시저 생성

  첫번째 IN 파라미터는 'select', 'insert', 'update'...와 같은 privilege를 지정 합니다.

  두번째 IN 파라미터는 권한을 부여할 schema를 지정 합니다.

 
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;
    /
    

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

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

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
...
위와 같이 권한이 부여된 것을 확인 할 수 있다. 
    

- 강좌 URL : http://www.gurubee.net/lecture/1340

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 지나가다 [2008.12.04 18:28:40]
잘보고갑니다.
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입