Global Synonyms and Views 1

[Michael42]

Hello,

In Oracle 8.17 on Sun Solaris, could you please help me understand Synonyms and Views at bit deeper. I know that if I want to create an "interface" into multiple tables I can do it via a View. I have been reading various materials and still do not grasp how to do this. If you could please elaborate so even I can understand the syntax and steps.

1. How can I create a View that ALL users that access my database instance can perform SELECTs and INSERTs on?

2. How can I create a Synonym to the above View to be used by ALL users that need access to the View?




Thanks,

Michael42

 

[SantaMufasa]

Michael,

Oracle VIEWs are simply named SELECT statements. You can create a VIEW that provides access to 1, many, or all columns and/or rows of one or more other TABLEs (and/or VIEWs). If you wish to allow updates via the VIEW, it is usually best to make the VIEW for a single table only.

Here is the syntax to create a VIEW against certain columns of an EMPLOYEE table (both owned by user "TEST"):

create or replace view emp as
select id, last_name, first_name, commission_pct, salary
from s_emp
/

View created.

desc emp

Name                    Null?    Type
----------------------- -------- ------------
ID                      NOT NULL NUMBER(7)
LAST_NAME               NOT NULL VARCHAR2(25)
FIRST_NAME                       VARCHAR2(25)
COMMISSION_PCT                   NUMBER(4,2)
SALARY                           NUMBER(11,2)

Then, to allow anyone to make changes to the underlying table, "TEST.S_EMP", via the view "TEST.EMP", issue the following GRANT:

grant insert, update, delete, select on emp to public;

(Of course this is certainly an unrealistic example from a business perspective since you do not want anyone making changes to names and salaries for any employees. . But, to illustrate syntax, this example is fine.)

To allow the all the updaters to make changes via the EMP view without specifying "TEST.EMP", someone with "CREATE PUBLIC SYNONYM" privileges can issue the following command:

create public synonym emp for test.emp;

Synonym created.

Following the creation of the PUBLIC SYNONYM, anyone can make updates to TEST.EMP as this change by user "YADA":

update emp set salary = 5000 where last_name = 'Velasquez';

1 row updated.

Let us know if this resolves your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 15:47 (15Nov04) UTC (aka "GMT" and "Zulu"),
@ 08:47 (15Nov04) Mountain Time

 

[Michael42]

SantaMufasa,

Many thanks again for your great advice!

You very concisely described the entire scenario that I have been scouring many books to put together.

SantaMufasa, I can only give you one star it seams but if you have a web site with your contact info I (and am sure MANY others) would love to buy you a pizza or the like.

-Michael42

 

[SantaMufasa]

Michael,

Thanks for your feedback. You are very kind. Others have expressed interest in making contact on non-Tek-Tips-related issues, so I believe I shall change my signature to include my e-mail for just such occasions. I'll also start including our web address sometime tomorrow, after we finish some scheduled maintenance on our site. (I believe this all still complies with Tek-Tips site policies. If not, I'll hear about it and make appropriate adjustments.)

It's a pleasure to work with you and my many other friends on Tek-Tips. You all feel like my family (and heaven knows I spend more time with you all than I do my real family .) I just wish that we could somehow, sometime, have a "Tek-Tipsters Convention" so that we could all get together to enjoy pizza and (root) beer together. .

Looking forward to more Tek-Tips fun together...

Mufasa
(aka Dave of Sandy, Utah, USA)
e-mail: dave@dasages.com
@ 22:21 (15Nov04) UTC (aka "GMT" and "Zulu"),
@ 15:21 (15Nov04) Mountain Time