OraFAQ Forum: Server Administration » insufficient privileges

Home » RDBMS Server » Server Administration » insufficient privileges (Oralce 11.1.0.7 Solaris 2.10)

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

insufficient privileges [message #554248]

Fri, 11 May 2012 11:23

BeefStu
Messages: 208
Registered: October 2011

Senior Member

Can somebody tell me whats missing from a privs stand point for the user N500661. As I want to have this user create objects in the schema DATA_INTEGRATION.

As you can see I assigned the role "DEVELOPER" to this user, which I would think should have all the permissions but obvisouly I am missing something.

Also, this user should have an unlimited quota to create files in the table space.

Thanks in advance to all who answer


 USER N500661
  GRANT FMDW_ADMIN TO N500661;
  GRANT DEVELOPER TO N500661;
  GRANT DI_APP_ASD TO N500661;
  GRANT FMDW_APP_ASD TO N500661;
  ALTER USER N500661 DEFAULT ROLE FMDW_ADMIN, DI_APP_ASD;
  -- 1 System Privilege for N500661 
  GRANT CREATE SESSION TO N500661;
  -- 2 Tablespace Quotas for N500661 
  ALTER USER N500661 QUOTA UNLIMITED ON DATA_INTEGRATION_IDX;
  ALTER USER N500661 QUOTA UNLIMITED ON DATA_INTEGRATION_DAT;



GRANT DROP ANY VIEW TO DEVELOPER;
GRANT DROP ANY INDEX TO DEVELOPER;
GRANT DROP ANY TABLE TO DEVELOPER;
GRANT ALTER ANY TABLE TO DEVELOPER;
GRANT CREATE ANY VIEW TO DEVELOPER;
GRANT CREATE ANY INDEX TO DEVELOPER;
GRANT CREATE ANY TABLE TO DEVELOPER;
GRANT DELETE ANY TABLE TO DEVELOPER;
GRANT DROP ANY TRIGGER TO DEVELOPER;
GRANT INSERT ANY TABLE TO DEVELOPER;
GRANT SELECT ANY TABLE TO DEVELOPER;
GRANT UPDATE ANY TABLE TO DEVELOPER;
GRANT DROP ANY SEQUENCE TO DEVELOPER;
GRANT ALTER ANY SEQUENCE TO DEVELOPER;
GRANT CREATE ANY SYNONYM TO DEVELOPER;
GRANT CREATE ANY TRIGGER TO DEVELOPER;
GRANT DROP ANY PROCEDURE TO DEVELOPER;
GRANT ALTER ANY PROCEDURE TO DEVELOPER;
GRANT CREATE ANY SEQUENCE TO DEVELOPER;
GRANT SELECT ANY SEQUENCE TO DEVELOPER;
GRANT CREATE ANY PROCEDURE TO DEVELOPER;
GRANT GRANT ANY OBJECT PRIVILEGE TO DEVELOPER;
GRANT SELECT_CATALOG_ROLE TO DEVELOPER;
GRANT DEVELOPER TO N500661;


CREATE TABLE DATA_INTEGRATION.XXX 
(
   X   VARCHAR2 (20) NOT NULL
)TABLESPACE DATA_INTEGRATION_DAT

Error at Command Line:1 Column:0
Error report:
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"

Report message to a moderator

Re: insufficient privileges [message #554250 is a reply to message #554248]

Fri, 11 May 2012 11:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

did N500661 start new session after all GRANT & such were issued?
was USER=N500661 for CREATE TABLE DATA_INTEGRATION.XXX statement?

Report message to a moderator

insufficient privileges [message #554253 is a reply to message #554248]

Fri, 11 May 2012 11:47

BeefStu
Messages: 208
Registered: October 2011

Senior Member

yes and yes.... Can a user modify another users schema without DBA privs?
Is that the issue?

Report message to a moderator

Re: insufficient privileges [message #554254 is a reply to message #554253]

Fri, 11 May 2012 11:50

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Can a user modify another users schema without DBA privs?

It depends on what you mean by DBA privs. all ANY privileges are DBA privileges.

Does DATA_INTEGRATION has any quota on its default tablespace?
Who executed what?

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel

Report message to a moderator

Re: insufficient privileges [message #554257 is a reply to message #554254]

Fri, 11 May 2012 12:06

BeefStu
Messages: 208
Registered: October 2011

Senior Member

BY DBA I was taking about granting dba to user.....

As not to distrub the login N500661 I copied it to a new login called XXX.
Everything else is setup the same. But I will provide it any way.

Note the role "DEVELOPER" is granted to user XXX see botom of role.
Sorry about all the redunancy.

If you dont trust my toad output let me know if there are any specific queries you
want me to post the output for...


Here is the info for DATA_INTEGRATION as you can see there is not quota on the tablespace

USER DATA_INTEGRATION
  DEFAULT TABLESPACE DATA_INTEGRATION_DAT
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 8 System Privileges for DATA_INTEGRATION 
  GRANT CREATE SESSION TO DATA_INTEGRATION;
  GRANT CREATE PROCEDURE TO DATA_INTEGRATION;
  GRANT CREATE TRIGGER TO DATA_INTEGRATION;
  GRANT CREATE TABLE TO DATA_INTEGRATION;
  GRANT CREATE MATERIALIZED VIEW TO DATA_INTEGRATION;
  GRANT CREATE SEQUENCE TO DATA_INTEGRATION;
  GRANT CREATE ANY TYPE TO DATA_INTEGRATION;
  GRANT CREATE VIEW TO DATA_INTEGRATION;
  -- 2 Tablespace Quotas for DATA_INTEGRATION 
  ALTER USER DATA_INTEGRATION QUOTA UNLIMITED ON DATA_INTEGRATION_DAT;
  ALTER USER DATA_INTEGRATION QUOTA UNLIMITED ON DATA_INTEGRATION_IDX;





  USER XXX
  DEFAULT TABLESPACE DATA_INTEGRATION_DAT
  TEMPORARY TABLESPACE TEMP
  
  -- 4 Roles for XXX 
  GRANT FMDW_APP_ASD TO XXX;
  GRANT FMDW_ADMIN TO XXX;
  GRANT DEVELOPER TO XXX;
  GRANT DI_APP_ASD TO XXX;
  ALTER USER XXX DEFAULT ROLE FMDW_ADMIN, DI_APP_ASD;
  -- 1 System Privilege for XXX 
  GRANT CREATE SESSION TO XXX;
  -- 2 Tablespace Quotas for XXX 
  ALTER USER XXX QUOTA UNLIMITED ON DATA_INTEGRATION_DAT;
  ALTER USER XXX QUOTA UNLIMITED ON DATA_INTEGRATION_IDX;


GRANT DROP ANY VIEW TO DEVELOPER;
GRANT DROP ANY INDEX TO DEVELOPER;
GRANT DROP ANY TABLE TO DEVELOPER;
GRANT ALTER ANY TABLE TO DEVELOPER;
GRANT CREATE ANY VIEW TO DEVELOPER;
GRANT CREATE ANY INDEX TO DEVELOPER;
GRANT CREATE ANY TABLE TO DEVELOPER;
GRANT DELETE ANY TABLE TO DEVELOPER;
GRANT DROP ANY TRIGGER TO DEVELOPER;
GRANT INSERT ANY TABLE TO DEVELOPER;
GRANT SELECT ANY TABLE TO DEVELOPER;
GRANT UPDATE ANY TABLE TO DEVELOPER;
GRANT DROP ANY SEQUENCE TO DEVELOPER;
GRANT ALTER ANY SEQUENCE TO DEVELOPER;
GRANT CREATE ANY SYNONYM TO DEVELOPER;
GRANT CREATE ANY TRIGGER TO DEVELOPER;
GRANT DROP ANY PROCEDURE TO DEVELOPER;
GRANT ALTER ANY PROCEDURE TO DEVELOPER;
GRANT CREATE ANY SEQUENCE TO DEVELOPER;
GRANT SELECT ANY SEQUENCE TO DEVELOPER;
GRANT CREATE ANY PROCEDURE TO DEVELOPER;
GRANT GRANT ANY OBJECT PRIVILEGE TO DEVELOPER;
GRANT SELECT_CATALOG_ROLE TO DEVELOPER;
GRANT DEVELOPER TO N500661;
GRANT DEVELOPER TO XXX;



SQL> show user
USER is "XXX"
SQL> CREATE TABLE DATA_INTEGRATION.XXX ( X   VARCHAR2 (20) NOT NULL) tablespace DATA_INTEGRATION_DAT;
CREATE TABLE DATA_INTEGRATION.XXX ( X   VARCHAR2 (20) NOT NULL) tablespace DATA_INTEGRATION_DAT
*
ERROR at line 1:
ORA-01031: insufficient privileges

Report message to a moderator

Re: insufficient privileges [message #554259 is a reply to message #554257]

Fri, 11 May 2012 12:13

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Fri, 11 May 2012 18:50

Quote:
Can a user modify another users schema without DBA privs?

It depends on what you mean by DBA privs. all ANY privileges are DBA privileges.

Does DATA_INTEGRATION has any quota on its default tablespace?
Who executed what?

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel

No I don't trust TOAD, TOAD is just s...

[Updated on: Fri, 11 May 2012 12:14]

Report message to a moderator

Re: insufficient privileges [message #554267 is a reply to message #554248]

Fri, 11 May 2012 12:48

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

BeefStu wrote on Fri, 11 May 2012 12:23

Also, this user should have an unlimited quota to create files in the table space.

You need a system privilege of ALTER TABLESPACE to add datafiles to a tablespace.

[fixed typo]

[Updated on: Fri, 11 May 2012 12:49]

Report message to a moderator

Re: insufficient privileges [message #554270 is a reply to message #554259]

Fri, 11 May 2012 12:52

BeefStu
Messages: 208
Registered: October 2011

Senior Member

Got it!!! Thanks for your time and help

As you can see the user was setup like this:

ALTER USER XXX DEFAULT ROLE FMDW_ADMIN, DI_APP_ASD;

Note the role "DEVELOPER" was not specified in this list.

To fix the issue I ran this statement

ALTER USER N500661 DEFAULT ROLE ALL;

This enable the roles that have been granted to that user. Without doing that the user must do SET ROLE "rolename" or SET ROLE ALL to enable granted roles in that session.

When they are setup as DEFAULT roles they are enabled when the session gets created.

[Updated on: Fri, 11 May 2012 13:08] by Moderator

Report message to a moderator

Re: insufficient privileges [message #554272 is a reply to message #554270]

Fri, 11 May 2012 13:09

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Something we could immediatly gave you if you posted what is requested.

Regards
Michel

Report message to a moderator

Re: insufficient privileges [message #554277 is a reply to message #554272]

Fri, 11 May 2012 14:01

BeefStu
Messages: 208
Registered: October 2011

Senior Member

If you look that was in my first post.. Sorry to have wasted your time

Report message to a moderator

Re: insufficient privileges [message #554278 is a reply to message #554277]

Fri, 11 May 2012 14:11

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Your post does not show any execution so how can we know what you actually did? And which user(s) did it?
A SQL*Plus session shows us what is real and we can answer in less than a minute for such things.

Regards
Michel

Report message to a moderator

Previous Topic:	Change Parameter value in Oracle 11gR2
Next Topic:	oracle database login error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 02:28:28 CST 2024