Oracle Database - User (Account/Client)

About

A user is an entity that can be authenticated. A user can be a person or a software entity, such as a Java client. Each user is given a unique identity within a security realm. For efficient security management, Oracle recommends adding users to groups. A group is a collection of users who usually have something in common, such as working in the same department in a company.

You assign a profile to a user in order to control application parameters such as password, session behaviours.

Management

Create

The minimal statement is:

create user user identified by password;
-- Example
create user nico identified by nico quota unlimited ON users;
 
-- Followed generally by
GRANT CONNECT, resource TO nico;

User and password must follow the oracle naming convention and must be in ASCII

The default value for the tablespaces will be:

select default_tablespace, TEMPORARY_TABLESPACE from dba_users where username = 'NICO';
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------
USERS                          TEMP

Lock and Unlock

ALTER USER SH ACCOUNT UNLOCK;
ALTER USER SH ACCOUNT LOCK;

Password Modification

alter user "gerard" identified by "newPwd";

Password Policy

How to disable the default password policy change of 180 days.

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION NULL;
alter user USERNAME identified by PWD account unlock;

Documentation / Reference


Powered by ComboStrap