Oracle Database - User (Account/Client)

> Database > Oracle Database

1 - 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.

Advertising

3 - Management

3.1 - 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

3.2 - Lock and Unlock

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

3.3 - Password Modification

ALTER USER "gerard" IDENTIFIED BY "newPwd";

3.4 - 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;
Advertising

4 - Documentation / Reference

db/oracle/user.txt · Last modified: 2017/11/21 13:32 by gerardnico