Oracle -- Grant privilege

CREATE TABLE "GRANT_PRIVS_INFO"
   ( "GRANT_REVOKE" VARCHAR2(1000 BYTE),
 "PRIVS" VARCHAR2(100 BYTE),
 "TABLE_NAME" VARCHAR2(64 BYTE),
 "GRANTEE" VARCHAR2(100 BYTE)
   )   ;

create or replace PROCEDURE                     GRANT_PRIVS (
 err_msg OUT VARCHAR2,
    err_code OUT NUMBER)
is
L_SP_NAME VARCHAR2(100):='GRANT_PRIVS';
SQL_TXT VARCHAR2(200);
Type T_GRANT_REVOKE IS table of GRANT_PRIVS_INFO.GRANT_REVOKE%type INDEX BY pls_integer ; 
Type T_PRIVS IS table of GRANT_PRIVS_INFO.PRIVS%type INDEX BY pls_integer ; 
Type T_TABLE_NAME IS table of GRANT_PRIVS_INFO.TABLE_NAME%type INDEX BY pls_integer ; 
Type T_GRANTEE IS table of GRANT_PRIVS_INFO.GRANTEE%type INDEX BY pls_integer ; 
Cursor C_GRANT is select * from GRANT_PRIVS_INFO;
GRANT_REVOKE T_GRANT_REVOKE;
PRIVS T_PRIVS;
TABLE_NAME T_TABLE_NAME;

GRANTEE T_GRANTEE;

BEGIN
open C_GRANT;
FETCH C_GRANT BULK COLLECT INTO GRANT_REVOKE,PRIVS,TABLE_NAME,GRANTEE;
IF GRANT_REVOKE.FIRST>0 THEN                                                                                          
      FOR I IN GRANT_REVOKE.FIRST .. GRANT_REVOKE.LAST  LOOP  
      IF upper(GRANT_REVOKE(I)) = 'GRANT' THEN
      SQL_TXT := GRANT_REVOKE (I) || ' ' || PRIVS(I) || ' ON ' || TABLE_NAME(I) || ' TO ' || GRANTEE(I);
      ELSE
      SQL_TXT := GRANT_REVOKE(I) || ' ' || PRIVS(I) || ' ON ' || TABLE_NAME(I) || ' FROM ' || GRANTEE(I);
      END IF;
      EXECUTE IMMEDIATE (SQL_TXT);

      END LOOP;
 END IF; 
 CLOSE C_GRANT;

END;
/

最后

以上就是糟糕水杯为你收集整理的Oracle -- Grant privilege的全部内容，希望文章能够帮你解决Oracle -- Grant privilege所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。