What can do a user (where can he do what ;-) ??)

From: Christophe <chocman_at_libertysurf.fr>
Date: Thu, 6 May 2004 10:32:34 +0200
Message-ID: <MPG.1b041689a6e3b3c5989696@news.oleane.fr>

Hello,

  I've wrote a query to know what privilege (with nature and type) a user have like that :
SELECT 'DIRECT' AS "Type",'SYSTEME' AS "Nature",privilege as "Privilege" FROM dba_sys_privs
WHERE grantee=upper('&usr')
UNION
SELECT 'ROLE','SYSTEME',grantee||' : '||privilege FROM dba_sys_privs
WHERE grantee IN

	(SELECT granted_role 
	FROM dba_role_privs 
	WHERE grantee=upper('&usr'))

UNION
SELECT 'DIRECT','OBJET',privilege||' sur '||table_name||' de '||owner FROM dba_tab_privs
WHERE grantee=upper('&usr')
UNION
SELECT 'ROLE','OBJET',a.granted_role||' : '||b.privilege|| ' sur '||table_name||' de '||owner
FROM dba_role_privs a,dba_tab_privs b
WHERE a.grantee=upper('&usr')

        AND b.grantee=a.granted_role;

I'd like to add one info : the object (ie, tableSpace.tabes) that the user own or on witch he can do anything

I'm a bit lost coz I don't know where to find info on what he can do somewhere else than in his on schema (=TableSpace)

Thank for you help. Received on Thu May 06 2004 - 03:32:34 CDT