Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Exclamation Unanswered: Stored Procedure - A Wierd Discovery?

    Hi All,

    To get you up to speed with what I am about to ask here is a little background.
    I have two database users 'A' and 'B' both the users have a table Employee with exactly the same schema. Now if a user logged in as 'A' make an unnamed procedure i.e.
    Code:
            declare
             
             type Rec is record 
               (name B.Employee.name%type, 
                age B.Employee.age%type, 
                salary B.Employee.salayr%type); 
    
              procedure......
       
               begin
                { call procedure}
              end;
    things work well and fine but as soon as 'A' converts the same to a named package i.e.
    Code:
       create or replace package Record is
           type Rec is record 
               (name B.Employee.name%type, 
                age B.Employee.age%type, 
                salary B.Employee.salayr%type); 
            procedure....;
       end Record;
    the compiler throws an error PLS-00201: identifier 'B.Employee' must be declared.

    Could some one please let me know if this is a valid error or am I doing something wrong?

    Thanks for your help.

    Sumit

  2. #2
    Join Date
    Jan 2004
    Posts
    9

    Keeping it alive

    Keeping it alive

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I believe that when accessing objects through a package, the invoker must be explicitly granted rights to the underlying tables. I remember seeing similar posts where rights had been granted through a role and similar issues occurred.

    Check the Oracle docs for the specifics (invoker rights, definer rights, rights granted through a role etc).

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    Join Date
    Jan 2004
    Posts
    9

    I still didnt get it...

    Im sorry but I didnt understand your reply...what is the difference in writing an un named package and a named pakage in terms of security or permission of access?

    Thank You for your help..

    Sumit

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Sumit,

    Try this, it shows that the select permission granted through a role does not work for packages. The select permission must be explicitly granted.

    User "Test_user" owns a table.
    The role "test_role" has rights to select on the table
    User "Test_other" has been granted "test_role".
    "Test_other" can select directly from the table, but not through a package which it has created.
    "Test_user" then grants select on "Test_Table"
    "Test_other's" package now compiles fine.

    Hth
    Bill

    SQL> connect sys/sys@dev.asd
    Connected.

    SQL> create role test_role;
    Role created.

    SQL> create user test_user identified by test_user;
    User created.

    SQL> grant create table to test_user;
    Grant succeeded.

    SQL> grant connect to test_user;
    Grant succeeded.

    SQL> alter user test_user default tablespace users;
    User altered.

    SQL> alter user test_user quota unlimited on users;
    User altered.

    SQL> connect test_user/test_user@dev.asd
    Connected.

    SQL> create table test_table (col1 varchar2(10));
    Table created.

    SQL> grant select on test_table to test_role;
    Grant succeeded.

    SQL> connect sys/sys@dev.asd
    Connected.

    SQL> create user test_other identified by test_other;
    User created.

    SQL> grant connect to test_other;
    Grant succeeded.

    SQL> grant test_role to test_other;
    Grant succeeded.

    SQL> grant create procedure to test_other;
    Grant succeeded.

    SQL> connect test_other/test_other@dev.asd
    Connected.

    SQL> select * from test_user.test_table;
    no rows selected

    SQL> edit
    Wrote file afiedt.buf
    1 CREATE OR REPLACE PACKAGE TEST_PACKAGE IS
    2 PROCEDURE DoSomething;
    3* END TEST_PACKAGE;
    SQL> /
    Package created.

    SQL> edit
    Wrote file afiedt.buf
    1 CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE AS
    2 PROCEDURE DoSomething IS
    3 xtemp number(10);
    4 BEGIN
    5 SELECT count(*)
    6 INTO xtemp
    7 FROM TEST_USER.TEST_TABLE;
    8 END DoSomething;
    9* END TEST_PACKAGE;
    SQL> /
    Warning: Package Body created with compilation errors.

    SQL> show errors
    Errors for PACKAGE BODY TEST_PACKAGE:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/4 PL/SQL: SQL Statement ignored
    7/9 PLS-00201: identifier 'TEST_USER.TEST_TABLE' must be declared

    SQL> connect test_user/test_user@dev.asd
    Connected.

    SQL> grant select on test_table to test_other;
    Grant succeeded.

    SQL> connect test_other/test_other@dev.asd
    Connected.

    SQL> alter package test_package compile body;
    Package body altered.

    SQL> show errors
    No errors.

    SQL>
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  6. #6
    Join Date
    Jan 2004
    Posts
    9

    Smile Thank You

    Thank You..that worked...but I am still surprised

    Regards,
    Sumit

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Sumit,

    I haven't read through in detail the specifics of this, but *my un-educated* opinion is that it stems from a conceptual principle in which a schema owning a table would also contain the necessary packages for managing such a table.

    The package would be declared AUTHID DEFINER as opposed to AUTHID INVOKER.

    This means the package executes in the security context of the owner of the package, as opposed to the invoker of the package.

    This way, you could grant execute permissions on a different users package, the main user has fairly constrained execute priviledges on required packages in other schemas. This means each individual user doesn't have too many priviledges scattered across the database, as the required privileges can propogate up through various packages and relevant schemas as required.

    Viz, 'typical model'
    User A needs to work on User B's table. User B's table has relationships to User C's table.

    You would need to grant A rights on B and C.

    Definer rights model
    User A has execute on B.procedure.
    User B has execute on C.procedure
    Procedures called in definer rights.

    The complexities and locations of C (or any other relationships) are completely hidden from A, who also doesn't actually need to know that C even exists.

    As I say, these thoughts are just me rationalising what I've understood of it, and by no means based on actual technical reading. I guess that during my sleepless nights I'll figure out exactly how this fits in with the rest of the security principles :-)

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •