Procedure to give permissions to an Oracle 11g user?

Question

Procedure to give permissions to an Oracle 11g user?

Navigation

#1 by (3 votes)

3

I am trying to create a procedure that receives as a parameter the user to whom they wish to grant the tables permission directly, I know that it would be better with a role and simply grant it, but it is an exercise, to create a procedure that allows to give permission to X user on tables.

I made this code but I see an error in the GRANT CREATE and the word create (use ORACLE SQL Developer ) is underlined, if I delete it (this sentence), the following sentence is underlined what I put after the GRANT , in this case, when you delete all the GRANT create table TO usuario; , the update of the next sentence is highlighted.

The code is as follows

create or replace PROCEDURE Gran_toAnyTables (usuario varchar2) AS
BEGIN
  GRANT create table TO usuario;
  GRANT update any table TO usuario;
  GRANT insert any table TO usuario;
  GRANT select any table TO usuario;
  GRANT delete any table TO usuario;
  GRANT COMMENT ANY TABLE TO usuario;
  GRANT BACKUP ANY TABLE TO usuario;
END Gran_toAnyTables;

And the error that appears to me when compiling it is the following

"PLS-00103: Encountered the symbol" GRANT "when expecting one of the   following:

(begin case declare exit for goto if loop mod null pragma raise   return select update while with < < continue close current   delete fetch lock insert open rollback savepoint set sql execute   commit forall merge pipe purge "

sql oracle oracle-11g stored-procedure

asked by Fabian Montoya 04.04.2017 в 03:58

source

1 answer

Graph in C # with Datagridview data Problem when inserting new element in array

score 3 · Accepted Answer

In a procedure, you can only statically execute DML statements, such as SELECT , INSERT , UPDATE , DELETE .

In contrast, you can not execute statements DDL in the same way, such as GRANT , CREATE , ALTER , DROP , etc ... To be able to execute this type of sentence, you must do it using dynamic SQL with the help of EXECUTE IMMEDIATE .

In your case, the equivalent using EXECUTE IMMEDIATE would be the following:

create or replace PROCEDURE Gran_toAnyTables (usuario varchar2) AS
BEGIN
  EXECUTE IMMEDIATE 'GRANT create table TO ' || usuario;
  EXECUTE IMMEDIATE 'GRANT update any table TO ' || usuario;
  EXECUTE IMMEDIATE 'GRANT insert any table TO ' || usuario;
  EXECUTE IMMEDIATE 'GRANT select any table TO ' || usuario;
  EXECUTE IMMEDIATE 'GRANT delete any table TO ' || usuario;
  EXECUTE IMMEDIATE 'GRANT COMMENT ANY TABLE TO ' || usuario;
  EXECUTE IMMEDIATE 'GRANT BACKUP ANY TABLE TO ' || usuario;
END Gran_toAnyTables;

Just in case, I want to warn you, that although what I mention is correct, there are other subtleties with the use of procedures that could take you by surprise and cause access problems depending on the user you use to define and execute the procedure. I leave the following link in case you run into these problems after solving the problem described in your question: I have a PL / SQL procedure which will not work because of 'insufficient privileges', but it works in SQL * Plus it seems to work. Why? .