Part 2 – Oracle Users, Roles, Profiles, Security and Access Management

Posted on by Rahul Mittal

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

UserPurpose
APP_OWNEROwns tables
APP_RWRead/write application user
APP_ROReporting user
APP_ADMINAdministrative 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

ParameterPurpose
DEFAULT TABLESPACEObject storage
TEMPORARY TABLESPACESorting operations
QUOTAStorage 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

PrivilegeDescription
CREATE SESSIONLogin to database
CREATE TABLECreate tables
CREATE VIEWCreate views
CREATE PROCEDURECreate procedures
CREATE SEQUENCECreate 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

RolePurpose
CONNECTLogin
RESOURCEObject creation
DBAFull 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

SettingMeaning
FAILED_LOGIN_ATTEMPTSLock account
PASSWORD_LIFE_TIMEPassword expiry
PASSWORD_REUSE_TIMEPrevent reuse
SESSIONS_PER_USERConcurrent 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

UserPurpose
APP_OWNEROwns objects
APP_RUNTIMEApplication
APP_REPORTReporting
APP_DBAAdministration

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

EnvironmentUser
DEVAPP_DEV
QAAPP_QA
UATAPP_UAT
PRODAPP_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

Leave a Reply

Your email address will not be published. Required fields are marked *