Users and Schemas
A user is an account that can log in. Every user owns a schema, which is the collection of objects (tables, views, procedures) that user created. The two names are used almost interchangeably.
Create a User
sql
CREATE USER report_user IDENTIFIED BY "Rep0rtPwd#1"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 200M ON users;
GRANT CREATE SESSION TO report_user;
Why GRANT? A new user can do nothing until you grant privileges. Without CREATE SESSION the user cannot even log in.
Change and Manage Accounts
sql
ALTER USER report_user IDENTIFIED BY "NewPwd#2"; -- reset password
ALTER USER report_user ACCOUNT LOCK;
ALTER USER report_user ACCOUNT UNLOCK;
ALTER USER report_user PASSWORD EXPIRE; -- force change at next login
ALTER USER report_user QUOTA 1G ON users;
Drop a User
sql
DROP USER report_user; -- fails if the user owns objects
DROP USER report_user CASCADE; -- also drops all their objects
Careful: CASCADE permanently removes all tables and data owned by the user. Export the schema first if you may need it.
Check Users
sql
SELECT username, account_status, lock_date, expiry_date,
default_tablespace, profile
FROM dba_users
WHERE oracle_maintained = 'N'
ORDER BY username;
Tip: oracle_maintained = 'N' hides Oracle's own internal accounts, so you only see the users you or your team created.
Quotas
A quota limits how much space a user can use in a tablespace. Without a quota, a user cannot create tables there.
| Quota | Command |
| Limited | QUOTA 500M ON users |
| Unlimited | QUOTA UNLIMITED ON users |
| None | QUOTA 0 ON users |
sql
SELECT username, tablespace_name, ROUND(bytes/1024/1024) AS used_mb, max_bytes
FROM dba_ts_quotas
WHERE username = 'SHOP';
Profiles: Password and Resource Rules
A profile is a named set of rules for passwords and resource limits. Every user belongs to one (the default is DEFAULT).
sql
CREATE PROFILE app_user_profile LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1/24
PASSWORD_LIFE_TIME 90
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 5
SESSIONS_PER_USER 3
IDLE_TIME 30;
ALTER USER report_user PROFILE app_user_profile;
| Setting | Meaning |
FAILED_LOGIN_ATTEMPTS | Lock the account after this many wrong passwords |
PASSWORD_LOCK_TIME | How long it stays locked (in days, 1/24 = 1 hour) |
PASSWORD_LIFE_TIME | Days until the password must change |
PASSWORD_REUSE_TIME / MAX | Stops reuse of old passwords |
SESSIONS_PER_USER | Maximum concurrent sessions |
IDLE_TIME | Minutes of inactivity before the session is disconnected |
sql
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE profile = 'APP_USER_PROFILE';
Conclusion
Create users with a default tablespace, a temp tablespace and a quota. Grant only what they need and put them in a profile that enforces a password policy.
Quick Summary: CREATE USER, GRANT CREATE SESSION, set quota, use profiles for password rules.