-
Using public synonym
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.
-
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
I remember when this place was cool.
-
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.
Jeff Hunter
-
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!
I remember when this place was cool.
-
"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.
-
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
I remember when this place was cool.
-
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
I remember when this place was cool.
-
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.
Code:
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
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|