Home » RDBMS Server » Security » Access to Public Synonym
Access to Public Synonym [message #221619] Tue, 27 February 2007 13:57 Go to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi,
I was going through Oracle 9i Concepts a chapter on Schema Objects. As per the document,

You can create both public and private synonyms. A public synonym is owned by the special user group named PUBLIC and every user in a database can access it. A private synonym is in the schema of a specific user who has control over its availability to others.

I queried ALL_OBJECTS for v$datafile, it is shown as PUBLIC SYNONYM, however I fail to understand why I am not able to query v$datafile from any other schema (with just create session privilege).

Can anyone explain this idea?

Thanks
qA
Re: Access to Public Synonym [message #221620 is a reply to message #221619] Tue, 27 February 2007 14:35 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
SYNONYMS do not GRANT access to anything. This is why GRANT exists.
Re: Access to Public Synonym [message #221653 is a reply to message #221620] Tue, 27 February 2007 22:56 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Thank you for the update. But then what is the difference between PUBLIC Synonym and PRIVATE Synonym.

I thought PUBLIC Synonym can be queried by any schema users. Correct me if I am wrong.
Re: Access to Public Synonym [message #221659 is a reply to message #221619] Tue, 27 February 2007 23:42 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
>Correct me if I am wrong.
Since you have all the answers, I don't need to say more & you can continue to answer your own questions.
Re: Access to Public Synonym [message #221664 is a reply to message #221659] Wed, 28 February 2007 00:04 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
My Dear Friend,
I don't know all the answers, but I am sure you too do not know the answer to this and that is the reason for you to post irrelevant things.

I don't mind if there are no replies, but don't post replies which are not relevant to the question.
Re: Access to Public Synonym [message #221675 is a reply to message #221619] Wed, 28 February 2007 00:45 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

HI,

I queried ALL_OBJECTS for v$datafile, it is shown as PUBLIC SYNONYM, however I fail to understand why I am not able to query v$datafile from any other schema (with just create session privilege).



The public phrase means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.


SQL> conn system@orcl /oracle
Connected.
SQL> create table test(no number);

Table created.

SQL> create public synonym systemtest for test;

Synonym created.

SQL> conn scott/tiger@orcl
Connected.
SQL> select * from systemtest;
select * from systemtest
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> conn system/oracle@orcl
Connected.
SQL> grant select on test to public;

Grant succeeded.

SQL> conn scott/tiger@orcl
Connected.
SQL> select * from systemtest;

no rows selected

SQL>


Hope this helps
Taj

[Updated on: Wed, 28 February 2007 00:52]

Report message to a moderator

Re: Access to Public Synonym [message #221678 is a reply to message #221619] Wed, 28 February 2007 00:52 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
Mohammad Taj,
THANKS!
Re: Access to Public Synonym [message #221681 is a reply to message #221675] Wed, 28 February 2007 01:04 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi Mohammad,
Thanks for clarifying things. Now I understood.

Regards,
Anand
Re: Access to Public Synonym [message #221688 is a reply to message #221681] Wed, 28 February 2007 01:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10628
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
scott@9i > select owner,object_Name,object_type from all_objects where object_name='V$DATAFILE';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ------------------
PUBLIC                         V$DATAFILE                     SYNONYM

first, it is not just a public synonym. it is a synonym owned by public.
This 'public' is a special category role (and will NOT be listed in dba_roles).
(try creating a role called PUBLIC and you will be denied)

To make a few thing simple (or just to confuse us folks a little),
this public role acts as if it is been granted to all users.
But, PUBLIC role does not have access to all objects/privs by default).

scott@9i > grant create session to public;

Grant succeeded.

scott@9i > create user x identified  by y;

User created.

scott@9i > connect x/y
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
scott@9i > show user
USER is "X"
scott@9i > select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

scott@9i > select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
PUBLIC                         CREATE SESSION                           NO

cott@9i > desc v$datafile
ERROR:
ORA-04043: object "SYS"."V_$DATAFILE" does not exist


scott@9i > !
oracle@mutation#sqlplus -s "sys/sys as sysdba" <<EOF
> grant select on v_\$datafile to public;
> EOF

Grant succeeded.

oracle@mutation#exit
exit

scott@9i > desc v$datafile
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 FILE#                                                          NUMBER
 CREATION_CHANGE#                                               NUMBER
 CREATION_TIME                                                  DATE
 TS#                                                            NUMBER
 RFILE#                                                         NUMBER
 STATUS                                                         VARCHAR2(7)
 ENABLED                                                        VARCHAR2(10)
 CHECKPOINT_CHANGE#                                             NUMBER
 CHECKPOINT_TIME                                                DATE
 UNRECOVERABLE_CHANGE#                                          NUMBER
 UNRECOVERABLE_TIME                                             DATE
 LAST_CHANGE#                                                   NUMBER
 LAST_TIME                                                      DATE
 OFFLINE_CHANGE#                                                NUMBER
 ONLINE_CHANGE#                                                 NUMBER
 ONLINE_TIME                                                    DATE
 BYTES                                                          NUMBER
 BLOCKS                                                         NUMBER
 CREATE_BYTES                                                   NUMBER
 BLOCK_SIZE                                                     NUMBER
 NAME                                                           VARCHAR2(513)
 PLUGGED_IN                                                     NUMBER
 BLOCK1_OFFSET                                                  NUMBER
 AUX_NAME                                                       VARCHAR2(513)

[Updated on: Wed, 28 February 2007 01:24]

Report message to a moderator

Re: Access to Public Synonym [message #406773 is a reply to message #221619] Fri, 05 June 2009 15:33 Go to previous messageGo to next message
palazzi
Messages: 11
Registered: June 2009
Location: Toluca
Junior Member
ok, a public synonym can be queryed by anybody, but if you want to have access to the base table you need to have a grant on that table.

a synonym is just an alias for the table.
Re: Access to Public Synonym [message #406775 is a reply to message #406773] Fri, 05 June 2009 15:50 Go to previous messageGo to next message
babuknb
Messages: 1733
Registered: December 2005
Location: NJ
Senior Member


Thanks. But before updating any thing please check last update date. "Wed, 28 February 2007 12:53 "

Babu
Re: Access to Public Synonym [message #406814 is a reply to message #406773] Sat, 06 June 2009 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, check if what you want to say was not already said by another answer.
It has been clearly explained in the previous posts.

Regards
Michel
Re: Access to Public Synonym [message #410663 is a reply to message #406814] Mon, 29 June 2009 10:15 Go to previous message
jaspreet.nagra
Messages: 1
Registered: June 2009
Junior Member
You can grant access to objects via synonyms. I swear I have never done that and didn't know you could.
SQL> create table table1 (what date);

Table created.

SQL> create table table3 (what date);

Table created.

SQL> insert into table1 values (sysdate);

1 row created.

SQL> insert into table3 values (sysdate + 10000);

1 row created.

SQL> commit;

Commit complete.

SQL> create public synonym table1 for table1;

Synonym created.

SQL> grant select on table1 to oe;

Grant succeeded.

SQL> create public synonym table2 for table3;

Synonym created.

SQL> grant select on table2 to oe;

Grant succeeded.

SQL> conn oe/oe
Connected.
SQL> select * from table1;

WHAT
---------
25-FEB-08

SQL> select * from table2;

WHAT
---------
13-JUL-35

SQL> select * from table3;
select * from table3
                *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from scott.table3;

WHAT
---------
13-JUL-35

SQL>

Notice that I never granted access to table3 but OE has access anyway. That's because when I granted access to the synonym table2, Oracle pushed the access through to the underlying schema object.

Cool. I love it when I discover something so simple.


[EDITED by LF: applied [code] tags]

[Updated on: Mon, 29 June 2009 11:20] by Moderator

Report message to a moderator

Previous Topic: V$PWFILE_USERS is empty
Next Topic: Username & password for Oracle 10g
Goto Forum:
  


Current Time: Thu Oct 23 03:47:03 CDT 2014

Total time taken to generate the page: 0.08394 seconds