Geek asks and answers

Can GRANT be used inside an Oracle storage routine?

When trying to place a GRANT statement in an Oracle 11 stored procedure, it reports that GRANT is an unexpected character. Should GRANT be preceded by something, or does Oracle simply prevent GRANTS from running inside the SP?

+3
source share
2 answers

It is a bad idea to use DDL(for example GRANT) inside stored procedures.

You will need to use dynamic SQL( EXECUTE IMMEDIATE), but to be honest, I don’t understand why you want to do this inside the stored process.

+3
source

PL/SQL, (SELECT, UPDATE ..) , , . - "appuser" .

CREATE OR REPLACE PROCEDURE grant_privs
IS
   CURSOR ut_cur IS SELECT table_name from user_tables;
   ut_rec ut_cur%rowtype;
BEGIN
   FOR ut_rec IN ut_cur
   LOOP
      EXECUTE IMMEDIATE 'GRANT ALL ON ' || ut_rec.table_name || ' TO appuser';
   END LOOP;
END;
/

-. . , "appuser" SQL- . -, "appuser" , , .

, .

+1

More articles:


All Articles