ORA-00942 error when permissions are granted through roles
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ORA-00942 error when permissions are granted through roles

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    160

    ORA-00942 error when permissions are granted through roles

    Dear all,

    I am getting "ORA-00942 for PL/SQL: ORA-00942: table or view does not exist". I check the table exists and the user has required permission on the table through roles. I understand roles are disabled in stored procedures, and permissions need to be granted directly to user for the procedures to work. However, we are required to enforce the permissions being granted to roles instead of direct grant to users. Is there a work around that will help the store procedures to work without direct grant of permissions to the users?

    I have the following grant and procedure:

    grant select on hr.employees to scott;
    grant execute on dbms_output;

    conn scott/scott

    create or replace Procedure get_result
    AUTHID CURRENT_USER AS
    Cursor c_info is
    SELECT LAST_NAME FROM hr.EMPLOYEES;
    begin
    For i in c_info Loop
    dbms_output.put_line('User last name: '|| i.last_name);
    END LOOP;
    end get_result;
    /

    Warning: Procedure created with compilation errors.

    show error
    Errors for PROCEDURE GET_RESULT:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    4/6 PL/SQL: SQL Statement ignored
    4/31 PL/SQL: ORA-00942: table or view does not exist
    8/9 PL/SQL: Statement ignored
    8/51 PLS-00364: loop index variable 'I' use is invalid


    Of course when the "grant select on hr.employees to scott" is executed, the above errors went away. Can anyone advice how do avoid the direct grant? We are required to use roles instead of direct grant.


    Thanks in advance.
    Unna

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    no, you need to grant direct to a user

  3. #3
    Join Date
    Nov 2000
    Posts
    160
    Our requirement is we have to use roles instead of direct grants. I saw in our other databases, we don't have direct grants in place, but roles only. I asked the developers how did they do it; they didn't know.

    Now I need to figure out how do I do that? Any ideas? Thanks!

Posting Permissions

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



Click Here to Expand Forum to Full Width