Oracle Create Session Role and Kicking Everybody Off

Do not use the default Oracle role, Connect.  Use of that role is deprecated.  Instead, create a role to provide valid users with the Create Session privilege.  (Please refer to my book, “Expert Oracle and Java Security”.)  Do not grant the Create Session privilege directly to users; but rather, grant them your role.  Here is an example:

create role create_session_role not identified;
grant create session to create_session_role;
create user username identified by userpassword container=CURRENT;
grant create_session_role to username;

Note that the qualifier “container=CURRENT” is for Pluggable Database (PDB) instances in Oracle 12c.

You should ask, “besides adhering to a philosophy or rule of standardization, why should I use a role for the Create Session privilege?  After all, all standard users need to be able to connect.”  In this case, our role is less for providing a standard grant for users to connect than it is for providing a mechanism to remove that ability without deleting the users (or shutting down the listener or database.)  Basically, the create_session_role role provides us a mechanism for kicking everyone off an active Oracle instance.

Say, for example, we have a hundred user accounts, and all have been granted the create_session_role role.  At any time, twenty of those users may be connected.  Now, for some security or administrative reason, we need to stop all user activity without shutting down the database.  First we need to assure that no one else can connect, and that the current users cannot reconnect.  This is the easy part.  We simple revoke the Create Session privilege from create_session_role.

revoke create session from create_session_role;

This does not also have the effect of breaking the current sessions.  Those have already been “created”; and so for active sessions, the Create Session privilege has already been used, and is no longer needed.  For active sessions, we need to manually kill them to stop their activity.  This Anonymous PL/SQL Block will kill all active sessions, except the current session in which it is run.

declare
    pragma autonomous_transaction;
    m_sid v$session.SID%TYPE;
    cursor session_cur is
        select serial#, sid from sys.v$session
        where type='USER' and not sid = m_sid;
    session_rec session_cur%ROWTYPE;
begin
    -- Oracle will not let you kill your own current, active session
    m_sid := SYS_CONTEXT( 'USERENV', 'SID' );
    open session_cur;
    loop
        fetch session_cur into session_rec;
        exit when session_cur%NOTFOUND;
        dbms_output.put_line( 'Killing: ' ||
            session_rec.SID || ', ' || session_rec.serial# );
        execute immediate 'ALTER SYSTEM KILL SESSION ''' ||
            session_rec.SID || ', ' || session_rec.serial# || '''';
    end loop;
    close session_cur;
end;

In this block, we create a cursor (session_cur) of active sessions, selected from sys.v$session; so this needs to be run by an account with SYS, SYSTEM or DBA level privileges.  We only care about USER sessions.  Also, we filter out the current session by Session ID (SID).  We find our current SID from the USERENV environment of the SYS_CONTEXT context.

We loop through our cursor, getting each record (session_rec) while there are more to find.  We print out a line to DBMS_OUTPUT for each session we are killing.  And we call EXECUTE IMMEDIATE, passing the ALTER SYSTEM command syntax required to kill each session.

So everybody is kicked off the database, except the current user.  And no one may connect / reconnect.  The current user may grant Create Session to any additional user needed for troubleshooting or research on the current situation.  After the situation is remedied, and normal operations are restored, users can be permitted to connect once again with a single grant:

grant create session to create_session_role;

Now, we are very glad to have a role to distribute this privilege to all users.