Skip to Main Content
  • Questions
  • using sql loader for a table where you only have insert privileges via a non default role

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Joseph.

Asked: February 13, 2019 - 8:27 pm UTC

Last updated: February 15, 2019 - 11:29 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

TeamTOM,

I'm trying to use Sql Loader to load a text file into a normal Oracle table (as opposed to an external one). Unfortunately I only have insert rights to the table via a non default role. If I were just doing a simple insert statement in sql*plus/sql developer/toad/whatever, I could do a "set role all" command prior to running the insert command but I can't find in the documentation how I can do that for sql loader.

I've included the below code to hopefully present a valid test case. (Sorry, Livesql does not appear to allow creation of users and roles).

The key is "ALTER USER some_other_user DEFAULT ROLE CONNECT;" does not include "some_role".

CREATE TABLE some_user.SOME_TABLE(
   Col1 number(10) not null,
   col2 varchar2(100) not null,
   col3 varchar2(100) not null
);

CREATE ROLE SOME_ROLE NOT IDENTIFIED;

GRANT SELECT,  INSERT,  UPDATE, DELETE  ON some_user.SOME_TABLE TO SOME_ROLE;

CREATE USER some_other_user IDENTIFIED BY some_password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

ALTER USER some_other_user QUOTA UNLIMITED ON USERS;

GRANT CONNECT TO some_other_user;
GRANT SOME_ROLE TO some_other_user;

ALTER USER some_other_user DEFAULT ROLE CONNECT ;
--notice in the above the SOME_ROLE role is not listed

GRANT CREATE SESSION TO some_other_user;



Assume my sql loader control file is really simple like below.

LOAD DATA
  INFILE 'some_file.dat'
  BADFILE 'some_file.bad'
  DISCARDFILE 'some_file.dsc'
  INSERT
  INTO TABLE some_user.SOME_TABLE
  FIELDS TERMINATED BY '|'
  TRAILING NULLCOLS
 (
      col1 SEQUENCE(1,1),
      col2,
      col3
)



Assume some_file.dat is the following:

some text|some other text


and Chris said...

Sorry, I'm not aware of an option to enable non-default roles in the SQL*Loader command-line/parfile.

So your options are:

- Create an on logon trigger which enables the role you need
- Make the role you need a default role
- Create another user which does have this (and only this) role

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library