Insufficient privs when trying to execute an Oracle stored proc
I have created the stored procedure below. If I try to execute it, I am getting an insufficient priviledges error message.
Does anyone have any idea why?
CREATE OR REPLACE PROCEDURE SIMPLE
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ELIX (ID INT NULL);
EXECUTE IMMEDIATE 'GRANT INSERT ON B1';
EXECUTE IMMEDIATE 'INSERT INTO ELIX (ID) VALUES (1)';
END;
EXECUTE SIMPLE
Error starting at line : 3 in command -
EXECUTE SIMPLE
Error report -
ORA-01031: "insufficen
*Cause: An attempt was made to perform a database operation without the necessary privileges
Does anyone have any idea why?
CREATE OR REPLACE PROCEDURE SIMPLE
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ELIX (ID INT NULL);
EXECUTE IMMEDIATE 'GRANT INSERT ON B1';
EXECUTE IMMEDIATE 'INSERT INTO ELIX (ID) VALUES (1)';
END;
EXECUTE SIMPLE
Error starting at line : 3 in command -
EXECUTE SIMPLE
Error report -
ORA-01031: "insufficen
*Cause: An attempt was made to perform a database operation without the necessary privileges
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
For stored procedures the permissions need to be granted to the user that owns the procedure directly and not through a role.
Also: You really shouldn't be creating tables and performing grants inside a stored procedure like that. Oracle normally doesn't need to do that.