Oracle 使用者許可權獲取Procedure

CREATE OR REPLACE PROCEDURE grant_priv

      /*

            grant select and dml privilege to

            user

      */

      (strToUser VARCHAR2,

       strPriv  VARCHAR2 )

AS

      strSql VARCHAR2(500);

      lngCount NUMBER(10);

      tab_name VARCHAR2(100);

      CURSOR cur_tab IS

            SELECT table_name FROM user_tables  ORDER BY 1;

BEGIN

      strSql:=' SELECT COUNT(*) FROM dba_users WHERE  username='''||strToUser||'''';

      EXECUTE IMMEDIATE strSql INTO lngCount ;

     

      IF lngCount>0 THEN

            --GRANT DELETE ON AM_DEPT TO LIS

            IF strPriv ='SEL' THEN

                  OPEN cur_tab ;

                  LOOP

                        FETCH cur_tab INTO tab_name;

                        EXIT WHEN cur_tab%NOTFOUND;

                        strSql:='GRANT SELECT ON '||tab_name|| ' TO '|| strToUser;

                        EXECUTE IMMEDIATE strsql;

                        --dbms_output.put_line(strSql);

                  END LOOP;

                  CLOSE cur_tab;   

            END IF;

 

            IF strPriv ='DML' THEN

                  OPEN cur_tab ;

                  LOOP

                        FETCH cur_tab INTO tab_name; 

                        EXIT WHEN cur_tab%NOTFOUND;

                        strSql:='GRANT ALL ON '||tab_name|| ' TO '|| strTouser;

                        EXECUTE IMMEDIATE strsql;

                        --dbms_output.put_line(strsql);

                  END LOOP;

                  CLOSE cur_tab;   

            END IF;

      END IF;

END grant_priv;

多年前寫的內容，請測試後使用。