This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Oct 15, 2012 1:05 PM by Mark Malakanov (user11181920) Go to original post RSS
  • 15. Re: need answer for below question
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    Missing information that is needed includes
    1. HOW is user B trying to execute the proc in schema A? Directly or within a procedure?
    It does not matter. A.EMP will be updated by A.PROC1.
    2. HOW was user B given permission to execute the proc in schema A? Directly or thru a role?
    It does not matter. A.EMP will be updated by A.PROC1.
    Grants have to be given directly if B is trying to execute the proc in schema A within a proc of schema B.
    Question was A.EMP or B.EMP will be updated.
    3. HOW was the proc in schema A defined? Using INVOKER rights or DEFINER rights?
    It does not matter. A.EMP will be attempted to be updated by A.PROC1.
    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?
    It does not matter. A.EMP will be updated by A.PROC1.

    What would matter, it is how A.PROC1 updates EMP, using regular SQL or dynamic SQL. With dynamic SQL it can be possible that B.EMP will be updated.
  • 16. Re: need answer for below question
    rp0428 Guru
    Currently Being Moderated
    >
    Missing information that is needed includes
    
    
    1. HOW is user B trying to execute the proc in schema A? Directly or within a procedure?
    It does not matter. A.EMP will be updated by A.PROC1.
    2. HOW was user B given permission to execute the proc in schema A? Directly or thru a role?
    It does not matter. A.EMP will be updated by A.PROC1.
    Grants have to be given directly if B is trying to execute the proc in schema A within a proc of schema B.
    Question was A.EMP or B.EMP will be updated.
    3. HOW was the proc in schema A defined? Using INVOKER rights or DEFINER rights?
    It does not matter. A.EMP will be attempted to be updated by A.PROC1.
    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?
    It does not matter. A.EMP will be updated by A.PROC1.
    What would matter, it is how A.PROC1 updates EMP, using regular SQL or dynamic SQL. With dynamic SQL it can be possible that B.EMP will be updated.
    Since everything you said is wrong I'm not even going to go over them one by one.

    The procedure in A does not reference the schema for the table
    >
    Procedure logic doesnt contain any schema name like schema.table( A. emp). Per OP
    >
    Clearly you did not perform any tests before you replied since even a simple test would have shown that the answers to the questions I ask are needed in order to know what will happen.
    >
    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.
    >
    Connect as user SYS and execute the following statements.
    create user a identified by a;
    
    grant create session, connect, unlimited tablespace, create procedure to a;
    
    create user b identified by b;
    
    grant create session, connect, unlimited tablespace, create procedure to b;
    
    create table a.test1 (colA number);
    
    insert into a.test1 values (10);
    
    create table b.test1 (colA number);
    
    insert into b.test1 values (20);
    Now connect as user A and execute the following
    create or replace procedure proc1 authid current_user as
    BEGIN
       UPDATE TEST1 SET COLA = COLa + 1;
    END;
    /
     
    grant execute on proc1 to b;
    And now see what happens if you connect as user B and execute the following
    exec a.proc1;
    
    select * from test1;
    
          COLA
    ----------
            21
    
    SQL>
    The B.test1 table now shows 21 when it initially had a value of 20.

    How did it get updated if 'When A.PROC1 is created it is compiled to update A.EMP'?

    I'll leave it as an exercise for you to conduct your own tests of the other questions that I raised: using DEFINER rights, granting priviligess to B using a role, executing the proc as B, executing the proc from a stored procedure that B creates.
  • 17. Re: need answer for below question
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    You are right only in case #3 if the procedure is created with INVOKER rights. And you provided only this as an example.

    But if the procedure is created using DEFINER right, which is default way, all you said (#1,2,4) do not matter - how it is granted, directly or via role, is table access granted or not, and how it is granted.

    Please run this exercise.
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
    With the Partitioning, Data Mining and Real Application Testing options
    
    SQL> create user a identified by a;
    User created.
    
    
    SQL> grant create session, connect, unlimited tablespace, create procedure to a;
    Grant succeeded.
    
    
    SQL> create user b identified by b;
    User created.
    
    
    SQL> grant create session, connect, unlimited tablespace, create procedure to b;
    Grant succeeded.
    
    
    
    SQL> create table a.test1 (colA number);
    Table created.
    
    SQL> insert into a.test1 values (10);
    1 row created.
    
    SQL> create table b.test1 (colA number);
    Table created.
    
    SQL> insert into b.test1 values (20);
    1 row created.
    
    SQL> commit;
    Commit complete.
    
    SQL> conn a/a
    Connected.
    
    SQL> create or replace procedure proc1 as
    BEGIN
       UPDATE TEST1 SET COLA = COLa + 1;
    END;
    /
    Procedure created.
    
    SQL> grant execute on proc1 to b;
    Grant succeeded.
    
    SQL> conn b/b
    Connected.
    
    SQL> exec a.proc1
    PL/SQL procedure successfully completed.
    
    SQL> select * from test1;
    
          COLA
    ----------
            20
    I'll leave it as an exercise for you to conduct your own tests of the other questions that I raised: using DEFINER rights, granting priviligess to B using a role, executing the proc as B, executing the proc from a stored procedure that B creates.
    Have yourself ever conducted these exercises? Please do. And look at the result. You will see that A.TEST1 will be updated no matter what.
  • 18. Re: need answer for below question
    rp0428 Guru
    Currently Being Moderated
    >
    You are right only in case #3 if the procedure is created with INVOKER rights. And you provided only this as an example.
    >
    You made a blanket statement that A.TEST1 will be updated no matter what so all I need to do is provide ONE example to show that your blanket statement is wrong.

    My reply stated all of the information that is needed in order to answer the OPs question.
    >
    But if the procedure is created using DEFINER right, which is default way, all you said (#1,2,4) do not matter - how it is granted, directly or via role, is table access granted or not, and how it is granted.
    >
    Your original reply made a blanket statement. That statement wasn't conditioned on how the procedure was created. That is EXACTLY why I said that one piece of information needed is whether the procedure uses DEFINER or INVOKER authid. So far you have confirmed that my statement is correct.

    And it certainly DOES matter how the execute privilege is granted. Again, if you had conducted simple tests (and yes I have done them) you would discover that if user A grants the execute privilege on PROC2 to B via a role (e.g. USER_B_ROLEL)o then B cannot create a valid, compiled procedure to execute A.PROC2 because that role is disabled within the procedure. Oracle will throw an 'PLS-00201: identifier 'A.PROC2' must be declared' error.

    It is the combination of ALL of the information that I ask OP for that will determine whether the procedure can be called at all and, if it is called, which table will get updated.

    Your original statement is totally wrong
    >
    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.
    >
    It DOES matter which user calls it.
    It DOES matter if that user has their own EMP table
    It DOES matter whether the proce uses DEFINER or INVOKER authid
    A.PROC1 is NOT compile to update A.EMP when no schem prefix is specified - the table to update will depend on the authid of the procedure.
  • 19. Re: need answer for below question
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    I agree I was wrong about invoker rights.

    But about grants and way how a proc is called, it is irrelevant.
    And it certainly DOES matter how the execute privilege is granted.
    are you saying that if execute privilege is granted directly user B will update A.TEST1 and if granted via role it will update B.TEST1 (or vice versa)?
    if user A grants the execute privilege on PROC2 to B via a role (e.g. USER_B_ROLEL)o then B cannot create a valid, compiled procedure to execute A.PROC2 because that role is disabled within the procedure. Oracle will throw an 'PLS-00201: identifier 'A.PROC2' must be declared' error.
    I know this. But it is irrelevant to the OPs question.
    OPs question was "which table will be updated".
    Also A.PROC2 can be called from anonymous block.

    Please show the difference (which table will be updated) that depends on:
    1. how the A.PROC1 procedure is granted
    2. how A.PROC1 is called, in anonymous block or in stored proc.
  • 20. Re: need answer for below question
    rp0428 Guru
    Currently Being Moderated
    >
    are you saying that if execute privilege is granted directly user B will update A.TEST1 and if granted via role it will update B.TEST1 (or vice versa)?
    >
    No - that is not what I am saying.

    The pieces of information I said were needed are used for different things.

    The AUTHID clause will determine which user's privileges are used. If DEFINER privileges are used (user A) Oracle will attempt to resolve the reference to the EMP table by using A.EMP if it exists. If it does not exist but there is a PUBLIC synonym named EMP Oracle will use the table referenced in the public synonym if it has privileges. The B.EMP table would only be referenced using DEFINER privileges

    1. if the A.EMP table does not exist
    2. there is a PUBLIC synonym pointing to the B.EMP table

    We both missed stating that we actually need to also know about any PUBLIC synonyms named EMP that might exist.
    >
    OPs question was "which table will be updated".
    >
    Agreed - but the implication is that one of the tables WILL BE updated. But OP didn't provide any information about how the procedure was being called. And rather than make any assumption I said that we need to know that piece of information. If not called from within a procedure then one of the tables will be updated.
    >
    Please show the difference (which table will be updated) that depends on:
    1. how the A.PROC1 procedure is granted
    2. how A.PROC1 is called, in anonymous block or in stored proc.
    >
    I agree with you - HOW the grant is made or how the call is made won't determine which table, if any, gets updated. The table updated depends only on the AUTHID used and the existence of the tables and/or public synonyms.

    I didn't mean to imply that all of the information I ask for was needed in every case. Some of it, AUTHID, is needed for some scenarios, some (how grant is made) for others and one we didn't mention, public synonym, also comes into play.

    I think we're both heading in the same direction. I was just trying to not make ANY assumtions about things the OP did not provide information about. We mostly stated about the same thing in slightly different ways.
  • 21. Re: need answer for below question
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    We both missed stating that we actually need to also know about any PUBLIC synonyms named EMP that might exist.
    and private synonyms too.
    I think we're both heading in the same direction. I was just trying to not make ANY assumtions about things the OP did not provide information about. We mostly stated about the same thing in slightly different ways.
    Yes :)
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points