Users Management in Oracle

=================================
General
=================================
Create and Drop User
Handle Locked users
Login as user, without knowing his password
Find out user open sessions.
Roles and Permissions
USERENV
"ORA-01031 insufficient privileges" Error in PL/SQL
Why I do not see all existing tables in ALL_TABLES?

=================================
Create and Drop User
=================================
Create User
CREATE USER my_user IDENTIFIED BY my_password;

Drop User
DROP USER my_user CASCADE;

How to create admin_user

CREATE USER <user> IDENTIFIED BY <password>
GRANT CONNECT ,RESOURCE, DBA to USER;

Reset User Password
ALTER USER my_user IDENTIFIED BY my_password;

Change user default tablespace

ALTER USER my_user DEFAULT TABLESPACE WORKAREA;

Create User with DBA Permissions
sqlplus / as sysdba;
CREATE USER DBA_USER IDENTIFIED BY DBA_USER;
GRANT DBA TO DBA_USER;

=================================
Handle Locked users
=================================
Get a list of Locked users
SELECT username, account_status, lock_date, expiry_date
  FROM DBA_USERS
 WHERE account_status <> 'OPEN'
ORDER BY username;

Unlock User
ALTER USER my_user ACCOUNT UNLOCK;

=================================
Handle case of expired user password

=================================

ALTER USER GSMUSER ACCOUNT UNLOCK;
ALTER PROFILE GSM_PROF limit PASSWORD_LIFE_TIME  unlimited;
ALTER USER GSMUSER IDENTIFIED BY <old password>;

If <old password> is unknown, How to get the old password:

SQL> set long 1000000

SQL> set linesize 32000

SQL> set trimspool on

SQL> set heading off

SQL> set pages 0

SQL> set newpage none

SQL> SELECT DBMS_METADATA.get_ddl ('USER', 'GSMUSER') FROM DUAL;

   CREATE USER "GSMUSER" IDENTIFIED BY VALUES 'S:FC78E546A7

810E216A6D68412370E507654B9982165459479A

6DE750AE92;T:5C5A96583F8211642A912F48A91

08BB90F3B0DA50173744E1B0CA6E4BB3ECBD49FC

38AD9E1B3BF775AB3D09E7AF6A6567E2EECB0C78

5EB40F0AB117F5AF364468C337C94F2F68C5E9CF.......

ALTER USER GSMUSER IDENTIFIED BY VALUES 'S:FC78E546A7810E216A6D68412370E507654B9982165459479A6DE750AE92;T:5C5A96583F8211642A912F48A9108BB90F3B0DA50173744E1B0CA6E4BB3ECBD49FC38AD9E1B3BF775AB3D09E7AF6A6567E2EECB0C785EB40F0AB117F5AF364468C337C94F2F68C5E9CF664CD348A528D';

=================================

Login as user, without knowing his password
=================================
Step1. SELECT PASSWORD FROM DBA_USERS WHERE username = 'SOME_USER'
      It would be something like '42DSDANOSDNQLC9'

Step2. ALTER USER SOME_USER IDENTIFIED BY SOME_PASSWORD;
     Now you can login.

Step3. Revert back the original password by:
     ALTER USER SOME_USER IDENTIFIED BY values '42DSDANOSDNQLC9';


=================================
Login as user, without knowing his password Option 2
=================================

conn / as sysdba

ALTER USER TEST_USER GRANT CONNECT THROUGH system;

User altered.

SQL > conn system[TEST_USER]

Enter password:< Give password for system>

SQL >show user

USER is "TEST_USER"

SQL > create table MY_TABLE as select * from emp;

Table created.

SQL > conn / as sysdba

connected

SQL > SELECT owner FROM DBA_TABLES WHERE table_name='MY_TABLE';

OWNER

---------

TEST_USER

=================================
Find out user open sessions.
=================================
SELECT 'ALTER SYSTEM KILL SESSION '''||v_session.sid||','||v_session.serial#||''' IMMEDIATE;' AS "Kill Command",
       v_session.sid,
       v_session.serial#,
       v_process.spid,
       v_session.username,
       v_session.program
FROM   v$session v_session,
       v$process v_process
WHERE  v_process.addr = v_session.paddr
  AND  v_session.username = 'MY_USER';

=================================
Roles and Permissions
=================================
SELECT * FROM DBA/USER_ROLE_PRIVS WHERE GRANTEE = 'SAMPLE';
SELECT * FROM DBA/USER_TAB_PRIVS  WHERE GRANTEE = 'SAMPLE';
SELECT * FROM DBA/USER_SYS_PRIVS  WHERE GRANTEE = 'SAMPLE';

Get user Roles and Permissions:

set long 100000

set head off

SELECT DBMS_METADATA.GET_DDL('USER','MY_USER') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'MY_USER') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'MY_USER') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'MY_USER') FROM dual;

First check what user has:
DBA_SYS_PRIVS - Lists users and their System Privileges
DBA_ROLE_PRIVS -   Lists users and their roles.

Then check what that role means
ROLE_TAB_PRIVS - Lists Role, Owner and Object, and the Privilege on that Object
ROLE_ROLE_PRIVS - Lists Roles which were granted to roles
ROLE_SYS_PRIVS - Lists System privileges that were granted to roles

SQLs to check current user permissions
SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE IN (SELECT granted_role from USER_ROLE_PRIVS);

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE IN (SELECT granted_role from USER_ROLE_PRIVS);

Using DBMS_METADATA

set long 100000

set head off

set linesize 120

set pagesize 0
col CREATE_USER FORMAT A120 WORD_WRAP

col USER_ROLES FORMAT A120 WORD_WRAP

col USER_SYSTEM_GRANTS FORMAT A120 WORD_WRAP

col USER_OBJECT_GRANTS FORMAT A120 WORD_WRAP

SELECT DBMS_METADATA.GET_DDL('USER','MY_USER') AS CREATE_USER 
FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'MY_USER') AS USER_ROLES FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'MY_USER') AS USER_SYSTEM_GRANTS FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'MY_USER') AS USER_OBJECT_GRANTS FROM DUAL;

Check PL/SQL permissions.
Permissions to execute a PL/SQL code are granted directly to a user, and not via role.
SELECT * FROM USER_TAB_PRIVS WHERE privilege = 'EXECUTE';

Oracle Reference for Privileges

Built-in roles
CONNECT - Built-in Role with permissions to CREATE SESSION privilege
RESOURCE - Built-in Role with permissions to 
CREATE CLUSTER, INDEXTYPE, OPERATOR, PROCEDURE, CREATE SEQUENCE, TABLE, TRIGGER, TYPE.

=================================
Users with SYSDBA and SYSOPER Permissions
=================================
V$PWFILE_USERS view lists users who have SYSDBA or SYSOPER privilege.

SELECT * FROM V$PWFILE_USERS;

USERNAME                       SYSDBA SYSOPER SYSASM 
------------------------------ ------ ------- ------ 
SYS                            TRUE   TRUE    FALSE 

SHDBA                          TRUE   FALSE   FALSE


=================================
Create user with SELECT only permission
=================================
CREATE USER QUERY IDENTIFIED BY QUERY;
GRANT CONNECT, RESOURCE TO QUERY;
GRANT SELECT_CATALOG_ROLE to QUERY;

CREATE ROLE SELECTOR;

spool grant_select_to_selector.sql
set pagesize 0
set heading off
set verify off
SELECT 'GRANT SELECT ON '||table_name||' TO SELECTOR;' FROM USER_TABLES;
SELECT 'GRANT SELECT ON '||table_name||' TO SELECTOR;' FROM USER_SNAPSHOTS;
spool off

@grant_select_to_selector.sql


GRANT SELECTOR TO QUERY;

=================================
USERENV
=================================

How to know the current schema?
SELECT SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA' ) FROM DUAL;

How to know the current SID?
SELECT SYS_CONTEXT('USERENV','INSTANCE_NAME') FROM DUAL; 

=======================================

"ORA-01031 insufficient privileges" Error in PL/SQL
=======================================

Consider this scenario: 
USER A is running a DDL statement, for example:
CREATE SYNONYM MY_USERS FOR ALL_USERS;

When running from sqlplus window, All is OK.

Now the same user is running the same DDL statement, on same DB, from PL/SQL block. 
The Package is owned by User A.
This time the execution ends with error:
ORA-01031 insufficient privileges
What might be wrong?

The Reason: Grants to PL/SQL procedures cannot be granted via Role.
User A got to have explicit permission.
In this case: 

GRANT CREATE SYNONYM TO USER_A; 

What is weird, that USER_A got the CREATE SYNONYM privilege with GRANT option via Role.
So USER_A granted the GRANT CREATE SYNONYM privilege to himself, and then the PL/SQL code could be run...

=======================================
Why I do not see all existing tables in ALL_TABLES?
=======================================
Without explicit grants, the user is allowed to see only tables that have public synonyms.

To resolve this, if indeed it is needed, grant SELECT ANY TABLE to the user.

Here is the example:
AS DBA Privileged user
SQL> select count(*) from ALL_TABLES;
  COUNT(*)
----------
      2737
 
As non privileged query_user:
SQL> SELECT COUNT(*) FROM ALL_TABLES;
  COUNT(*)
----------
        42

AS DBA Privileged user:
SQL> GRANT SELECT ANY TABLE TO query_user;
Grant succeeded.

Now run again as query_user:
SQL> SELECT COUNT(*) FROM ALL_TABLES;
  COUNT(*)
----------

      2737

Appendix
Short Reference