Introduction
In enterprise applications, database security is just as important as application security.
Many production incidents happen because:
- Applications use SYS accounts.
- Developers receive excessive privileges.
- Passwords never expire.
- Tables are directly accessible.
- Audit trails are missing.
- Roles are poorly designed.
A well-designed Oracle security model provides:
✅ Controlled access
✅ Separation of responsibilities
✅ Principle of least privilege
✅ Auditability
✅ Easier maintenance
This article covers Oracle security concepts every Java developer should understand.
Oracle Security Architecture
Oracle security consists of:
- Users
- Schemas
- Roles
- Privileges
- Profiles
- Synonyms
- Auditing
What is an Oracle User?
A user is an account that can connect to Oracle.
CREATE USER APP_USER
IDENTIFIED BY Welcome123;
Each user owns a schema.
APP_USER
EMPLOYEE
DEPARTMENT
ORDERS
What is a Schema?
A schema is a collection of database objects owned by a user.
Objects include:
- Tables
- Views
- Indexes
- Sequences
- Procedures
- Packages
Example:
APP_OWNER Schema
EMPLOYEE
EMPLOYEE_SEQ
EMPLOYEE_VIEW
Enterprise Environment Example
| User | Purpose |
|---|---|
| APP_OWNER | Owns tables |
| APP_RW | Read/write application user |
| APP_RO | Reporting user |
| APP_ADMIN | Administrative tasks |
Applications should connect using APP_RW.
Creating a User
CREATE USER APP_USER
IDENTIFIED BY Password123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA 500M ON USERS;
Parameters Explained
| Parameter | Purpose |
|---|---|
| DEFAULT TABLESPACE | Object storage |
| TEMPORARY TABLESPACE | Sorting operations |
| QUOTA | Storage limit |
Unlocking User
ALTER USER APP_USER
ACCOUNT UNLOCK;
Changing Password
ALTER USER APP_USER
IDENTIFIED BY NewPassword;
Dropping User
DROP USER APP_USER CASCADE;
CASCADE removes all schema objects.
System Privileges
System privileges allow administrative operations.
Examples:
GRANT CREATE TABLE TO APP_USER;
GRANT CREATE VIEW TO APP_USER;
GRANT CREATE SESSION TO APP_USER;
Common System Privileges
| Privilege | Description |
|---|---|
| CREATE SESSION | Login to database |
| CREATE TABLE | Create tables |
| CREATE VIEW | Create views |
| CREATE PROCEDURE | Create procedures |
| CREATE SEQUENCE | Create sequences |
Object Privileges
Object privileges control access to tables.
GRANT SELECT
ON EMPLOYEE
TO APP_USER;
Examples
GRANT INSERT ON EMPLOYEE TO APP_USER;
GRANT UPDATE ON EMPLOYEE TO APP_USER;
GRANT DELETE ON EMPLOYEE TO APP_USER;
ALL Privileges
GRANT ALL
ON EMPLOYEE
TO APP_USER;
Avoid in production.
Revoking Privileges
REVOKE DELETE
ON EMPLOYEE
FROM APP_USER;
Oracle Roles
Roles simplify privilege management.
Example:
CREATE ROLE APP_ROLE;
Grant privileges:
GRANT SELECT, INSERT
ON EMPLOYEE
TO APP_ROLE;
Assign role:
GRANT APP_ROLE
TO APP_USER;
Built-in Roles
| Role | Purpose |
|---|---|
| CONNECT | Login |
| RESOURCE | Object creation |
| DBA | Full administration |
Why Avoid DBA?
Bad:
GRANT DBA TO APP_USER;
Problems:
- Drop tables.
- Create users.
- Full database access.
Applications should never use DBA.
Role-Based Access Example
Application
|
APP_RW User
|
APP_RW_ROLE
|
Tables
Benefits:
- Easier management.
- Centralized permissions.
- Better security.
Profiles
Profiles manage:
- Password policies
- Session limits
- Resource limits
Create:
CREATE PROFILE APP_PROFILE
LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 90;
Assign:
ALTER USER APP_USER
PROFILE APP_PROFILE;
Password Policies
| Setting | Meaning |
|---|---|
| FAILED_LOGIN_ATTEMPTS | Lock account |
| PASSWORD_LIFE_TIME | Password expiry |
| PASSWORD_REUSE_TIME | Prevent reuse |
| SESSIONS_PER_USER | Concurrent sessions |
Password Expiration
ALTER USER APP_USER
PASSWORD EXPIRE;
User changes password at next login.
Account Locking
ALTER USER APP_USER
ACCOUNT LOCK;
Unlock:
ALTER USER APP_USER
ACCOUNT UNLOCK;
Synonyms
Synonyms hide schema names.
Without synonym:
SELECT *
FROM APP_OWNER.EMPLOYEE;
Create:
CREATE SYNONYM EMPLOYEE
FOR APP_OWNER.EMPLOYEE;
Now:
SELECT *
FROM EMPLOYEE;
Public Synonyms
CREATE PUBLIC SYNONYM EMPLOYEE
FOR APP_OWNER.EMPLOYEE;
Avoid excessive use.
Views for Security
Example:
CREATE VIEW EMPLOYEE_PUBLIC AS
SELECT
EMP_ID,
NAME
FROM EMPLOYEE;
Hide:
- Salary
- Sensitive information
Grant:
GRANT SELECT
ON EMPLOYEE_PUBLIC
TO REPORT_USER;
Principle of Least Privilege
Applications should only receive permissions they require.
Bad:
SELECT
INSERT
UPDATE
DELETE
DROP
ALTER
Good:
SELECT
INSERT
UPDATE
Production User Model
| User | Purpose |
|---|---|
| APP_OWNER | Owns objects |
| APP_RUNTIME | Application |
| APP_REPORT | Reporting |
| APP_DBA | Administration |
Auditing
Oracle supports auditing.
Enable:
AUDIT SESSION;
Audit table access:
AUDIT SELECT TABLE;
Audit login failures:
AUDIT CREATE SESSION;
Java Application User
Spring Boot:
spring.datasource.username=APP_RUNTIME
spring.datasource.password=*******
Avoid:
SYS
SYSTEM
Service Accounts
Production applications should use:
- Dedicated users
- Limited privileges
- Password rotation
Example:
PAYMENT_APP
ORDER_APP
NOTIFICATION_APP
Multi-Environment Strategy
| Environment | User |
|---|---|
| DEV | APP_DEV |
| QA | APP_QA |
| UAT | APP_UAT |
| PROD | APP_PROD |
Schema Ownership Best Practice
Recommended:
APP_OWNER
owns tables
APP_RUNTIME
accesses tables
Grant:
GRANT SELECT,
INSERT,
UPDATE
ON APP_OWNER.EMPLOYEE
TO APP_RUNTIME;
Java Developer Checklist
Before connecting:
✓ Does the user have CREATE SESSION?
✓ Are privileges minimal?
✓ Is DBA avoided?
✓ Are passwords managed?
✓ Are roles used?
✓ Are schemas separated?
✓ Is auditing enabled?
Common Mistakes
Using SYS
Bad:
spring.datasource.username=SYS
Granting DBA
Bad:
GRANT DBA TO APP_USER;
Application Owning Tables
Bad:
APP_USER
owns tables
No Password Policy
Bad:
Password never expires.
Interview Questions
Difference between user and schema?
A user owns a schema.
Difference between role and privilege?
Privileges grant permissions.
Roles group privileges.
Why avoid DBA?
Excessive permissions.
Why use synonyms?
Hide schema names.
What is least privilege?
Give only required permissions.
Why separate owner and runtime users?
Security and maintainability.
Summary
This article covered:
✅ Users
✅ Schemas
✅ Privileges
✅ Roles
✅ Profiles
✅ Password policies
✅ Synonyms
✅ Auditing
✅ Service accounts
✅ Production security
Next Article
Part 3 – Oracle SQL and Query Optimization
Topics:
- SQL execution order
- Joins
- Group By
- Having
- Subqueries
- CTEs
- Window functions
- Explain plan
- Index tuning
- Query optimization
- SQL performance troubleshooting
