11 Replies Latest reply: Feb 14, 2014 9:49 AM by BPeaslandDBA RSS

    create synonym on synonym

    1007945

      Hi Team,

       

      I am using version 10g database.I have 3 Schema

      1. NET_TKM

      2. NET_STG

      3. NET_SRC

       

      First connected to (NET_TKM) I have given all the grant to NET_STG

       

      grant all on  CB_ACCOUNT_MASTER to NETONE_STG WITH GRANT OPTION;

      *out put :- grant succeeded.


      Second Connected to (NET_STG) I have created synonym

       

      create or replace synonym CB_ACCOUNT_MASTER FOR NET_TKM.CB_ACCOUNT_MASTER;

      *out put :- created

       

      and


      grant all on  CB_ACCOUNT_MASTER to NETONE_SRC ;

      *out put :- grant succeeded.

       

      Three Connect to (NET_SRC) i am creating synonym

       

      create or replace synonym CB_ACCOUNT_MASTER FOR NETONE_STG.CB_ACCOUNT_MASTER;

       

      Getting an error :-

      SQL Error: ORA-01031: insufficient privileges

      01031. 00000 -  "insufficient privileges"

      *Cause:    An attempt was made to change the current username or password

                 without the appropriate privilege. This error also occurs if

                 attempting to install a database without the necessary operating

                 system privileges.

                 When Trusted Oracle is configure in DBMS MAC, this error may occur

                 if the user was granted the necessary privilege at a higher label

                 than the current login.

      *Action:   Ask the database administrator to perform the operation or grant

                 the required privileges.

                 For Trusted Oracle users getting this error although granted the

                 the appropriate privilege at a higher label, ask the database

                 administrator to regrant the privilege at the appropriate label.

        • 1. Re: create synonym on synonym
          1007945

          We can create like this or not?

          • 2. Re: create synonym on synonym
            GregV

            Hi,

             

            NET_SRC needs the CREATE SYNONYM privilege.

            • 3. Re: create synonym on synonym
              1007945

              Thank Greg. Yes required privilege on NET_SRC . once get the privilege we can able to create or not? i.e create synonym to other synonym?

              • 4. Re: create synonym on synonym
                GregV

                Yes you can.

                • 5. Re: create synonym on synonym
                  Mark D Powell

                  Why not use PUBLIC synonyms?  Just create public synonym object_name for owner.object_name then any user with select privilege on original table can select against it while users without a privilege on the object cannot.

                   

                  HTH -- Mark D Powell --

                  • 6. Re: create synonym on synonym
                    BPeaslandDBA

                    I agree with Mark here. Use a PUBLIC synonym rather than a synonym on a synonym. Life can get really difficult down the road with this approach if you get to the point where you have a synonym on a synonym on a synonym on a synonym on a synonym on a synonym on a synonym on a synonym, etc. And then someone comes along a drops a synonym in the middle and the chain is broken. Use a PUBLIC synonym and there is no chain.

                     

                    Also, if another user has an object with the same name as the public synonym, the user's object will take precedence for that user.

                     

                    Cheers,

                    Brian

                    • 7. Re: create synonym on synonym
                      brunovroman

                      Hello,

                       

                      one additional remark, not about the SYNONYMs, but about something that I dislike a lot: I see
                      "grant all on  CB_ACCOUNT_MASTER to NETONE_STG WITH GRANT OPTION;"


                      Wow!
                        GRANT ALL: do you really want to give privileges like "ALTER"? Usually we give SELECT, INSERT, UPDATE, DELETE. Don't give more if not necessary -maybe you want to give REFERENCE so that user B can create a foreign key pointing to a table of user A, but this is rare. Anyway, don't grant "ALL"...
                        WITH GRANT OPTION: be aware of what you do when you do this... Not recommended to say the least.

                       

                      Best regards,

                       

                      Bruno Vroman

                      • 8. Re: create synonym on synonym
                        Ratnesh Kumar Roy

                        Note: Here we are creating synonym over synonym, but we should not make it so big.  always we should create public synonym to achive this kind of needful

                         

                        SQL> select ename from emp where ename='TURNER';

                         

                         

                        ENAME

                        ----------

                        TURNER

                         

                         

                         

                        SQL> create synonym emp_alias for emp;

                         

                         

                        Synonym created.

                         

                         

                         

                        SQL> select * from emp_alias where ename='TURNER';

                         

                         

                             EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

                        ---------- ---------- --------- ---------- --------- ---------- ----------

                            DEPTNO

                        ----------

                              7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0

                         

                        SQL> create synonym emp_alias_2 for emp_alias.

                         

                         

                        Synonym created.

                        • 9. Re: create synonym on synonym
                          brunovroman

                          Hello,

                           

                          to talk about cascade of synonyms: I don't like this idea either, but I am afraid I tend to disagree with PUBLIC synonyms because this is "bad" regarding consolidation and it is also more complex when working with datapump (easy to forget...)

                           

                          Nowadays we put often more than one "application" in a database, one appli being made of one or some schemas.
                          The use of PUBLIC synonyms for one application "pollutes" the other applications and can cause problems.
                          What if for example the database contains:
                          a_admin, a_user, a_reader for application a, a_admin being the owner of a table "users"
                          b_admin, b_user, b_reader for application b, b_admin being the owner of a table "users"
                          and if both want to use public synonyms to point to their table "users"...

                          To come back to original question, I think that I would try a solution where each owner stays his own master (regarding privileges granted) and each "subscriber" has its own synonyms, for example:
                          NET_TKM owns table t1 and grants necessary privielges on this table to role(s), role(s) that is(are) given to other users.
                          NET_STG has a private synonym tkm_t1 -it might also be called t1- pointing to net_tkm.t1
                          NET_SRC has a private synonym tkm_t1 -it might also be called t1- pointing to net_tkm.t1

                          (alternatively to the use of synonyms, I make often use of "alter session set current schema", this works great if the users don't have their own objects)

                           

                          Best regards,

                           

                          Bruno.

                          • 10. Re: create synonym on synonym
                            Mark D Powell

                            The problem of two owners having table with the same name and wanting to use public synonyms can be solved in several ways.  First we do not allow the same object names to appear under multiple owners in our home grown applications.  Our naming standards require objects to be prefixed with the application code that the object belongs to like AP, AR, GL, IC, etc  So if a WORKORDER table is created for Engineering and another for Manufacturing the tables would be named something like ENGR_WORKORDER and MF_WORKORDER.  This prevents conflict to begin with.

                             

                            For vendor products the issue can be tougher.  What we normally do depending on how the vendor designed their application (connect and run as owner who has DBA privilege) is dedicate a database to the product.  This prevents conflict but stops consolidation.  Still security demands separation and products that run under a DBA privileged username with often less that secret passwords are not secure so running another product with it makes the other product unsecure.  Security wins, keep the products separate.

                             

                            In the cases where we cram the products into the same database step one is to check if the product actually needs a public synonym to function.  If it does then the product cannot share a database with which it has a name conflict.  If on the other hand the application does not need them then we do not create them for that application.  That would allow two products with object name overlap to co-exist.

                             

                            What works depends on the shop, what applications you are running, how the applications were written, and the resources available to support the business environment.

                             

                            -- Mark D Powell --

                            • 11. Re: create synonym on synonym
                              BPeaslandDBA

                              wadays we put often more than one "application" in a database, one appli being made of one or some schemas.

                              The use of PUBLIC synonyms for one application "pollutes" the other applications and can cause problems.

                               

                              Good points...but upgrade to 12c and use the mutli-tenant database and this is no longer an issue.

                               

                              Cheers,
                              Brian