Home » RDBMS Server » Server Administration » Grant privilege (10.2.0.3 Windows 2003)
Grant privilege [message #400946] Thu, 30 April 2009 09:26 Go to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi all,

Our application runs from a default schema 'aris' and we have synonyms created for the tables of all other schemas('asg','cmi'...) in the database so the 'aris' user can read all the tables in the database.

Logged in as 'aris' user, we should be able to truncate any table in 'cmi' schema. I think Oracle does not have 'truncate' table privilege. CMI user already has 'delete any table' privilege.

Aris user has DBA privilege; alsc_bl is a table in 'cmi' schema. alsc_bl public synonym has been created. We are able to select records in alsc_bl table when logged in as 'aris' user. But we are not able to truncate it.

I run this:

aris@test> truncate table alsc_bl;
*
ERROR at line 1:
ORA-00942: table or view does not exist

Please give me your suggestions. Thanks

Report message to a moderator

Re: Grant privilege [message #400949 is a reply to message #400946] Thu, 30 April 2009 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Aris user has DBA privilege;
I suspect the statement above is not correct & even if it were correct it would do nothing for TRUNCATE table of other schema.

DBA in Oracle is NOT like "root" for Unix.

Report message to a moderator

Re: Grant privilege [message #400950 is a reply to message #400946] Thu, 30 April 2009 09:46 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
sant_new wrote on Thu, 30 April 2009 16:26
Please give me your suggestions. Thanks

What about reading Oracle documentation and find which privilege is necessary to TRUNCATE the table? It is available e.g. online on http://tahiti.oracle.com/.

Privileges are described in SQL Reference under GRANT statement description (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm#i2155015) in the Table 18-1 System Privileges:
Quote:
System Privilege Name
Operations Authorized

...

DELETE ANY TABLE
Delete rows from tables, table partitions, or views in any schema.

DROP ANY TABLE
Drop or truncate tables or table partitions in any schema.

...

Report message to a moderator

Re: Grant privilege [message #400951 is a reply to message #400946] Thu, 30 April 2009 09:47 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
truncate ignores synonyms

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:737276187582

Report message to a moderator

Re: Grant privilege [message #400960 is a reply to message #400946] Thu, 30 April 2009 12:50 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Thanks for the info.

I just tried truncate with qualified table as 'truncate table cmi.alsc_bl' it worked.

Report message to a moderator

Re: Grant privilege [message #401039 is a reply to message #400949] Fri, 01 May 2009 04:38 Go to previous message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Thu, 30 April 2009 15:32
>Aris user has DBA privilege;
I suspect the statement above is not correct & even if it were correct it would do nothing for TRUNCATE table of other schema.



That's not true.

Report message to a moderator

Previous Topic: TEMP tablespace
Next Topic: Finding number of transactions
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jul 02 23:19:49 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.