Grant privilege to create table INSIDE oracle sql procedure

Question

I am trying to create a table inside a procedure on oracle. But I keep getting ORA-01031: insufficient privileges. I thought that maybe my user needs a privilege for doing that?

Here is the procedure code:

CREATE OR REPLACE PROCEDURE AjouterCol(TAB VARCHAR2, NOM VARCHAR2) IS 
REQ VARCHAR2(200);
RES VARCHAR2(200);
RES1 VARCHAR2(200);
NAME VARCHAR2(200);
BEGIN
  NAME:=TAB || '_' || NOM;

  RES:='CREATE TABLE '||NAME||' AS SELECT * FROM '|| TAB || ' WHERE Col9 = ''PARIS'' AND SOUNDEX(Col9) = SOUNDEX(''PARIS'')' ;
  EXECUTE IMMEDIATE RES;

  RES1:='ALTER TABLE '|| NAME ||' ADD MAGASIN VARCHAR2(5)';
  EXECUTE IMMEDIATE RES1;

  REQ:='UPDATE '|| NAME ||' SET MAGASIN = '''|| NOM ||''' WHERE MAGASIN IS NULL' ;
  EXECUTE IMMEDIATE REQ;

  END;
  /

Any help please ? Thank you.

Ummmm, are you the database admin? If so, run this as a user that has the necessary privileges? If not, ask the DBA for a user with the necessary privileges? You already have your answer ; you need a user with the necessary privileges. What exactly is your question then? — MatBailie, Dec 03 '18 at 17:25
My question is: What are the necessary privileges (if they exist) that a user needs to be able to execute a procedure that creates a table. Before, I wasn't able to create views from my user so i logged in as system and granted that to my user using: GRANT CREATE VIEW TO MyUser; Is there something similar to do in this case? — , Dec 03 '18 at 17:32
`GRANT CREATE,ALTER,UPDATE ON TABLE/VIEW TO USER ` the only solution in case of insufficient privillages — Himanshu, Dec 03 '18 at 17:35
Possible duplicate of [Can GRANT be used inside an Oracle Store Procedure?](https://stackoverflow.com/questions/5394140/can-grant-be-used-inside-an-oracle-store-procedure) — Himanshu, Dec 03 '18 at 17:51
@HimanshuAhuja OH no, I'm just asking why is my procedure keeps returning the error and wondering whether a grant can solve my problem — , Dec 03 '18 at 17:55
Yes grant will solve the problem the issue with the error is insufficient privillages that means you dont have create/alter/etc access to that table so you should first use grant as a seperate statement then try to run this procedure it would work. I referred that link to clarify grant should not be used inside the procedures though if you are using grant statement — Himanshu, Dec 03 '18 at 17:59

score 1 · Answer 1 · answered Dec 03 '18 at 17:31

1

If user (which executes that script) acquired CREATE TABLE privilege via role, it can create tables. However, those privileges won't work in named PL/SQL procedures - you'll have to grant that privilege directly to the user (i.e. not via role).

answered Dec 03 '18 at 17:31

Littlefoot

131,892
15
35
57

What privilege would it be ? a standard GRANT CREATE,ALTER,UPDATE ON TABLE/VIEW TO USER ? – Dec 03 '18 at 17:38
@Littlefoot exactly Pl/sql doesnt allow grant statements inside the procedures and functions as these are meant to manipulate/store/return the data which is a dml operation(insert,update,delete..) on the other side `grant` is related to providing privillages not manipulation instead access permission so will not work inside procedures,functions rather you should use grant directly as a seperate statement. – Himanshu Dec 03 '18 at 17:47
Start with CREATE TABLE (as you're creating it). Move on to subsequent privileges you used in the procedure, if necessary. Though, why do you use dynamic SQL? Why wouldn't you create table *normally*? Aren't you, perhaps, overcomplicating things? The fact that you *can* do it doesn't mean that you *should* do it. – Littlefoot Dec 03 '18 at 17:50

score 0 · Accepted Answer · answered Dec 03 '18 at 18:06

0

So thank you everyone for your answers. I resolved the problem by simply adding AUTHID CURRENT_USER to the proc declaration. Thank you again.

answered Dec 03 '18 at 18:06

Grant privilege to create table INSIDE oracle sql procedure

2 Answers2