OraFAQ Forum: Server Administration » what pririvlege is requried to create lob table ?

Home » RDBMS Server » Server Administration » what pririvlege is requried to create lob table ? (oracle 11gr2 linux 6.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

what pririvlege is requried to create lob table ? [message #612450]

Fri, 18 April 2014 16:11

kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI

Senior Member

Hi team,
i am trying to create below under one user. this user has only CREATE TABLE permission. what priv is additionally required ?

SQL> CREATE TABLE t1 (a CLOB)
  2      LOB(a) STORE AS SECUREFILE(
  3      COMPRESS LOW
  4      CACHE
  5      NOLOGGING
  6    );
CREATE TABLE t1 (a CLOB)
*
ERROR at line 1:
ORA-01031: insufficient privileges

thank you

Report message to a moderator

Re: what pririvlege is requried to create lob table ? [message #612451 is a reply to message #612450]

Fri, 18 April 2014 16:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>this user has only CREATE TABLE permission
a USER with ONLY CREATE TABLE privilege can NOT login to issue any SQL.

Report message to a moderator

Re: what pririvlege is requried to create lob table ? [message #612452 is a reply to message #612451]

Fri, 18 April 2014 16:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

it works ok for me.

SQL> @ct
SQL> connect / as sysdba
Connected.
SQL> create user ct identified by ct;

User created.

SQL> grant create session to ct;

Grant succeeded.

SQL> grant create table to ct;

Grant succeeded.

SQL> connect ct/ct
Connected.
SQL> CREATE TABLE t1 (a CLOB)
  2          LOB(a) STORE AS SECUREFILE(
  3          COMPRESS LOW
  4          CACHE
  5          NOLOGGING
  6        );

Table created.

SQL>

Report message to a moderator

Re: what pririvlege is requried to create lob table ? [message #612474 is a reply to message #612452]

Sat, 19 April 2014 08:45

kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI

Senior Member

Hi Team,

Yes, I had given "create session" & "create table" privilege to that local user. I am able to create now. I did not do any change in permission now, but I am able to create it now. Thank you very for your time and assistance. ( My apologies for confusion )

SQL> create user u003 identified by "User123#";

User created.

SQL> grant connect,create table to u003;

Grant succeeded.


SQL> show user
USER is "U003"
SQL> CREATE TABLE t11 (a CLOB)
  2           LOB(a) STORE AS SECUREFILE(
  3            COMPRESS LOW
  4            CACHE
  5             NOLOGGING
  6           );

Table created.

SQL> exit

Thanks again

Report message to a moderator

Re: what pririvlege is requried to create lob table ? [message #612475 is a reply to message #612474]

Sat, 19 April 2014 08:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Yes, I had given "create session" & "create table" privilege to that local user
What you said (above) & what you did (below) are NOT the same!
>grant connect,create table to u003;
why should we believe anything you post in the future?

Report message to a moderator

Re: what pririvlege is requried to create lob table ? [message #612480 is a reply to message #612475]

Sat, 19 April 2014 11:40

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Since 10gR2, CONNECT role has only the CREATE SESSION privilege so I suppose (but may be wrong and would like to hear if so) there's no much difference in this case whether he granted the privilege or the role containing only that privilege as he's on 11gR2 anyway.

Report message to a moderator