10 Replies Latest reply: Feb 13, 2013 12:45 AM by user10366531 RSS

    Replicate user

    user10366531
      Hi ,

      I Have one oracle user ABC which is having 10 Tables.

      Now i want to create replica of this user XyZ which can access only 5 tables from . This user having All access.

      Example :

      Any DML changes made in XYZ these changes should be reflected in ABC user. and vice a versa.

      How can i achive this in ORACLE DB.
        • 1. Re: Replicate user
          Karan
          Oracle streams http://docs.oracle.com/cd/B19306_01/server.102/b14228/config_simple.htm#STREP056 in your case source and target will be same database
          • 2. Re: Replicate user
            user10366531
            Is this possible via oracle security ??
            • 3. Re: Replicate user
              Osama_Mustafa
              user10366531 wrote:
              Is this possible via oracle security ??
              Via Oracle Security How you can do this , Oracle Security Is products used to Secure your database and encrypt it, for replication solutions you have more than one :
              -Oracle Stream
              -Materialized view
              -Golden Gate
              • 4. Re: Replicate user
                EdStevens
                user10366531 wrote:
                Hi ,

                I Have one oracle user ABC which is having 10 Tables.

                Now i want to create replica of this user XyZ which can access only 5 tables from . This user having All access.

                Example :

                Any DML changes made in XYZ these changes should be reflected in ABC user. and vice a versa.

                How can i achive this in ORACLE DB.
                "Any DML changes made in XYZ these changes should be reflected in ABC user. and vice a versa. "


                This sounds very, very suspicious. What is the business requirement this is supposed to meet?

                Why not just grant XYZ the appropriate access to ABCs tables, perhaps via views? Duplicating the same data into two different schemas is a huge red flag.

                Edited by: EdStevens on Jan 31, 2013 6:38 AM
                • 5. Re: Replicate user
                  user10366531
                  Yes you are right but here it will not duplicate data.
                  You can consider A user, Having One Table .(Account)
                  Now user B want to access table A.

                  I am providing grant select A.table to B;

                  Now COnn B/B
                  Select * from A.Account ;
                  Above statement will work fine.

                  --2) I can create synonym in User B and i can access this from B user.

                  In user B
                  Create synonym ACCOUNT For A.ACCOUNT;
                  Select * from Account.

                  This will work fine too.
                  Is there any other way rather then above two. For access tables of another user.

                  Edited by: user10366531 on Jan 31, 2013 9:47 PM
                  • 6. Re: Replicate user
                    Osama_Mustafa
                    Is there any other way rather then above two. For access tables of another user.
                    which way you are looking for ?

                    3 simple way :
                    -Grant
                    -Synonym
                    -View

                    Choose one
                    • 7. Re: Replicate user
                      JohnWatson
                      4th way:
                      alter session set current_schema
                      • 8. Re: Replicate user
                        JohnWatson
                        the 5th (and cleverest) way is proxy authentication:
                        drop user jon cascade;
                        drop user jim cascade;
                        
                        grant connect to jon identified by jon;
                        grant connect to jim identified by jim;
                        alter user jon grant connect through jim;
                        
                        conn jim[jon]/jim
                        sho user
                        • 9. Re: Replicate user
                          EdStevens
                          user10366531 wrote:
                          Yes you are right but here it will not duplicate data.
                          You can consider A user, Having One Table .(Account)
                          Now user B want to access table A.

                          I am providing grant select A.table to B;

                          Now COnn B/B
                          Select * from A.Account ;
                          Above statement will work fine.

                          --2) I can create synonym in User B and i can access this from B user.

                          In user B
                          Create synonym ACCOUNT For A.ACCOUNT;
                          Select * from Account.

                          This will work fine too.
                          Is there any other way rather then above two. For access tables of another user.
                          What you just described is the standard method of giving one user access to another schema's tables. What's wrong with it that you want "another way"?

                          Edited by: user10366531 on Jan 31, 2013 9:47 PM
                          • 10. Re: Replicate user
                            user10366531
                            Its is resolved