Skip to Main Content
  • Questions
  • Privilege required for non DBA user to create the external table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ebin.

Asked: July 19, 2017 - 6:12 am UTC

Last updated: August 09, 2017 - 12:43 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi ,
What are the privilege required for a non DBA user to create the external table?

and Chris said...

You need:

- create table
- Either:
- read/write permissions on an existing directory
- permissions to create a new directory

Assuming you have a file called test.txt in /tmp and an existing directory object pointing to this:

grant create session, create table, create any directory 
  to testu identified by testu;
grant read, write on directory tmp to testu;

conn testu/testu

create table t (
  x varchar2(10) 
) organization external (
  default directory tmp
  location ('test.txt')
);

select * from t;

X         
----------
test

drop table t purge;
create or replace directory testdir as '/tmp';
create table t (
  x varchar2(10) 
) organization external (
  default directory testdir
  location ('test.txt')
);

select * from t;

X         
----------
test

Rating

  (2 ratings)

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

Comments

Ebin Jozer, July 20, 2017 - 4:04 am UTC


Ebin Jozer, August 09, 2017 - 12:32 pm UTC

Hi chris,
thanks for the elaborated information.
as per security i dont want to provide "create any directory" privilege, will the external table still be created?
the external table has been created via stored procedure, if i remove "create any table" it is not allowing to create the external table, showing "insufficient privilege".
Please advise.
Chris Saxon
August 09, 2017 - 12:43 pm UTC

You need to have access to a directory object pointing to the file location to make an external table. So either you need someone else to create it for you and have read & write privs on it or create it yourself (which requires "create any directory").

You need "create table" or "create any table" to make tables. You should stick with plain "create table" the vast majority of the time.

But... why are you building the table via a stored procedure?!

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