Pages

Monday, January 19, 2015

Setting CURRENT_SCHEMA caused error 'ORA-01031: insufficient privileges'

Question: Why did I get 'ORA-01031: insufficient privileges' when running 'create table' command to create table ? I do have 'CREATE TABLE' privilege granted.

Answer: the CURRENT_SCHEMA of your session is not expected account of yours.

The database user devuser is granted 'CREATE TABLE' privilege:
evuser@ORADB> select * from user_sys_privs;

USERNAME PRIVILEGE ADM
-------------------- ---------------------------------------- ---
DEVUSER CREATE TABLE NO
DEVUSER CREATE SESSION NO

But he got ORA-01031 when running 'create table'
devuser@ORADB> create table tb01(sno number);
create table tb01(sno number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

And did not get error when creating table with schema specified in the statement:
devuser@ORADB> create table devuser.tb01(sno number);

Table created.

Looks like the 'create table' without schema specified is trying to create table in another user's schema. Find out who the another user is:
devuser@ORADB> select sys_context('userenv','current_schema') as current_schema from dual;

CURRENT_SCHEMA
------------------------------
DEVOWNER

It is not expected user name 'devuser'. Try to change back:
devuser@ORADB> alter session set current_schema=devuser;

Session altered.

devuser@ORADB> select sys_context('userenv','current_schema') as current_schema from dual;

CURRENT_SCHEMA
------------------------------
DEVUSER

Test if 'create table' works:
devuser@ORADB> create table tb02(sno number);

Table created.

See, current_schema accounts for it!

 

 

 

 

 

No comments: