14 Replies Latest reply: Jan 23, 2013 3:41 PM by rp0428 RSS

    Public Synonym question

    polo
      Hi,

      Our DBA (?) has a rule here that all tables must have public synonym.

      Is this a industry standard practice?

      I am not a DBA but in recent assignment i need to setup a schema and i have been to told to create public synonums for each table (regardless if they will be used across schemas or not). I was under the impression that you usually create a private synonyms for each table and when you need to access table (or object) in another schema/user, you create private synonym in that new schema/user. is that a correct procedure?

      One last point, how would you handle same table name (like address table) in multiple schemas if you have standard practice of creating public synonyms for each tables? that would be problem isn't it?

      I just want to find out how DBAs are handling synonyms creating procedure and rules.

      Thank you in advance

      -R
        • 1. Re: Public Synonym question
          sb92075
          polo wrote:
          Hi,

          Our DBA (?) has a rule here that all tables must have public synonym.

          Is this a industry standard practice?
          NO!

          IMO, it is a silly policy.
          • 2. Re: Public Synonym question
            AdamMartin
            It is a best practice to avoid the use of public synonyms. There needs to be a compelling reason to use public synonyms before they should be employed.

            http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7769752293246
            • 3. Re: Public Synonym question
              Mark D Powell
              Lots of shops create public synonyms for their tables so that applications that do not run as the owner can locate the target tables without having to code owner.object_name in the code or create long lists of private synonyms for users.

              A very common practice.

              HTH -- Mark D Powell --
              • 4. Re: Public Synonym question
                polo
                Thank you SB, Adam and Mark for your response

                Mark,

                Logically, it only makes sense to have public synonyms for table which may be used across schemas (even that is debatable).

                If you makes it a standard practice, you are basically asking for trouble when you have similar name tables in multiple schema (like address, phone, etc..).

                just trying to understand your point....

                -R
                • 5. Re: Public Synonym question
                  jgarry
                  It's a common practice, but only sensible if only one schema has all the data. That makes little sense in a modern Oracle database, but some businesses last for more than a year or so and have historical constraints, or even strange ideas of what a database is.
                  • 6. Re: Public Synonym question
                    P.Forstmann
                    polo wrote:

                    If you makes it a standard practice, you are basically asking for trouble when you have similar name tables in multiple schema (like address, phone, etc..).
                    Exactly: this bad practice may require to create different databases just to store different schemas because they cannot exist in the same database.
                    • 7. Re: Public Synonym question
                      EdStevens
                      polo wrote:
                      Thank you SB, Adam and Mark for your response

                      Mark,

                      Logically, it only makes sense to have public synonyms for table which may be used across schemas (even that is debatable).

                      If you makes it a standard practice, you are basically asking for trouble when you have similar name tables in multiple schema (like address, phone, etc..).

                      just trying to understand your point....

                      -R
                      So as you see from the conflicting answers, the answer is (drum roll, please) ..... "it depends."
                      • 8. Re: Public Synonym question
                        rp0428
                        >
                        Logically, it only makes sense to have public synonyms for table which may be used across schemas (even that is debatable).
                        >
                        There is another aspect to the 'public synonym' issue that hasn't been mentioned yet: security.

                        The primary directive for ALL security consideration is: need to know. From the security perspective objects should not be visible to ANY schema by ANY means unless that schema has a 'need to know'.

                        Using public synonyms for ALL tables violates that basic security principle.

                        From a pure security perspective public synonyms should not really be used at all.

                        They are a convenience since the 'SCHEMA.OBJECT_NAME' syntax can always be used to reference objects that a user has privileges on.

                        If public synonyms are used then, in my opinion, they should only be used for objects that REQUIRE universal access.

                        And, as you suggest, for objects that are only needed within a given schema (e.g. work tables) there is no point whatsoever in having a public synonym. Those work tables will generally be accessed via stored procedures in the same schema so no schema prefix will be needed even if those procedures are executed by other schemas (think DEFINER rights privileges on the procedure).
                        >
                        If you makes it a standard practice, you are basically asking for trouble when you have similar name tables in multiple schema (like address, phone, etc..).
                        >
                        Excellent point!

                        That is one of the main causes of problems in QA/TEST/DEV environments (particularly DEV) when developers unknowingly access the wrong table because of a synonym. Their code accesses their private table when they are connected as themselves but will access a different table when they connect as the application user.

                        The fact that the application user usually has privileges granted via roles rather than directly, while the developer often has priviliges granted directly can cause even more confusion and problems. That can cause code to either not work at all, or produce different results. Widespread use of public synonyms just complicates things unnecessarily.
                        • 9. Re: Public Synonym question
                          Mark D Powell
                          You do not need all objects to be in one schema. What you need is a table naming standard that does not allow duplicate table names to be used. All home grown applications follow a naming standard where the object starts with a system code. So you can have an address table in accounts payable and in accouts receivable without conflict since the names would be ap_address, ar_address respectively.

                          Vendor applications should generally be placed in their own database or maybe in a container wth 12c becuase most vendor products do stupid things like connect every user as the application owner which must be a DBA privileged user and uses a well known password. In some cases vendor support even has remote access. Then I know of vendor products that create an user user for the application user and when you remove the user from the application perform a drop user in Oracle. This is not good if the end user uses the same Oracle username with another application that uses the same database.

                          We do not allow home-grown applications to run as the object owner. Though one set of private synonyms would likely work for the application you have the fact that the end users have access via adhoc query tools. The end customer does not want to have to worry about who owns a table. They just want to reference it.

                          You can argue both ways but you have to handle the applications and vendor products your customer alreadys has and buys or develops in the future means you may need to use multiple databases or provide the synonyms demanded by outside requirements.

                          IMHO -- Mark D Powell --
                          • 10. Re: Public Synonym question
                            Mark D Powell
                            Public synonyms do not grant any object level access to the referenced objects. You still need an object privilege grant for that. All you can do is tell that an object exists.

                            HTH -- Mark D Powell --
                            • 11. Re: Public Synonym question
                              polo
                              Thank you guys for your prompt response...

                              Now its time for me to start (battle) discussion with the DBAs here and make him understand about this practice (and without any authority....also)

                              Next plan is to start this political battle.....

                              Thanks bunch guys

                              -Raj
                              • 12. Re: Public Synonym question
                                jgarry
                                If you can tell that an object exists, that tells a bad guy to bother trying to get it. The basic problem with security by obscurity, you never know how easily someone will clarify things. Since most security violations are inside jobs, this becomes more important than it might seem on its face. Not that you can do anything about it when given existing silly things you can't change.
                                • 13. Re: Public Synonym question
                                  AdamMartin
                                  polo wrote:
                                  Thank you guys for your prompt response...

                                  Now its time for me to start (battle) discussion with the DBAs here and make him understand about this practice (and without any authority....also)

                                  Next plan is to start this political battle.....

                                  Thanks bunch guys

                                  -Raj
                                  I get the impression that people coming here might not be reading the discussion in the Ask Tom link I posted above. So to help you out a little, and get you some ammunition for the discussion you are planning, I have selected some powerful quotes from Tom Kyte on the issue from the Ask Tom website.

                                  Let me repeat that the best practice is to avoid the use of public synonyms. The following are all by Tom:

                                  December 10, 2001
                                  You should limit your use of public synonyms.
                                  When I write PLSQL code, I never use public synonyms in the code itself. I use schema names in the code.

                                  June 18, 2005
                                  Do not use public synonyms, not for performance reasons but because globals in general are "bad" and you'll prevent consolidation of systems down the road.

                                  March 12, 2007
                                  AVOID public synonyms at all costs.
                                  Use ALTER SESSION set current_schema=schema if you want, but avoid public synonyms.
                                  It makes it impossible to install your application in some database with other applications we'll almost certainly hit a name that you have!!!

                                  January 26, 2009
                                  Some people do not want to have to use the schema name when selecting from a table. Hence, they tend to flood the system with a lot of evil public synonyms. Public synonyms like triggers, autonomous transactions, when others then null, .... should be avoided, especially by those that cannot figure out when they would not be evil. Public synonyms are another 'feature' I wish we didn't have... They make trojan horses all too possible and as importantly they flood the namespace - making consolidation of many developed applications impossible.

                                  January 28, 2009
                                  public synonyms -> evil.

                                  December 16, 2009
                                  There are many reasons to avoid public synonyms like the plague. Public synonyms should not be used--it makes consolidation virtually impossible

                                  May 26, 2010
                                  Do not use public synonyms.
                                  I will never propose the widespread use of PUBLIC synonyms - they are evil, to be avoided at all costs. They make consolidation impossible (namespace is cluttered) and when used widely can open opportunities for security issues (not because they are insecure, but because it is far too easy to redirect code to use something it was not meant to use).

                                  June 24, 2010
                                  Public synonyms should just be avoided - it makes consolidation impossible - there can only be "one" public synonym. I don't like them for that reason. Public synonyms can also be considered a security issue as it is easy to put a "trojan" in there by creating some object in the local schema that overrides the public synonym in scope.

                                  February 1, 2011
                                  Public synonyms should be avoided.

                                  July 22, 2011
                                  Never create public synonyms. They cause a performance hit during parse time. They are a security issue (I wish we didn't create public synonyms - it would be MUCH better if people referenced SYS.dbms_sql, not just dbms_sql. Far too easy for someone to drop in a trojan bit of code and replace in your scope the meaning of dbms_sql and intercept your inputs and modify them). It makes maintaining the code harder (implicit things always do, BE EXPLICIT). It prevents consolidation of multiple applications into a single database. Do NOT use public synonyms!

                                  July 29, 2011
                                  [Prefixing tables like <schema>.<table_name> inside code] would be my first choice by far.
                                  With private synonyms being a second choice.
                                  With views being a third choice.
                                  And public synonyms NOT being a choice at all.

                                  October 12, 2011
                                  Please do NOT use public synonyms. They represent a certain security risk (very easy to repoint them to something else - something malicious). They represent a performance overhead you don't need. They represent massive confusion. They represent a limited namespace - if an object by that name already exists - well - bummer.

                                  April 6, 2012
                                  Do not use public synonyms, avoid them at all times.
                                  • 14. Re: Public Synonym question
                                    rp0428
                                    >
                                    Now its time for me to start (battle) discussion with the DBAs here and make him understand about this practice (and without any authority....also)

                                    Next plan is to start this political battle.....
                                    >
                                    Seriously, don't you have better things to do with your time?

                                    You already said you are not the DBA so why do you want to pick a battle like this that you probably can't win, won't really help the organization and will likely just piss people off? I suggest you keep the bigger picture in mind.

                                    I seriously doubt that management would want to spend time or resources to change the existing system given that, even if the changes are 100% successful, the system isn't going to run any better than it does now. And to try to achieve that '100% successful' means creating and implementing tests for ALL current functionality of the app rather than just the incremental change testing that is likely going on now for each release.

                                    I would limit the scope of your discussion to NEW functionality or applications and an argument that NO functionality in Oracle (including SYNONYMS) should be used without proper justification. That way you put the burden on the current proponents to provide a business justfication for using that functionality that overrides the arguments made by others in this forum and in the blogs by Tom Kyte.

                                    And by limiting the scope to NEW, forward-looking development management can't argue rework or testing issues at all which pretty much cuts the legs out from under anyone supporting the current method of doing things. Once this 'new way' has proven itself it will be easier to get management to switch over to it when major mods are done to existing functionality.

                                    You need to be real careful you try to bite off more than you can chew, especially if you are 'without any authority'!