patternMinor
insufficient privileges while executing oracle stored procedure?
Viewed 0 times
storedexecutingwhileprivilegesinsufficientprocedureoracle
Problem
I'm getting an insufficient privileges error while executing the following oracle stored procedure. I'm using Oracle Database 10g Express Edition.
I'm using the post Update oracle sql database from CSV to build this SP. I could compile this stored procedure successfully. I have all the rights for the oracle user because I'm the admin. I have given all possible rights.
But when I execute the SP I'm getting error like:
```
Error starting at line 13 in command:
execute sp_update_acounts('C:\Users\surenr\Desktop\UA\Intitial-Conversion','acc_data.csv')
Error report:
ORA-01031: insufficient privileges
ORA-06512: at "myuser.SP_UPDATE_ACOUNTS", line 7
ORA-06512: at line 1
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the
CREATE OR REPLACE
PROCEDURE sp_update_acounts(
accounts_file_dir IN VARCHAR2,
accounts_file_name IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ext_accounts_dir AS '''||accounts_file_dir||'''';
EXECUTE IMMEDIATE 'grant read, write on directory ext_accounts_dir to myuser';
EXECUTE IMMEDIATE 'drop table crm_account_stage';
EXECUTE IMMEDIATE 'CREATE TABLE crm_account_stage (entity_account_id NUMBER(19,0), crm_id VARCHAR2(255 CHAR)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY
ext_accounts_dir ACCESS PARAMETERS (FIELDS TERMINATED BY '','' ( entity_account_id CHAR(225), crm_id CHAR(225))) LOCATION ('''||accounts_file_name||''''||') )';
MERGE INTO ua_crm_accounts acc
USING (
SELECT entity_account_id,
crm_id
FROM crm_account_stage) acc_stage
ON (acc_stage.entity_account_id = acc.pkey)
WHEN MATCHED THEN
UPDATE SET acc.crm_id = acc_stage.crm_id;
END;I'm using the post Update oracle sql database from CSV to build this SP. I could compile this stored procedure successfully. I have all the rights for the oracle user because I'm the admin. I have given all possible rights.
But when I execute the SP I'm getting error like:
```
Error starting at line 13 in command:
execute sp_update_acounts('C:\Users\surenr\Desktop\UA\Intitial-Conversion','acc_data.csv')
Error report:
ORA-01031: insufficient privileges
ORA-06512: at "myuser.SP_UPDATE_ACOUNTS", line 7
ORA-06512: at line 1
- 00000 - "insufficient privileges"
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the
Solution
The pl/sql execution context does not include Role. So your risght have been delegated by a role. The Pl/SQL doesn't see it.
So, first, you can try to give the right to alter the user directly not via a role.
2nd, i would try this option on the package definition (auhtid) which is explained here
So, first, you can try to give the right to alter the user directly not via a role.
2nd, i would try this option on the package definition (auhtid) which is explained here
Context
StackExchange Database Administrators Q#49732, answer score: 2
Revisions (0)
No revisions yet.