-- PORTFOLOIO ¤ WHITE PAPERS ¤ HOME PAGE


-- THIS VIEW CAN BE USED TO FIND ALL USERS WHO HAVE THE CREATE SESSION SYSTEM PRIVILEGE.
-- I NEED TO SEPERATE THE USERS WHO CAN CONNECT FROM THOSE WHO CANNOT. THIS IS BECAUSE
-- WE PLAN TO DROP ALL USERS WHO CANNOT CREATE A SESSION AND WHO HAVE NO OBJECTS.
-- FINDING USERS WHO CANNOT CREATE A SESSION IS EASY, BUT THE QUERY CAN TAKE A FEW 
-- MINUTES TO FINISH. THAT QUERY IS:
--
-- SELECT username
-- FROM dba_users
-- WHERE username NOT IN (select distinct grantee from granted_create_session);
--

CREATE OR REPLACE FORCE VIEW SYSTEM.GRANTED_CREATE_SESSION
/* ©2004 by Edward Stoever,  */
(GRANTEE, GRANTED_METHOD)
AS 
SELECT DISTINCT grantee AS grantee,
                'GRANTED CREATE SESSION THROUGH ROLE' AS granted_method
           FROM dba_role_privs
          WHERE granted_role IN (
                   SELECT grantee
                     FROM dba_sys_privs
                    WHERE PRIVILEGE = 'CREATE SESSION'
                      AND grantee IN (SELECT ROLE
                                        FROM dba_roles))
UNION ALL
SELECT DISTINCT grantee, 'GRANTED CREATE SESSION DIRECTLY'
           FROM dba_sys_privs
          WHERE PRIVILEGE = 'CREATE SESSION'
            AND grantee NOT IN (SELECT ROLE
                                  FROM dba_roles)
WITH READ ONLY;

COMMENT ON TABLE SYSTEM.GRANTED_CREATE_SESSION IS 'View created by Edward Stoever on Aug 18, 2004 to improve Oracle Security';