Re: View Granted User Privileges in SQLPLUS?

From: osy45 <member18536_at_dbforums.com>
Date: Fri, 25 Apr 2003 15:55:44 +0000
Message-ID: <2807523.1051286144@dbforums.com>

hope the following helps:

%&o     owner vorgeben
%&ob    object_name vorgeben
%&gr    name, granted to vorgeben

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',
                                 ,'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

/

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',
                                 ,'NO',
                                 1,'YES',
                                 'NO') opt

FROM ALL_OBJECTS A , SYS.OBJAUTH$ B , TABLE_PRIVILEGE_MAP C , SYS.USER$
D

WHERE  D.Name           = 'TUTMS01'     AND
       D.User#          =    B.Grantee# AND
       A.Object_Id      =    B.Obj#     AND
       B.Privilege#     =    C.Privilege

/

• privs Script generieren für usrtuxedo

SELECT 'grant '|| c.name || ' on ' ||A.Owner ||'.' || A.Object_name ||
' to ' || &gr || ' ' ||

                DECODE(B.Option$,NULL,';',
                                 ,';',
                                 1,' with grant option;',
                                 ';')

FROM ALL_OBJECTS A , SYS.OBJAUTH$ B , TABLE_PRIVILEGE_MAP C , SYS.USER$
D

WHERE  D.Name           = 'USRTUXEDO'     AND
       D.User#          =    B.Grantee# AND
       A.Object_Id      =    B.Obj#     AND
       B.Privilege#     =    C.Privilege

/

--
Posted via http://dbforums.com

Received on Fri Apr 25 2003 - 10:55:44 CDT