User Security

Show Privileges for a Role
For example, EXECUTE_CATALOG_ROLE:

select privilege, table_name 
  from role_tab_privs 
 where role='EXECUTE_CATALOG_ROLE'
 order by 2;

Lock Down Database to DBAs Only
This is useful for when you are retiring a database and haven't removed it yet. Basically it makes sure the RESTRICTED SESSION is enabled and persists through restarts.

-- For future startups, ENABLE RESTRICTED SESSION
CREATE OR REPLACE TRIGGER RESTRICT_STARTUP
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter system enable restricted session';
END;
/
-- Make sure DB is currently in restrict mode
ALTER SYSTEM ENABLE RESTRICTED SESSION;