Re: User Privligies

"Andrzej7" <ANdrzej7_at_hotmail.com> wrote in message news:6930fcca.0404260843.68836809_at_posting.google.com...
> I have about 100 tables in my DB, all start with PS, like
> PSCOUNTY
> PSBRIDGE
> PSDESIGN
>
> And Privlidgies are set up in groups:
>
> ABC_01_ROLE
> ABC_02_ROLE
> ABC_03_ROLE etc.
>
> The ROLEs can have SELECT, UPDATE, DELETE INSERT (granted to the ROLE)
>
> My DBA is gone this week and I need to check the ROLEs for all tables.
>
> How can I request this info from ORACLE so I can look if all tables
> have right privlegies assign to them?
>
> I would like to see something like:
>
> PSCOUNTY ABC_01_ROLE SELECT
> PSBRIDGE ABC_01_ROLE SELECT DELETE INSERT
> PSBRIDGE ABC_02_ROLE SELECT INSERT
> PSBRIDGE ABC_03_ROLE DELETE
> PSDESIGN ABC_01_ROLW SELECT UPDATE DELETE
>
> or
>
> ABC_01_ROLE PSCOUNTY SELECT
> ABC_01_ROLE PSDESIGN UPDATE SELECT
>
> etc.
>
>
> Andy

Andy,

My suggestion would be:
SELECT

      table_name,
      grantee,
      MAX( DECODE( privilege, 'SELECT', 'SELECT' ) )
         AS select_priv,
      MAX( DECODE( privilege, 'DELETE', 'DELETE' ) )
         AS delete_priv,
      MAX( DECODE( privilege, 'UPDATE', 'UPDATE' ) )
         AS update_priv,
      MAX( DECODE( privilege, 'INSERT', 'INSERT' ) )
         AS insert_priv
   FROM
      dba_tab_privs
   WHERE
         table_name LIKE 'PS%'
      AND
         grantee IN (
            SELECT
                  role
               FROM
                  dba_roles
         )
   GROUP BY
      table_name,
      grantee

This is a form of table pivoting. This query needs to be run by someone with DBA privileges because the base tables for the query are the DBA_TAB_PRIVS (see p.2-134 of "Oracle 9i Database Reference") and DBA_ROLES (see p.2-215 of "Oracle 9i Database Reference").

I have used a subquery on the DBA_ROLES to get the set of valid roles instead of relying on the naming standard that you have. To use the naming standards, replace the WHERE clause with the following: WHERE
      table_name LIKE 'PS%'
   AND
      grantee LIKE 'ROLE%'

The secret to the table pivoting lies in the combination of the GROUP BY clause, and the two functions, MAX and DECODE.

The DECODE function (see p.6-52 of "Oracle 9i SQL Reference") replaces the value retrieved from the PRIVILEGE column of the DBA_TAB_PRIVS view with either the value of the third parameter or a NULL. For example, the first DECODE in the SELECT statement above is equivalent to: IF ( privilege = 'SELECT' ) THEN -- compares first parameter to second parameter

   select_privilege := 'SELECT'; -- sets result as value of third parameter ELSE
   select_privilege := NULL; -- this is the default result for no match END IF; The MAX function (see p.6-95 of "Oracle 9i SQL Reference") works on groups of values and thus requires a GROUP BY clause if you want to analyse subsets of the data. With the aggregate function (see p.6-8 of "Oracle 9i SQL Reference"), NULL values are ignored. For example, the only non-NULL value in the SELECT_PRIV column is 'SELECT'. If, at least, one non-NULL value is found in that column, then the MAX function returns 'SELECT', otherwise it returns NULL.

Douglas Hawthorne Received on Mon May 03 2004 - 04:49:20 CEST