Re: Synonym in trucate table, drop table ...

From: ddf <oratune_at_msn.com>
Date: Mon, 22 Dec 2008 09:31:50 -0800 (PST)
Message-ID: <d905b3fb-5df8-4bfc-bf93-2431899c05bf@g38g2000yqn.googlegroups.com>


Comments embedded.

On Dec 20, 3:27 pm, zigzag..._at_yahoo.com wrote:
> On Dec 20, 1:18 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
>
>
>
>
> > zigzag..._at_yahoo.com wrote:
> > > On Dec 19, 3:20 pm, joel garry <joel-ga..._at_home.com> wrote:
> > >> On Dec 19, 5:40 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
> > >>> On Dec 18, 2:16 pm, joel garry <joel-ga..._at_home.com> wrote:
> > >>>> On Dec 18, 10:46 am, zigzag..._at_yahoo.com wrote:
> > >>>>>  am on Oracle 10.2.0.3 on HP UNIX 11i.
> > >>>>> When I execute a statement:
> > >>>>> truncate table t;
> > >>>>> where t is a synonym for a table tx
> > >>>>> I get error
> > >>>>> ORA-00942: table or view does not exit
> > >>>>> However, if I do truncate table tx
> > >>>>> table is deleted.
> > >>>>> synonym however works for insert, select, delete, update etc.
> > >>>>> What is rationale for not recognizing synonyms in ceratin statements.
> > >>>>> Thanks
> > >>>> I suppose it would be, when you are doing DDL, you want to do it on
> > >>>> the proper object.  http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statem...
> > >>>> says what you can do, but not the rationale.  So I guess that means
> > >>>> that's just the way it is.  Also, consider the differences between
> > >>>> public and private synonyms.  Maybe it just gets too hinky with the
> > >>>> possibilities of dependent objects with several degrees of separation.
> > >>>> Besides that, there are bugs, like this one "fixed in the 10.2.0.5
> > >>>> patchset" metalink Note:603093.1
> > >>>> jg
> > >>>> --
> > >>>> @home.com is bogus.
> > >>>> Spear phishing:  http://ibtimes.com/articles/20081217/personalized-spam-rising-sharply...-
> > >>>> - Show quoted text -
> > >>> I look at it this way.  DDL opertates on the object in the command.
> > >>> Only tables and the indexes on them can be truncated so truncate
> > >>> table_synonym is not valid because synonym is not a table and the only
> > >>> valid operations on a synonym are create/replace and drop.
> > >>> The truncate command has worked this way since it was introduced and
> > >>> since I am not usually the owner when I use the command I have used:
> > >>> "truncate owner.table_name" when I have needed to perform truncates.
> > >>> You can grant non-owners the ability to truncate a table via a stored
> > >>> procedure owned by the target table owner that uses dynamic SQL to
> > >>> issue the truncate command or better yet a procedure owned by a DBA
> > >>> privileged account that accepts the owner and table_name and compares
> > >>> it against an authorized to be truncated table list.  This can help
> > >>> protect your system from soneone using the first type of routine to
> > >>> truncate the wrong table.
> > >>> HTH -- Mark D Powell --
> > >> So why can't you truncate over a link?  Doesn't that specify a
> > >> particular table/owner?  But it acts like a synonym, which I guess it
> > >> must be.
>
> > >> jg
> > >> --
> > >> @home.com is bogus.
> > >> If at first you don't succeed... jump off!http://video.signonsandiego.com/vmix_hosted_apps/p/media?id=2531504&i...quoted text -
>
> > >> - Show quoted text -
>
> > > I noticed that Oracle allows truncate in some DDL statements but not
> > > in others. I do not find any consistenecy in what Oracle does, If it
> > > lets us delete rows from a table,  why it won't let you drop a table.
>
> > TRUNCATE and DELETE are two entirely different things. First off the
> > first is DDL and the second DML. Secondly I can recover from the later
> > with ROLLBACK. From the first, traditionally, I am running for the
> > backup tapes. There is a huge difference between running FLASHBACK TABLE
> > and FLASHBACK DATABASE.
>
> > But as to your initial comment: "allows truncate in some DDL
> > statements." This is incorrect by almost any definition.
>
> > TRUNCATE is DDL and TRUNCATE and is not allowed, whatever that means,
> > in any other DDL statement. It is a statement unto itself.
> > --
> > Daniel A. Morgan
> > Oracle Ace Director & Instructor
> > University of Washington
> > damor..._at_x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>
> > - Show quoted text -
>
> Sorry, I meant to say synoymns are allowed in severl DDL statements:http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statem...,
> so saying that synonyms are only allowed in DML is not correct.

And all of those DDL statements have the form of:

<command> <options> ON <object>;

For example:

SQL> --
SQL> -- Create table
SQL> --
SQL> -- Use a logical name
SQL> --
SQL> create table sales_data(
  2          sales_id   number,
  3          year    number,
  4          quarter number,
  5          sales   number

  6 );

Table created.

SQL>
SQL> --
SQL> -- Grant all privileges on our
SQL> -- sales_data table to another
SQL> -- user
SQL> --
SQL> grant all on sales_data to yarkput;

Grant succeeded.

SQL>
SQL> --
SQL> -- Connect as that other user
SQL> --
SQL> connect yarkput/^^^^^^^^^^^^^

Connected.
SQL>
SQL> --
SQL> -- Create a synonym
SQL> --
SQL> -- Use a mysterious name
SQL> -- having nothing at all to
SQL> -- do with the table's purpose
SQL> --
SQL> -- This flies in the face of providing
SQL> -- transparency, but, hey, it's
SQL> -- 'legal'
SQL> --
SQL> create synonym grompenflumper

  2 for bing.sales_data;

Synonym created.

SQL>
SQL> --
SQL> -- Execute an approved DDL command
SQL> -- against the synonym
SQL> --
SQL> audit select on grompenflumper;

Audit succeeded.

SQL>
SQL> --
SQL> -- Attempt to truncate the base table
SQL> -- through the synonym
SQL> truncate table grompenflumper;
truncate table grompenflumper
               *

ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> --
SQL> -- Gee, it appears that grompenflumper isn't
SQL> -- a table ...
SQL> --
SQL> -- which it isn't, it's a synonym
SQL> -- and there is no 'truncate synonym' command
SQL> --
SQL> --
SQL> -- We try again, using the fully qualified
SQL> -- object name
SQL> --
SQL> -- Goll-ee, we succeed
SQL> --
SQL> truncate table bing.sales_data;

Table truncated.

SQL>
>
> Puropose of synonym is to use another name for existing name.

No, it isn't.

> If
> Oracle allows this, it should be  consistent through all statements.

It is. Why do you fail to see this?

> You or anyone can come up with your own reasons why truncate is  not
> allowed with synonyms,

Show me a 'truncate synonym' command in Oracle. One doesn't exist as a synonym is a 'pointer' to another object, allowing a user to reference that object absent the owner.object_name syntax. This is why the 5 DDL command that *are* allowed work; they allow the synonym to be resolved to its base object:

audit ...  on ...
noaudit ... on ...
grant ... on ... to ...
revoke ... on ... from ...
comment ... on ....

> but in my view there is really no good reason
> why  synoymns  should not be allowed every where.- Hide quoted text -
>

The fact that a synonym is NOT a table is a pretty good one in my estimation.

> - Show quoted text -

David Fitzjarrell Received on Mon Dec 22 2008 - 11:31:50 CST

Original text of this message