👥 Users, Roles & Privileges

1-- all users, with account status, expiry, profile, etc.
2SELECT username, profile, account_status, expiry_date, lock_date
3FROM dba_users WHERE oracle_maintained = 'N';
4
5CREATE USER my_user IDENTIFIED BY my_password;   -- create a user.
6DROP USER my_user;                              -- drop a user.
7DROP USER my_user CASCADE;                      -- drop a user and all its tables.

1SELECT * FROM dba_sys_privs;                               -- all possible privileges.
2SELECT * FROM dba_sys_privs WHERE grantee = 'MY_USER';     -- one user's privileges.
3
4GRANT create session, alter session, drop any index TO my_user;
5REVOKE alter session FROM my_user;

 1CREATE ROLE my_role;      -- create a role.
 2GRANT create session, alter session, drop tablespace, delete any table TO my_role;  -- grant to a role.
 3GRANT my_role TO my_user, hr;   -- grant a role to users.
 4REVOKE alter session FROM my_role;
 5
 6SELECT * FROM dba_roles;
 7SELECT * FROM dba_role_privs WHERE grantee = 'MY_USER';   -- roles of one user.
 8SELECT grantee, granted_role, admin_option, default_role FROM dba_role_privs ORDER BY 1,2;
 9SELECT * FROM dba_sys_privs WHERE grantee = 'MY_ROLE';
10SELECT * FROM dba_tab_privs WHERE grantee = 'MY_ROLE';

1SELECT * FROM dba_profiles;                              -- all profiles.
2SELECT * FROM dba_profiles WHERE profile = 'MY_PROFILE'; -- one profile's limits.
3
4CREATE PROFILE my_profile LIMIT idle_time 15 connect_time 20 failed_login_attempts 50;
5ALTER USER my_user PROFILE my_profile;

1ALTER USER my_user IDENTIFIED BY new_password;   -- change the password.
2ALTER USER my_user ACCOUNT UNLOCK;               -- unlock a user.

1ALTER USER sys IDENTIFIED BY '<password>';
2ALTER USER system IDENTIFIED BY '<password>';

1cd $ORACLE_HOME/dbs
2orapwd file=orapwORCL password=<password> format=12 force=y

1SHOW PARAMETER remote_login_passwordfile;   -- NONE (no remote sysdba) / EXCLUSIVE

1vi $ORACLE_HOME/network/admin/tnsnames.ora
2sqlplus sys@orcl as sysdba

1ALTER SYSTEM ENABLE RESTRICTED SESSION;
2ALTER SYSTEM DISABLE RESTRICTED SESSION;

1-- Sessions and their current status
2SELECT sid, serial#, username, status, event FROM v$session WHERE type = 'USER';

1-- Which object is locked, by which session (lmode/request = lock mode)
2SELECT o.object_name, lo.session_id, lo.type, lo.lmode, lo.request
3FROM v$locked_object lo JOIN dba_objects o ON lo.object_id = o.object_id;

 1SELECT lpad(' ', 2*level) || granted_role "User, roles and privileges"
 2FROM (
 3  SELECT NULL grantee, username granted_role FROM dba_users WHERE username = 'MY_USER'
 4  UNION
 5  SELECT grantee, granted_role FROM dba_role_privs
 6  UNION
 7  SELECT grantee, privilege FROM dba_sys_privs
 8)
 9START WITH grantee IS NULL
10CONNECT BY grantee = PRIOR granted_role;