Using public synonym

[pcotten]

If a public synonym is created for a view, can any user access that view?

Can user within the database where the view and synonym are, access the view without explicit privileges granted?

Can a user from a different database using a db link to this database access the view as well? Thanks.

[Mr.Hanky]

Well, I would say no.

I just created a table and a public synonym for it.
When logged on as another user I could not see the table.
After granting select to public I could see it fine.

Try it.

MH

[marist89]

A synonym is nothing more than an alias. If the user doesn't have permission on the base object, he won't be able to access it through the synonym.

[Mr.Hanky]

Originally posted by marist89
A synonym is nothing more than an alias. If the user doesn't have permission on the base object, he won't be able to access it through the synonym.

Yer still my hero!

[pcotten]

"A synonym is nothing more than an alias. If the user doesn't have permission on the base object, he won't be able to access it through the synonym."

What is a public synonym then? what it used for? I just created a view under a user schema. I am able to see it because I have a DBA account. Another user wants to be able to select from the view and is coming from a different database via a dba link. How do I make sure he can select from the view? Thanks.

[Mr.Hanky]

Well, you just answered your own question. Here is one application.

Instead of....
SQL> select * from your_long_ass_id.long_arse_table_name@long_arse_db_link;

You can simply....

SQL> select * from new_synonym;

Get it?

MH

[Mr.Hanky]

Another application would be to simply make the table name more user freindly.

Instead of TOU812 you could create a synonym called emp.

Remember "user" is a 4 letter word!

MH

[marist89]

Originally posted by pcotten
What is a public synonym then? what it used for? I just created a view under a user schema. I am able to see it because I have a DBA account. Another user wants to be able to select from the view and is coming from a different database via a dba link. How do I make sure he can select from the view? Thanks.

A public synonym is nothing more than an alias that every person can see.

system@nt817.us> connect user1/user1
Connected.
system@nt817.us> @d:\scripts\login

user1@nt817.us> create table xyz (x number(10), y varchar2(200));

Table created.

Elapsed: 00:00:00.70
user1@nt817.us> insert into xyz values (1, 'user1''s xyz table');

1 row created.

Elapsed: 00:00:00.50
user1@nt817.us> commit;
Commit complete.

Elapsed: 00:00:00.00
user1@nt817.us> create synonym abc for xyz;

Synonym created.

Elapsed: 00:00:00.11
user1@nt817.us> grant select on xyz to user2;

Grant succeeded.

Elapsed: 00:00:00.80
user1@nt817.us> connect user2/user2
Connected.
user1@nt817.us> @d:\scripts\login
user2@nt817.us>  create table xyz (x number(10), y varchar2(200));

Table created.

Elapsed: 00:00:00.40
user2@nt817.us> insert into xyz values (2,'user2''s xyz table');

1 row created.

Elapsed: 00:00:00.10
user2@nt817.us> commit;

Commit complete.

Elapsed: 00:00:00.10
user2@nt817.us> create synonym abc for xyz;

Synonym created.

Elapsed: 00:00:00.10
user2@nt817.us> select * from xyz;

         X Y
---------- ------------------------------
         2 user2's xyz table

Elapsed: 00:00:00.30

user2@nt817.us> select * from abc;

         X Y
---------- ------------------------------
         2 user2's xyz table

Elapsed: 00:00:00.10
user2@nt817.us> select * from user1.abc;

         X Y
---------- ------------------------------
         1 user1's xyz table

Elapsed: 00:00:00.20
user2@nt817.us> create public synonym def for user1.xyz;

Synonym created.

Elapsed: 00:00:00.70
user2@nt817.us> select * from def;

         X Y
---------- ------------------------------
         1 user1's xyz table

Elapsed: 00:00:00.10
user2@nt817.us> connect user3/user3
Connected.
user2@nt817.us> @d:\scripts\login
user3@nt817.us> select * from def;
select * from def
              *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.20
user3@nt817.us> connect user1/user1
Connected.
user3@nt817.us> @d:\scripts\login
user1@nt817.us> grant select on xyz to user3;

Grant succeeded.

Elapsed: 00:00:00.10
user1@nt817.us> connect user3/user3
Connected.
user1@nt817.us> @d:\scripts\login
user3@nt817.us> select * from def;

         X Y
---------- ------------------------------
         1 user1's xyz table

Elapsed: 00:00:00.10