In Oracle Database, user management plays a crucial role in security, access control, and resource limitation.
This article provides a comprehensive explanation of the CREATE USER statement—from the basics to tablespace assignment, quota settings, privilege grants, and account controls—suitable for practical, production-level environments.
🔰 What Is a User in Oracle?
A “user” in Oracle is an account that owns schema objects such as tables and views, and can access the database.
📘【Diagram】Relationship between User and Schema
User = Schema Owner
├── Table (EMP, DEPT, etc.)
├── Index
├── View
└── Synonym
Creating a user = establishing the base of a schema.
🧱 Basic Syntax and Components of CREATE USER
CREATE USER username
IDENTIFIED BY password
DEFAULT TABLESPACE tablespace
TEMPORARY TABLESPACE temp_tablespace;
✅ Minimal Example
CREATE USER test_user IDENTIFIED BY StrongP@ss1;
⚠️ At this point, the user still cannot connect. You must grant privileges like
CREATE SESSION.
📦 Assigning Tablespaces
It’s a best practice to explicitly assign both default and temporary tablespaces.
CREATE USER test_user
IDENTIFIED BY StrongP@ss1
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
📘【Diagram】Tablespace Configuration
User: test_user
├─ DEFAULT TABLESPACE → USERS
└─ TEMPORARY TABLESPACE → TEMP
📊 Quota Settings
If no quota is specified, the user cannot store objects in the tablespace.
ALTER USER test_user QUOTA UNLIMITED ON users;
Or specify a limited quota:
ALTER USER test_user QUOTA 100M ON users;
- UNLIMITED: No limit
- 100M: Limit to 100MB of usage
🔐 Granting Privileges
After user creation, minimal privileges must be granted for the user to operate.
GRANT CREATE SESSION TO test_user;
GRANT CREATE TABLE TO test_user;
Or grant roles that contain multiple privileges:
GRANT CONNECT, RESOURCE TO test_user;
| Role | Description |
|---|---|
| CONNECT | Includes CREATE SESSION for login |
| RESOURCE | Includes privileges for table creation, etc. |
⚠️ From Oracle 11g onward, RESOURCE includes outdated privileges. Use explicit GRANTs when possible.
🔄 Account Controls (Locking, Expiration, Passwords)
✅ Lock/Unlock Account
ALTER USER test_user ACCOUNT LOCK;
ALTER USER test_user ACCOUNT UNLOCK;
✅ Force Password Expiration
ALTER USER test_user PASSWORD EXPIRE;
→ Requires user to change password at next login.
✅ Use Profile for Security Policies
ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 90;
→ Locks after 5 failed logins, expires password in 90 days.
❌ Deleting Users with DROP USER
DROP USER test_user CASCADE;
CASCADEremoves all owned objects.- Without it, DROP fails if objects exist.
🔄 Full Workflow Diagram
1. CREATE USER
↓
2. ALTER USER to set QUOTA
↓
3. GRANT privileges
↓
4. Optional: Lock / Password Expire
↓
5. User starts using DB
↓
6. DROP USER CASCADE when no longer needed
✅ Key Points to Remember
| Item | Description |
|---|---|
| CREATE USER | Account creation with mandatory IDENTIFIED BY |
| Tablespace settings | Always specify default and temporary tablespaces |
| Quota | Without it, DML and object creation may fail |
| Privileges | CONNECT, RESOURCE or explicit GRANTs |
| Security policies | PASSWORD EXPIRE, PROFILE settings |
| Best Practices | Least privilege, dedicated tablespaces, auditing |
🎓 Recommended For:
- Beginners creating Oracle users for the first time
- DBAs managing multiple accounts
- Professionals designing secure user environments
[reference]
CREATE USER
コメント