1 2 Previous Next 17 Replies Latest reply: Aug 12, 2010 1:50 AM by Rupesh Shelar RSS

    synonyms or grant select access

    692864
      hi i have a database with 2 user_login

      1) owner login
      2) user login

      3)all tables that are created in the database will have the owner as ( owner login username)

      4)user will be able to insert,delete,update,execute the objects of the owner




      Now my question is ..... I have created all the tables through oWNER LOGIN

      how do i make sure the point no. 4 is satisfied............?

      my dba says to create synonyms etc which i did not understand.............. he can directly give ins,upd,select,exec privileges to those objects for the user right?


      why is he asking me to create the synonyms....and whr should i create synonyms?
        • 1. Re: synonyms or grant select access
          677340
          you need to grant the privs to the user you want to have insert,update,delete,select. You can create synonyms (create synonym table for owner.table;) so that the USER doesn't have to fully qualify the table when running a command.
          • 2. Re: synonyms or grant select access
            692864
            HI mike thans for your response...

            that means


            1) my dba will grant insert,upd,select,execute privileges to the user
            2) i have to create synonyms by logging in as a user for all those objecs that are created by owner login


            is my understanding right?

            Edited by: user10887630 on Apr 10, 2009 7:24 AM
            • 3. Re: synonyms or grant select access
              677340
              That would depend if you have the create synonym priv. You can issue the grants to the USER from the OWNER and then have the dba create the private synonyms for the USER.
              • 4. Re: synonyms or grant select access
                692864
                i think i have the create synonym privileges. and my dba asked me to create synonym ....if thats the case......

                then

                1) i have to login using <userlogin>
                2) have to create synonyms

                is that right>
                • 5. Re: synonyms or grant select access
                  677340
                  yes, you would from the USER login issue create synonym <table> for OWNER.<table>;
                  • 6. Re: synonyms or grant select access
                    692864
                    DBA_Mike


                    Posts: 92
                    Registered: 12/26/08
                    Re: synonyms or grant select access
                    Posted: Apr 10, 2009 7:18 AM in response to: user10887630 Reply

                    you need to grant the privs to the user you want to have insert,update,delete,select. You can create synonyms (create synonym table for owner.table;) so that the USER doesn't have to fully qualify the table when running a command.



                    when you say create synonym ....... (using which login should we creae it owner or user? )
                    • 7. Re: synonyms or grant select access
                      692864
                      mike......

                      if i use create synonym <table> for OWNER.<table> from a USER LOGIN


                      will the users be able to see the tables once he logs in ?



                      right now .. am unable to see the tables that are created in the owner when i login as a user
                      • 8. Re: synonyms or grant select access
                        677340
                        in that post I was talking about the dba with a dba account running (create synonym USER.<table_name> for OWNER.<table_name>) but if USER has create synonym then the user can create it's own synonym for those objects it has privs on.
                        • 9. Re: synonyms or grant select access
                          677340
                          Yes, if you issue create synonym for the OWNER objects then you should be able to run select * from <table> of any table that you have select privs. on and that you have created a synonym so that you don't have to fully qualify (owner.tab). from the USER login.

                          and just to clarify...you say when you login as a user...you need to be the user that owns the synonym.
                          • 10. Re: synonyms or grant select access
                            692864
                            Mike really sorry to bother you............... am a bit confused here

                            A is owner and B is user

                            i logged in as : A
                            created tables.....t1, t2

                            now i logged in using B

                            i could not find those tables here .......

                            what i want is when i login using B i want to use those tables here like select * from t1 ; insert in to t2 etc

                            what should i do ?

                            plz bear with me am a bit confused here.. thats why asking you again

                            Edited by: user10887630 on Apr 10, 2009 8:05 AM
                            • 11. Re: synonyms or grant select access
                              677340
                              ok..log in as a...run create tables statements...then run grant select, insert, etc to B (do this as user A)
                              then as user B..(assuming you have create synonym priv) you run create synonym tab1 for a.tab1;
                              then as user b run select * from tab1;
                              • 12. Re: synonyms or grant select access
                                692864
                                thank you so much mike i got it now .... thanks a lot

                                final question : can i grant select,insert,delete,update privileges to B for all the tables.. at a time

                                or do i need to use

                                grant select
                                grant insert
                                grant update
                                grant delete

                                for each one of the tables......
                                • 13. Re: synonyms or grant select access
                                  677340
                                  Well, it will be at a time pretty much but that doesn't mean you have to type it out..try this while logged in as user A (and this assumes you want to grant everything for each table to user B)..just sub in your user name for A and B. after this you will need to create synonyms for each object from user B....which you could create a script from user A to run at b such as this:

                                  synonyms:

                                  select 'create synonym '||table_name||' for a.'||table_name||';' from user_tables;

                                  then take the spooled script that the above producted and login as b and run it to create the synonyms

                                  Grants:

                                  FOR x IN (SELECT * FROM user_tables)
                                  LOOP
                                  EXECUTE IMMEDIATE 'GRANT ALL ON A.' || x.table_name || ' TO B';
                                  END LOOP;
                                  • 14. Re: synonyms or grant select access
                                    692864
                                    THANKS A TON FOR BEING WITH ME FROM LAST 30 TO 45 MIN ......THANKS A LOT HAVE A GREAT WEEKEND
                                    1 2 Previous Next