simple sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: simple sql

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    Hi

    I am trying to get the privleges for the users for each table in a sql

    I want the output as

    user1 table1 delete insert select update
    user1 table2 delete insert

    I am querying from dba_tab_privs and getting the output as

    user1 table1 delete
    insert
    select
    update

    ie all the privs are appearing on new lines, how do i get it on the same line
    Thanks
    badrinath


  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    I would try something like this :

    select TAB.owner,TAB.table_name,S.P,I.P,D.P,U.P
    from
    (
    select distinct owner,table_name from dba_tab_privs
    ) TAB,
    (
    select owner,table_name,privilege P from dba_tab_privs where privilege = 'SELECT'
    ) S,
    (
    select owner,table_name,privilege P from dba_tab_privs where privilege = 'INSERT'
    ) I,
    (
    select owner,table_name,privilege P from dba_tab_privs where privilege = 'DELETE'
    ) D,
    (
    select owner,table_name,privilege P from dba_tab_privs where privilege = 'UPDATE'
    ) U
    where TAB.owner = S.owner (+)
    and TAB.table_name = S.table_name (+)
    and TAB.owner = I.owner (+)
    and TAB.table_name = I.table_name (+)
    and TAB.owner = D.owner (+)
    and TAB.table_name = D.table_name (+)
    and TAB.owner = U.owner (+)
    and TAB.table_name = U.table_name (+)
    /

    Hope this helps
    Gert

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    A simple set of decodes will work much more efficiently:

    SELECT
    ---GRANTEE,
    ---TABLE_NAME,
    ---MAX(DECODE(PRIVILEGE,'SELECT','SELECT',NULL)) AS SELECTPRIV,
    ---MAX(DECODE(PRIVILEGE,'UPDATE','UPDATE',NULL)) AS UPDATEPRIV,
    ---MAX(DECODE(PRIVILEGE,'DELETE','DELETE',NULL)) AS DELETEPRIV,
    ---MAX(DECODE(PRIVILEGE,'INSERT','INSERT',NULL)) AS INSERTPRIV
    FROM
    ---DBA_TAB_PRIVS
    GROUP BY
    ---GRANTEE,
    ---TABLE_NAME


    - Chris

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