In Oracle, a user is also a schema: the objects a user creates belong to it. A good design separates the schema that owns the tables from the users that applications connect as, which get only the privileges they need. This guide creates such an application user, sets its password and space quota, and assigns a profile with password and session rules.
Code for This Guide
The main examples are in the examples/security folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create user [if not exists] user identified by password
[default tablespace ts] [temporary tablespace ts]
[quota {n | unlimited} on ts]
[profile profile] [password expire] [account {lock | unlock}]
alter user user {identified by password | account {lock | unlock} | profile profile | quota ...}
create profile profile limit {resource | password_parameter} {n | unlimited | default} [...]Creating users and profiles needs administrator privileges, so these examples run as SYS in the pluggable database.
Create an Application User
The user NIMBUS_APP gets a password, the USERS tablespace with a 10 MB quota, and an expired password that must be changed at first login. The example then sets the password again, unlocks the account, and grants CREATE SESSION, without which the user cannot even connect.
Example:
create user nimbus_app identified by "Fly#Nimbus2026" default tablespace users quota 10m on users password expire; alter user nimbus_app identified by "Fly#Nimbus2026" account unlock; grant create session to nimbus_app; select username, account_status, default_tablespace, authentication_type from dba_users where username = 'NIMBUS_APP';
Output:
User NIMBUS_APP created. User NIMBUS_APP altered. Grant succeeded. USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE AUTHENTICATION_TYPE _____________ _________________ _____________________ ______________________ NIMBUS_APP OPEN USERS PASSWORD
DBA_USERS shows the account as OPEN. The password Fly#Nimbus2026 is a sample; choose your own.
Assign a Profile
A profile sets password rules and resource limits. Every user has one, DEFAULT unless another is assigned.
Example:
create profile app_profile limit failed_login_attempts 5 password_lock_time 1/24 password_life_time 90 sessions_per_user 10; alter user nimbus_app profile app_profile; select resource_name, limit from dba_profiles where profile = 'APP_PROFILE' and limit <> 'DEFAULT' order by resource_name;
Output:
Profile APP_PROFILE created. User NIMBUS_APP altered. RESOURCE_NAME LIMIT ________________________ ________ FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LIFE_TIME 90 PASSWORD_LOCK_TIME .0416 SESSIONS_PER_USER 10
After five failed logins the account locks for one hour (1/24 of a day), passwords last 90 days, and the user may have at most ten sessions. DBA_PROFILES lists the limits that differ from the default.
Drop the Demonstration Objects
DROP USER ... CASCADE drops a user with all its objects. The related guides on privileges and roles also create a role, NIMBUS_READER, which is dropped here too; skip any statement for an object you did not create.
Example:
drop user nimbus_app cascade; drop role nimbus_reader; drop profile app_profile;
Output:
User NIMBUS_APP dropped. Role NIMBUS_READER dropped. Profile APP_PROFILE dropped.
Things to Know
- PASSWORD EXPIRE forces a new password at the first login, so an administrator never knows the user's real password.
- Lock accounts that should not log in, such as schemas that only own objects: ALTER USER ... ACCOUNT LOCK.
- Resource limits in profiles, such as SESSIONS_PER_USER, apply only when the RESOURCE_LIMIT parameter is TRUE, the default.
Related Guides
Conclusion
CREATE USER creates an account and its schema, with a password, default tablespace, and quota; CREATE SESSION lets it connect; and a profile sets its password and session rules. Separate the users applications connect as from the schemas that own data, and give each only what it needs.
