Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Privileges for ALL_% views vs DBA_% views

    We're trying to decide if we can give up TOAD and use SQL Developer instead. One of the sticking points is that our Analysts aren't able to see object privileges, unless those privileges are granted to PUBLIC.

    We looked into it, and TOAD uses DBA_% views to gather this information (and we've toggled 'on' the setting which elects to use the DBA views over the ALL views). As you can see below, there is no problem displaying this data. However, if these same people use SQL Developer, which uses ALL_% views, no object grants display.

    What privileges do I need to grant to them so that the ALL_ views start working like the DBA_ views:

    Code:
    SQL> SELECT   PRIVILEGE,
      2           GRANTEE,
      3           GRANTABLE,
      4           GRANTOR,
      5           COLUMN_NAME object_name
      6    FROM   all_col_privs
      7   WHERE   table_schema = 'R5UITL' AND TABLE_NAME = 'ACT5'
      8  UNION ALL
      9  SELECT   PRIVILEGE,
     10           GRANTEE,
     11           GRANTABLE,
     12           GRANTOR,
     13           table_NAME object_name
     14    FROM   all_tab_privs
     15   WHERE   table_schema = 'R5UITL' AND TABLE_NAME = 'ACT5';
     
    no rows selected
     
    SQL> SELECT   PRIVILEGE,
      2           GRANTEE,
      3           GRANTABLE,
      4           GRANTOR,
      5           COLUMN_NAME object_name
      6    FROM   dba_col_privs
      7   WHERE   owner = 'R5UITL' AND TABLE_NAME = 'ACT5'
      8  UNION ALL
      9  SELECT   PRIVILEGE,
     10           GRANTEE,
     11           GRANTABLE,
     12           GRANTOR,
     13           table_NAME object_name
     14    FROM   dba_tab_privs
     15   WHERE   owner = 'R5UITL' AND TABLE_NAME = 'ACT5';
     
    PRIVILEGE  GRANTEE      GRANTABLE  GRANTOR  OBJECT_NAME
    ---------- ------------ ---------- -------- ------------
    DELETE     R5UITL_ROLE  NO         R5UITL   ACT5
    INSERT     R5UITL_ROLE  NO         R5UITL   ACT5
    UPDATE     R5UITL_ROLE  NO         R5UITL   ACT5
    SELECT     R5UITL_ROLE  NO         R5UITL   ACT5
    Our Analysts currently have the SELECT_CATALOG_ROLE, and the GRANT ANY OBJECT PRIVILEGE roles.
    --=Chuck

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Anyone have any leads that I could follow in order to chase this one down?
    --=cf

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by chuck_forbes

    What privileges do I need to grant to them so that the ALL_ views start working like the DBA_ views:
    Since the ALL_ views only show the objects accessible by the user querying the views, the minimum privileges I guess would be the SELECT privileges for all tables and columns. But then again, they will only see their own privileges.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I have used both and frankly, I prefer Toad anyway.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    I personally like TOAD, but mgmt likes *free* if the tool is comparable.

    So I tried a couple of things, and I found out that if I grant SELECT rights on a table directly to a user, then they can see that grant in ALL_TAB_PRIVS. If I grant SELECT to a role, and grant that role to a user, then they cannot see that grant in ALL_TAB_PRIVS.

    But what I want these Analysts to be able to see, are the privileges assigned which enable the objects to be accessed from our application. These Analysts aren't directly assigned these privileges.

    Since I can see this information in ALL_TAB_PRIVS, and I have the DBA role granted to me (and the DBA role is just a set of privileges), it just seems like I could grant that privilege to someone else, rather than giving them DBA.

Posting Permissions

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