Home » RDBMS Server » Server Administration » what pririvlege is requried to create lob table ? (oracle 11gr2 linux 6.4)
what pririvlege is requried to create lob table ? [message #612450] Fri, 18 April 2014 16:11 Go to next message
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
Re: what pririvlege is requried to create lob table ? [message #612451 is a reply to message #612450] Fri, 18 April 2014 16:21 Go to previous messageGo to next message
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.
Re: what pririvlege is requried to create lob table ? [message #612452 is a reply to message #612451] Fri, 18 April 2014 16:29 Go to previous messageGo to next message
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> 
Re: what pririvlege is requried to create lob table ? [message #612474 is a reply to message #612452] Sat, 19 April 2014 08:45 Go to previous messageGo to next message
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
Re: what pririvlege is requried to create lob table ? [message #612475 is a reply to message #612474] Sat, 19 April 2014 08:54 Go to previous messageGo to next message
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?
Re: what pririvlege is requried to create lob table ? [message #612480 is a reply to message #612475] Sat, 19 April 2014 11:40 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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.
Re: what pririvlege is requried to create lob table ? [message #612481 is a reply to message #612480] Sat, 19 April 2014 11:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
privileges acquired via ROLE do NOT apply within named PL/SQL procedures.
Re: what pririvlege is requried to create lob table ? [message #612482 is a reply to message #612481] Sat, 19 April 2014 12:04 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right; which is not the case here, is it?
Previous Topic: Extension of datafile in ASM
Next Topic: Redo geneated high in temp table
Goto Forum:
  


Current Time: Fri Mar 29 07:56:04 CDT 2024