Solved

EXECUTE IMMEDIATE -  ORA-01031: insufficient privileges

Posted on 2004-09-27
8
24,881 Views
Last Modified: 2011-08-18
I'm GETTING ORA-01031 ON EXECUTE IMMEDIATe while executing proc.

I have a privelge to create table, which privileges need to be granted to my account to execute this command successfully?
0
Comment
Question by:smena
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 10

Expert Comment

by:SDutta
ID: 12163838
You have to give us a little more clue than that...
EXECUTE IMMEDIATE (what?)
Can you paste your script please.
0
 
LVL 12

Expert Comment

by:geotiger
ID: 12164654
0
 

Author Comment

by:smena
ID: 12166191
... CREATING TABLE

EXECUTE IMMEDIATE ('CREATE TABLE TEST (COL CHAR(5)')
0
 
LVL 12

Assisted Solution

by:catchmeifuwant
catchmeifuwant earned 20 total points
ID: 12166580
1)It's a bad habit to create objects within your procedure. You can always create it outside and use it in the procedure .

2)The reason why you are getting this error is because the user who's executing the procedure does not have "Create Table" privileges.You need to grant the privilege to the user.

As sys execute the following command:

grant create table to <user_name>;

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 20 total points
ID: 12168714
And rememeber if you've granted that privilidge through a role, it wouldn't work. you will have to directly grant that one (create table).
0
 
LVL 10

Assisted Solution

by:SDutta
SDutta earned 30 total points
ID: 12169232
Hi Smena,

Riazpk is correct, you cannot do EXECUTE IMMEDIATE ('CREATE TABLE TEST (COL CHAR(5)') within a procedure unless the owner of the procedure has a direct 'GRANT CREATE TABLE'. It does not work if the grant is available through a role like CONNECT etc.

Another comment repeating what Catchmeifuwant said, "It's a bad habit to create objects within your procedure (even temporary tables)". One reason is that you have write code to make sure the procedure cannot be run by two users at the same time otherwise you will get errors due to already existing objects.

Also you have to take care of the cleanup. You need to either drop the object at the beginning of the procedure, or at the end of the procedure otherwise your procedure will not be able to create the object if it already exists from a prior execution.
0
 
LVL 12

Accepted Solution

by:
geotiger earned 30 total points
ID: 12169799
This is the content in the link ( http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html) that I posted earlier:

Why do I get a "ORA-01031: insufficient privileges" or "PLS-00201: identifier 'x' must be declared" in my stored procedures?
Roles are never enabled during the execution of a procedure except in the special case of Invokers Rights which is a new feature in Oracle8i, release 8.1.
This fact is documented application developers guide:
 

Privileges Required to Create Procedures and Functions
To create a stand-alone procedure or function, or package specification or
body, you must meet the following prerequisites:

• You must have the CREATE PROCEDURE system privilege to create a
procedure or package in your schema, or the CREATE ANY
PROCEDURE system privilege to create a procedure or package in
another user’s schema.

Attention: To create without errors, that is, to compile the procedure
or package successfully, requires the following additional privileges:
The owner of the procedure or package must have been explicitly
granted the necessary object privileges for all objects referenced within
the body of the code; the owner cannot have obtained required
privileges through roles.

If the privileges of a procedure’s or package’s owner change, the procedure
must be reauthenticated before it is executed. If a necessary privilege to a
referenced object is revoked from the owner of the procedure (or package), the
procedure cannot be executed.

</quote>
Try this:
 

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure.  If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure (unless you are using Invokers rights in Oracle8i.  See the PLSQL documentation for more information on this feature and make sure you understand the ramifications).  To be able to perform that operation in a typical procedure, you need to have that privelege granted directly to you.
 
 
0
 

Author Comment

by:smena
ID: 12171909
Thanks for your responses, though I still need to use EXECUTE IMMEDIATE ('CREATE TABLE')
 
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle query help 29 77
Oracle RAC 12c 8 60
Oracle DATE Column Space 11 63
How can I rollback insert statements after commit in oracle? 7 63
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now