OraFAQ Forum: Server Administration » Privileges for DROP partition

Home » RDBMS Server » Server Administration » Privileges for DROP partition (Oracle 11g)

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

Privileges for DROP partition [message #512675]

Tue, 21 June 2011 07:55

shamsh_pervaiz
Messages: 22
Registered: September 2005
Location: bangalore

Junior Member

Hi All,

I have one user CD_APP.
I have one partition table CD.T_FCDR_DT.
User has got ALTER/INSERT/UPDATE/DELETE/SELECT privileges on the table..

Now when I try to drop a partition, I get error as below:
-------------------------------------
SQL> show user
USER is "CD_APP"
SQL> ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES;
ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-01031: insufficient privileges
--------------------------------------

Do I have to grant some other privileges for this user.

Please help.

Thanks in advance.
Shamsh Pervaiz

Report message to a moderator

Re: Privileges for DROP partition [message #512679 is a reply to message #512675]

Tue, 21 June 2011 08:06

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

When else fails, read the documentation: ALTER TABLE

Additional Prerequisites for Partitioning Operations

If you are not the owner of the table, then you need the DROP ANY TABLE privilege in order to use the drop_table_partition or truncate_table_partition clause.

Regards
Michel

Report message to a moderator

Re: Privileges for DROP partition [message #512681 is a reply to message #512679]

Tue, 21 June 2011 08:14

shamsh_pervaiz
Messages: 22
Registered: September 2005
Location: bangalore

Junior Member

Thanks for the inputs.
The example I placed here is from a QA env. Similarly I have PROD env where it works with the similar privileges (ALTER/INSERT/UPDATE/DELETE/SELECT privileges on the table).
That is my main concern why it works on PROD env and not on QA env.

Thanks
Shamsh Pervaiz

Report message to a moderator

Re: Privileges for DROP partition [message #512687 is a reply to message #512681]

Tue, 21 June 2011 08:52

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

Because you don't have the same privileges obviously

Report message to a moderator

Re: Privileges for DROP partition [message #512808 is a reply to message #512687]

Wed, 22 June 2011 06:45

shamsh_pervaiz
Messages: 22
Registered: September 2005
Location: bangalore

Junior Member

Got the final answer and solution for this by investigating/experimenting.
It differs based on Oracle version. On oracle version 11.2 it additional requires "DROP ANY TABLE" privilege. But on Oracle version 10.2, it does not require that, just ALTER table privilege is enough.

Regards
Shamsh Pervaiz

Report message to a moderator

Re: Privileges for DROP partition [message #512816 is a reply to message #512808]

Wed, 22 June 2011 07:01

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

The 10.2 documentation that Michel linked to above says different.
Are you sure your 10.2 user doesn't have the DROP ANY TABLE priv (through a role maybe)?

Report message to a moderator

Re: Privileges for DROP partition [message #512821 is a reply to message #512816]

Wed, 22 June 2011 07:30

shamsh_pervaiz
Messages: 22
Registered: September 2005
Location: bangalore

Junior Member

Yes I'm sure. I even tried creating two users (X and Y) and created a partition table under user schema X. Gave insert/update/select/delete/alter privileges to user Y on X.partition table. Then tried to drop the table partition using user Y on both 10.2 and 11.2. It worked fine in 10.2 but not in 11.2. Hence it should be a BUG or not sure how it is.

Regards
Shamsh Pervaiz

Report message to a moderator

Re: Privileges for DROP partition [message #512825 is a reply to message #512821]

Wed, 22 June 2011 07:37

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

Probably a bug in your version of 10.2 then. What exact version is it?

Report message to a moderator

Re: Privileges for DROP partition [message #512834 is a reply to message #512825]

Wed, 22 June 2011 07:59

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> create table t (id integer, val integer)
  2  partition by range (id)
  3  (partition p1 values less than (10),
  4  partition p2 values less than (20),
  5  partition p3 values less than (30)
  6  )
  7  /

Table created.

SQL> grant alter on t to test;

Grant succeeded.

SQL> connect test/test
Connected.
TEST> alter table michel.t drop partition p2;

Table altered.

TEST> @v

Version Oracle : 10.2.0.4.0

SQL> create table t (id integer, val integer)
  2  partition by range (id)
  3  (partition p1 values less than (10),
  4  partition p2 values less than (20),
  5  partition p3 values less than (30)
  6  )
  7  /

Table created.

SQL> grant alter on t to test;

Grant succeeded.

SQL> connect test/test
Connected.
TEST> alter table michel.t drop partition p2;

Table altered.

TEST> @v

Version Oracle : 11.2.0.1.0

I got the same (unexpected) behaviour in 10.2 and 11.2.
Which version have you?
Can you post the exact test case you made as I did.

Regards
Michel

Report message to a moderator

Re: Privileges for DROP partition [message #512837 is a reply to message #512834]

Wed, 22 June 2011 08:09

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

Hmmmm. Maybe the bug is really in the documentation.

Report message to a moderator

Re: Privileges for DROP partition [message #512840 is a reply to message #512837]

Wed, 22 June 2011 08:20

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No, it is a product bug:
Bug 9168451: DROP ANY TABLE PRIVILEGE IS NOT NECESSARY FOR DROPPING PARTITIONED TABLE
Not fixed, in Q/A.

Regards
Michel

Report message to a moderator

Re: Privileges for DROP partition [message #512843 is a reply to message #512834]

Wed, 22 June 2011 08:24

shamsh_pervaiz
Messages: 22
Registered: September 2005
Location: bangalore

Junior Member

I have oracle version as "Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production".
Please check the output here:

SQL> CREATE TABLE MACHSPE.TST_FL_T
2 (
3 FL_ID NUMBER(38),
4 PRD VARCHAR2(8 BYTE),
5 TEC_ID NUMBER(38),
6 R_ID NUMBER(38)
7 )
8 TABLESPACE USERS
9 PARTITION BY LIST (PRD)
10 (
11 PARTITION MFS_000000 VALUES ('000000')
12 TABLESPACE USERS,
13 PARTITION MFS_200707 VALUES ('200707')
14 TABLESPACE USERS,
15 PARTITION MFS_200708 VALUES ('200708')
16 TABLESPACE USERS
17 );

Table created.

SQL> show user
USER is "MACHSPE"
SQL> GRANT ALTER, DELETE, INSERT, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE ON MACHSPE.TST_FL_T TO MACHABH;

Grant succeeded.

SQL> select * from v$version where banner like 'Oracle%';
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

1 row selected.

SQL> connect machabh
Enter password:
Connected.
SQL> alter table MACHSPE.TST_FL_T drop partition MFS_200707;
alter table MACHSPE.TST_FL_T drop partition MFS_200707
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>

Hope this is explaining everything.

Regards
Shamsh Pervaiz

Report message to a moderator

Re: Privileges for DROP partition [message #512848 is a reply to message #512843]

Wed, 22 June 2011 08:43

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thanks, so it seems to be fixed in 10.2.0.2.

Before your next post, Please read How to use [code] tags and make your code easier to read.

Regards
Michel

Report message to a moderator

Previous Topic:	Database Server Performance
Next Topic:	Changing web IP address(2 Merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 02 06:40:32 CDT 2024