Synonym vs. View 2

[CrystalVis]

Can you please help me understand why use synonym instead of view. My understanding is if you create a synonym, then you don't have to specify the schema owner and the users do not know the real objects that they are referencing. Same idea with using a view. You create a big temporary table that reference other tables in the db. I read that you can create a synonym for tables or views in the db. This is why I don't understand the purpose of creating a synonym. Your explanation is greatly appreciated.
TIa

 

[SantaMufasa]

Tia,

Since the effects of VIEWs and SYNONYMs can seem similar, and if you just look at the effects of each, then it can sometimes be difficult to discern the differences between VIEWs and SYNONYMs. So, to distinguish them, we must look closer at their fundamental purposes. First, let's distinguish their definitions and uses:

SYNONYM: An alias or pseudonym (that is stored in the database) for another database object such as a TABLE or a VIEW.

VIEW: A named SELECT statement that is stored in the database and itself becomes a two-dimensional, queriable object.

Now, when you query a SYNONYM or you query a VIEW, the result is a two-dimensional (ROWS and COLUMNS) set of output -- that is the effect.

Additionally, you can create a SYNONYM (an alternate name) for a VIEW, and/or you can create a VIEW (a named SELECT statement) against a SYNONYM. But they are not doing the same things.

To help distinguish: you can do a
"CREATE VIEW some_name AS SELECT ..." which gathers data from five tables or views in five different schemas (users). You cannot, however, say:
"CREATE SYNONYM my_name FOR SELECT ...", gathering data from five tables or views. You can say, "CREATE SYNONYM my_name FOR some_name;"

So the key to distinguishing VIEWs and SYNONYMs is focusing on purpose and method/syntax of each: VIEW names a SELECT statement; SYNONYM creates an alias for a table or view.

Did this help or not?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:53 (06Feb04) GMT, 09:53 (06Feb04) Mountain Time)

 

[CrystalVis]

Mufasa,

so is it safe for me to state the sole purpose of creating synonym is to hide the actual db objects from the users? Is it correct? If that is the case, then isn't view serving the same purpose. Unless you want to hide the actual view name from the users as well. I just want to have a clear understanding between the two. Your explanation is much appreciated. Thanks

 

[SantaMufasa]

Tia,

No, a synonym does not hide the actual db objects from the users. Users can query the view "ALL_SYNONYMS" in which they can see not only the SYNONYM_NAME, but also OWNER (of the SYNONYM), TABLE_OWNER, and TABLE_NAME (to which the SYNONYM applies). Therefore, nothing, really, is hidden from the user. You can say, however, that the typical purpose for creating a synonym is to simplify references to another table or view.

Similarly, a typical purpose for a VIEW is to simplify the use of a SELECT statement. For example, a view definition could be a 700-line SELECT statement. Which would you rather type a) SELECT * from view_name; or b) 700 lines of SELECT. Sure, you could run a script which contains the 700 lines of code, but you cannot treat a script as a table, which you can do with a view.

Again, to differentiate the VIEW from the SYNONYM, you cannot say: &quot;CREATE SYNONYM syn_name for <700-lines-of-SELECT-code>;&quot;. You can say, &quot;CREATE VIEW view_name AS <700-lines-of-SELECT-code>;&quot;, then you can say, &quot;CREATE SYNONYM syn_name FOR view_name;&quot;.

Are we getting closer to the truth here? Feel free to post more questions until you feel good about all this.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:44 (06Feb04) GMT, 12:44 (06Feb04) Mountain Time)

 

[CrystalVis]

I got it now. I just found another piece of information regarding to synonym. you can grant permission within a synonym to allow other users to access the objects that you create in your own schema. let say i create a view call vw_account in my schema call demo. I want to allow John to access demo.vw_account, so i would create a synonym account and grant permission to synonym account so that John can query the demo.vw_account. is it another reason why one must create synonym in order to grant permission to other users via the synonym? another word without synonym account, I can't grant permission to John to access demo.vw_account. thanks for your time and explanation.

 

[Turkbear]

Hi,
You can grant permissions to any user for any object you own ( I will assume you own demo).

grant select on vw_account to John;

Biggest difference is to John, if no synonym he would need to use

select * from demo.vw_account;

Instead of ( if you create a synonym called vwaccount and grant permission to John to select from it ( grant select on vwaccount to John):

select * from vwaccount;

Of course, the synonym can be any name, including the table name itself.