privileges.sql

/*
Purpose: To Determine the Object Privileges Given to a User or Granted by a User
Date Created: 12/19/97
Author: Krishnakumar Govindan
*/

Accept o prompt "Enter the Owner/Grantor of the Object Else Enter for ALL ....."
Accept ob prompt "Enter the Object Name Else Enter for All ....."
Accept gr prompt "Enter the Grantee Else Enter For ALL ....."

Break on ow on obj on gre

Col ow   for a10 Heading "Obj|Owner"    just left
Col obj  for a30 Heading "Object_Name"  just left
Col obt  for a15 Heading "Object_Type"  just left
Col gre  for a10 heading "Granted|To"   just left
Col priv for a10 heading "Privilege"    just left 
Col opt  for a13 heading "Grant|Option" just left

SELECT          A.Owner         ow      ,
             A.Object_name   obj        ,
                A.Object_type   obt     ,
                D.Name          gre     ,
             C.Name             priv    ,
                DECODE(B.Option$,NULL,'NO',
                                 0,'NO',
                                 1,'YES',
                                 'NO') opt
FROM   ALL_OBJECTS A , SYS.OBJAUTH$ B , TABLE_PRIVILEGE_MAP C , SYS.USER$ D
WHERE  A.Owner          LIKE '%&o'      AND
       A.Object_Name    LIKE '%&ob'     AND
       D.Name           LIKE '%&gr'     AND
       D.User#          =    B.Grantee# AND
       A.Object_Id      =    B.Obj#     AND
       B.Privilege#     =    C.Privilege 
/

/*
Purpose: To Determine the System Privileges Belonging to a User 
Date Created: 12/19/97
Author: Krishnakumar Govindan
*/

Accept gr prompt "Enter the Grantee ....."

Break on ow on obj on gre

Col gre for a10 heading "Granted|To" just left
Col priv for a30 heading "Privilege" just left 
Col opt for a13 heading "Admin|Option" just left

SELECT          D.Name  gre     ,
                C.Name  priv    ,
                DECODE(B.Option$,NULL,'NO',
                                 0,'NO',
                                 1,'YES',
                                 'NO') opt
FROM   SYS.SYSAUTH$ B , SYSTEM_PRIVILEGE_MAP C , SYS.USER$ D
WHERE  D.Name           LIKE '%&gr'    AND
       D.User#          =    B.Grantee# AND
       B.Privilege#     =    C.Privilege 
/