Home » SQL & PL/SQL » SQL & PL/SQL » grant required to create the table through API
grant required to create the table through API [message #270968] Fri, 28 September 2007 10:34 Go to next message
kukreja
Messages: 33
Registered: February 2007
Member
Hi

I create a user test through sys dba.
Then provide grants
grant resource to test;
grant create session to test;


And create a procedure test1

create or replace procedure test1 
is
begin 
execute immediate 'create table check(a number)';
end;


While executing this procedure test1 i got this error
execute test1;
BEGIN test1; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "AD.TEST1", line 4
ORA-06512: at line 1


can anyone help me to sort out this problem.
Re: grant required to create the table through API [message #270970 is a reply to message #270968] Fri, 28 September 2007 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.

Regards
Michel
Re: grant required to create the table through API [message #270973 is a reply to message #270968] Fri, 28 September 2007 10:43 Go to previous messageGo to next message
kukreja
Messages: 33
Registered: February 2007
Member
Thanks for replying

But when I grant through sys
grant create table to test


then its working.

michel can u please explain me
Re: grant required to create the table through API [message #270975 is a reply to message #270973] Fri, 28 September 2007 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 28 September 2007 17:37
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.
Roles are not enabled in stored procedure.

Regards
Michel


Remove the "create table" privilege you just granted and then try "set role none;" and "create table".

Regards
Michel

[Updated on: Fri, 28 September 2007 10:47]

Report message to a moderator

Re: grant required to create the table through API [message #270978 is a reply to message #270968] Fri, 28 September 2007 10:53 Go to previous messageGo to next message
kukreja
Messages: 33
Registered: February 2007
Member
ok ok michel

any alternative solution to my problem.
Through resource role without direct grants
if i able to create table through stored procedure.

Also even if i grant
grant create table to test

I am not able to access this table check through stored procedure.
I got this error
ORA-00942: table or view does not exist


[mod-edit] removed illiterate IM speak.

[Updated on: Fri, 28 September 2007 12:37] by Moderator

Report message to a moderator

Re: grant required to create the table through API [message #270983 is a reply to message #270978] Fri, 28 September 2007 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post exactly what you did.
Who created the procedure?
Who owns the procedure?
Who try to access the table?
Nothing is clear.

Copy and paste what you do.

Regards
Michel
Re: grant required to create the table through API [message #271156 is a reply to message #270968] Sun, 30 September 2007 04:09 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What you did?
And create a procedure test1

create or replace procedure test1 
is
begin 
execute immediate 'create table check(a number)';
end;


And see after holding the resources you gave ,

SQL> begin
execute immediate 'create table check(a number)';
end;  2    3
  4  /
begin
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 2

begin
execute immediate 'create table checking(a number)';
end;
PL/SQL procedure successfully completed.

SQL> select tname from tab;

TNAME
------------------------------
CHECKING


Check is a reserved keyword.As system,

SQL>  select keyword,reserved from v$reserved_words where KEYWORD='CHECK';

KEYWORD                        R
------------------------------ -
CHECK                          Y






Previous Topic: intial extent creation problem in jdeveloper
Next Topic: sql to return a flag rather than records
Goto Forum:
  


Current Time: Sat Dec 10 16:45:11 CST 2016

Total time taken to generate the page: 0.12340 seconds