1 2 Previous Next 21 Replies Latest reply: Oct 15, 2012 3:05 PM by Mark Malakanov (user11181920) RSS

    need answer for below question

    945922
      Hi I have data base and two schemas A and B
      A schema have procedure proc1 contain logic to update table emp

      and B schema have emp table.

      Schema A given permision to Schema B for execute proc1 in Schema B

      after execution porc1 in schema B which schema emp table get updates. and why?
        • 1. Re: need answer for below question
          ranit B
          >
          B schema have emp table.
          >
          I guess only schema- B has got "Emp" table.

          But i didn't get your last question clearly.
          >
          which schema emp table get updates.
          >

          Please clarify.
          Ranit B.
          • 2. Re: need answer for below question
            yoonas
            Hi,

            Hope this will answer your query

            http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#i18574

            Regards
            Yoonas
            • 3. Re: need answer for below question
              945922
              procedure Proc1 contain loginc of update table emp. proc1 owner is schema A

              emp table present in both schemas A and B

              Schema A had given execute permison to Schema B for proc1

              If schema B execute proc1 .. which schema emp table get updates..
              • 4. Re: need answer for below question
                Osama_Mustafa
                First you have to check your procedure if it already determine schema name inside if not it will be locally .

                for example
                in your procedure :
                update b.emp set ..... --it will be on schema B
                update A.emp set ..... --it will be on schema A
                if nothing has been determine it will be locally on schema B
                • 5. Re: need answer for below question
                  ranit B
                  There shouldn't be any confusion if you have used Schema_A.emp in the update statement of Proc-1.

                  It'll update table 'emp' table of schema A.
                  Else table of other schema B.
                  • 6. Re: need answer for below question
                    945922
                    Procedure logic doesnt contain any schema name like schema.table( A. emp)..

                    if schema B execute then emp table of schema B updates? are you sure ?
                    • 7. Re: need answer for below question
                      945922
                      let say schema B does nt contain emp table .

                      and proc1 does nt logic update emp table like schemaA.emp

                      like

                      proc1
                      begin
                      update emp set...

                      end;

                      now Schema B executing proc1 .. what happens is it show error or emp of schema A is updates?
                      • 9. Re: need answer for below question
                        Osama_Mustafa
                        942919 wrote:
                        let say schema B does nt contain emp table .

                        and proc1 does nt logic update emp table like schemaA.emp

                        like

                        proc1
                        begin
                        update emp set...

                        end;

                        now Schema B executing proc1 .. what happens is it show error or emp of schema A is updates?
                        it  will generate error , you should create synonym which related to schema A so update will be on A
                        • 10. Re: need answer for below question
                          yoonas
                          Hi,

                          It will update emp of A .

                          see the link below , i have posted this before

                          http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#i18574

                          Regards
                          Yoonas
                          • 11. Re: need answer for below question
                            ranit B
                            942919 wrote:
                            let say schema B does nt contain emp table .

                            and proc1 does nt logic update emp table like schemaA.emp

                            like

                            proc1
                            begin
                            update emp set...

                            end;

                            now Schema B executing proc1 .. what happens is it show error or emp of schema A is updates?
                            Yes. Of course it'll throw error.

                            Suppose, it doesn't throw this error, some other schema(say Schema 'VIRUS') can easily modify this table and corrupt the data, right?
                            So, as a best practice always use SCHEMA.Object_name to avoid any sort of confusions and get secured.

                            HTH
                            Ranit B.
                            • 12. Re: need answer for below question
                              EdStevens
                              942919 wrote:
                              Hi I have data base and two schemas A and B
                              A schema have procedure proc1 contain logic to update table emp

                              and B schema have emp table.

                              Schema A given permision to Schema B for execute proc1 in Schema B

                              after execution porc1 in schema B which schema emp table get updates. and why?
                              In less time than this thread has been running, you could have empirically discovered the answer for you self by setting up a very simple test case ....
                              • 13. Re: need answer for below question
                                rp0428
                                >
                                Hi I have data base and two schemas A and B
                                A schema have procedure proc1 contain logic to update table emp

                                and B schema have emp table.

                                Schema A given permision to Schema B for execute proc1 in Schema B

                                after execution porc1 in schema B which schema emp table get updates. and why?
                                >
                                Tell us what answer you gave when you were asked this question. This seems to be either an interview question or a test question from a class.

                                Whatever the source of the question your answer should have been that there is not enough information to know for sure what the result will be or why. Missing information that is needed includes

                                1. HOW is user B trying to execute the proc in schema A? Directly or within a procedure?
                                2. HOW was user B given permission to execute the proc in schema A? Directly or thru a role?

                                Grants have to be given directly if B is trying to execute the proc in schema A within a proc of schema B.

                                3. HOW was the proc in schema A defined? Using INVOKER rights or DEFINER rights?

                                4. Was user B given any privileges to the EMP table in schema A? If so was the privilege granted directly or through a role?

                                Without the above information there is no way to know for sure if an exception will be raised or, if no exception is raised, which table will be accessed.
                                • 14. Re: need answer for below question
                                  Mark Malakanov (user11181920)
                                  A.EMP will be updated, it does not matter which user calls it and if it has or does not have its EMP table.
                                  When A.PROC1 is created it is compiled to update A.EMP.
                                  1 2 Previous Next