Oracle user management

Create a new user:

create user bob
identified by str0nggpAsswd
default tablespace tblspace1
temporary tablespace temp;

Change user password:

alter user bob
identified by n3wPaswrd0;

Unlock a locked user account:

alter user bob
account unlock;

Assign privileges to user:

-- Allow user to connect to the database
grant create session to bob;

-- Allow user to create various objects;
-- usually reserved to DBAs and developers.
grant create table to bob;
grant create view to bob;
grant create sequence to bob;

-- Grant/revoke various table/view rights to objects to a user.
grant select on address_table to bob;
grant insert, update, delete on address_table to bob;
grant all on address_table to bob;

revoke delete on address_table from bob;
revoke all on address_table from bob;

-- Grant/revoke execute rights on functions/procedures for a user.
grant execute on sp_validate_address to bob;
revoke execute on sp_validate_address from bob;

Lock/unlock a user:

alter user bob account lock;
alter user bob account unlock;

Remove/delete a user:

drop user bob cascade;

List Oracle users:

-- List all users
select username, user_id, account_status, default_tablespace, temporary_tablespace, profile 
from dba_users;

-- List active users only
select username, user_id, account_status, default_tablespace, temporary_tablespace, profile 
from dba_users
where account_status='OPEN';