HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

insufficient privileges while executing oracle stored procedure?

Submitted by: @import:stackexchange-dba··
0
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.

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
  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

Context

StackExchange Database Administrators Q#49732, answer score: 2

Revisions (0)

No revisions yet.