Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Unanswered: grant problem - bug or feature?

    Hi All,

    I have found a problem in my ORA environment.

    I created a table in the SZEMELY users's schema, and granted select right to PORT user. After that I made a synonym for this table in PORT user's schema. In this point, I can select from this table, and can write package using this table via the synonym, using PORT user.

    After that I switched to SZEMELY, and re-created the same table (with the same name), but didn't granted any rights to PORT. In this situation, the PORT user can select from this table, but the package(I have made before) doesn't work! If I try to compile this, it returns with "Table or view not found" error....

    Any idea, what could be the problem? It seems to me a bug...

    Thanks in advance: Attila
    ----
    Verison: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

    Script:

    --SZEMELY user:
    CREATE Table ma_p1 (a number);
    grant select on MA_P1 to PORT;
    --PORT user:
    create synonym ma_ps for szemely.ma_p1;
    --
    create or replace package ma_p is
    function proba return number;
    PRAGMA RESTRICT_REFERENCES (PROBA, WNDS, WNPS);
    end ma_p;
    --

    create or replace package body ma_p is
    function proba return number is
    i NUMBER;
    begin
    select count(*) into i from ma_ps;
    return (i);
    end;
    end ma_p;
    --

    select * from ma_ps --OK
    SELECT ma_p.proba() from dual --OK
    --SZEMELY user:
    drop table ma_p1
    CREATE Table ma_p1 (a number)
    --PORT has no SELECT right on this table!!!!

    --PORT user:
    select * from ma_ps --OK
    SELECT ma_p.proba() from dual --ERROR!!!!!!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: grant problem - bug or feature?

    That would be a bug, i.e. requires a support call.

    I just went through your steps on 8.1.7.3.0 and did not get the result you get: once the owner had dropped and re-created the table, the other user could no longer select from it, either directly or via the package. Which is how it should be.

  3. #3
    Join Date
    Aug 2001
    Posts
    66

    Re: grant problem - bug or feature?

    What other privileges does PORT have either granted directly or via role?
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  4. #4
    Join Date
    Nov 2003
    Posts
    7

    Re: grant problem - bug or feature?

    PORT has the following rights:

    System privileges:
    -Create any type
    -Execute any type
    -Under any table
    -Unlimited tablespace

    Roles:
    -Connect
    -DBA
    -Resource

    It's OK, that PORT can select from any table (DBA), but I wonder why can't he access this table via package, while he can select from it directly? This is the strange thing...

    Attila

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Be sure you drop the synonym under PORT also ...

    Gregg

  6. #6
    Join Date
    Aug 2001
    Posts
    66
    Roles are not honoured in stored procedures (as AUTHID DEFINER anyway), hence having DBA role you can SELECT from a table in a different schema but to SELECT from the same in a stored procedure you would need a direct grant of either SELECT ANY TABLE (bad idea) or SELECT on the table in question.
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

Posting Permissions

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