Results 1 to 3 of 3

Thread: Rights issue

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Rights issue

    Why this still eludes me, I do not know. I thought I had it down:

    I have granted the SELECT ANY TABLE privilege to a role, and I have granted that role to a user. However, it doesn't seem to be working. But, if I grant it directly to the user, it works:

    Code:
    SQL> connect forbesc@test
    Enter password: *******
    Connected.
    
    SQL> grant select any table to imd_schema_owner_role;
    
    Grant succeeded.
    
    SQL> grant imd_schema_owner_role to filing;
    
    Grant succeeded.
    
    SQL> connect filing@test
    Enter password: ******
    Connected.
    
    SQL> CREATE OR REPLACE VIEW FILING.TEST_V AS
      2  SELECT 
      3   A.RSNRA
      4  FROM
      5   FILING.UR_RATES A,
      6   FILING.UR_FILING B,
      7   INSLIC.UC_COMPANY C
      8  WHERE
      9   B.RSNFI(+) = A.RSNFI
     10   AND C.COA_NMBR(+) = A.COA_NMBR;
    
    View created.
    
    SQL> select count(*) from test_v;
    select count(*) from test_v
                         *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    
    SQL> connect forbesc@test
    Enter password: *******
    Connected.
    
    SQL> grant select any table to filing;
    
    Grant succeeded.
    
    SQL> connect filing@test
    Enter password: ******
    Connected.
    
    SQL> select count(*) from test_v;
    
      COUNT(*)
    ----------
         16076

    Thanks,
    Chuck

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Even as the DBA I can't use the view:

    Code:
    SQL> connect forbesc@test
    Enter password: *******
    Connected.
    
    SQL> select count(*) from filing.test_v;
    select count(*) from filing.test_v
                                *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    and granting select on the view to public is not fixing the problem:

    Code:
    SQL> grant select on filing.test_v to public;
    
    Grant succeeded.
    
    SQL> select count(*) from filing.test_v;
    select count(*) from filing.test_v
                                *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    -Chuck

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    CREATE VIEW - Prerequisites:
    "The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role."

Posting Permissions

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