Skip to main content

Oracle Database Security as Code (Versioned)

Oracle Trademark

Oracle is a trademark of Oracle Corporation. Atlas is not affiliated with or endorsed by Oracle.

Managing Oracle access control through ad-hoc GRANT and REVOKE statements leads to drift: privileges accumulate across scripts and sessions, and it becomes hard to answer "who can touch what?" Atlas lets you define roles and privileges alongside your tables as code, then plans the exact GRANT/REVOKE statements needed to reach that state. With the versioned workflow, each change is captured as versioned migration files your team reviews and deploys through CI/CD.

This guide covers the versioned workflow. For the declarative approach, see the declarative security guide. For Oracle schema basics, see the automatic migrations guide.

Atlas Pro Feature

The Oracle driver and role/permission management are available only to Atlas Pro users. To use this feature, run:

atlas login

Prerequisites​

  1. Docker
  2. Atlas installed with the Oracle driver:

    To download and install the custom release of the Atlas CLI, simply run the following in your terminal:

    curl -sSf https://atlasgo.sh | ATLAS_FLAVOR="oracle" sh
  3. An Atlas Pro account (run atlas login to authenticate)

Setting Up the Database​

Start a local Oracle instance. The free:latest-lite image is the smallest and fastest to boot:

docker run --rm \
  -e ORACLE_PWD=Pssw0rd0995 \
  -p 1521:1521 \
  --name atlas-oracle \
  -d container-registry.oracle.com/database/free:latest-lite
Waiting for the database to be ready

Oracle images are larger than most and take longer to initialize. Wait until the database is ready before continuing:

docker logs -f atlas-oracle 2>&1 | grep -m1 "DATABASE IS READY TO USE"

Atlas connects as an ordinary Oracle user and manages objects in that user's own schema, so it never needs the broad CREATE ANY TABLE / GRANT ANY OBJECT PRIVILEGE system privileges. For this local walkthrough we connect as the built-in PDBADMIN; grant it the handful of privileges Atlas actually uses:

docker exec -i atlas-oracle sqlplus \
  -S 'sys/Pssw0rd0995@localhost:1521/FREEPDB1' AS SYSDBA <<SQL
GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO PDBADMIN;
GRANT CREATE ROLE TO PDBADMIN;
GRANT UNLIMITED TABLESPACE TO PDBADMIN;
GRANT EXECUTE ON SYS.DBMS_LOCK TO PDBADMIN;
SQL
  • CONNECT, RESOURCE - Log in and create tables in the user's own schema. SELECT_CATALOG_ROLE - Read the data dictionary so schema inspect can see roles and grants.
  • CREATE ROLE - Create roles (which are database-global in Oracle). Dropping a role needs no extra privilege: creating one grants it to you WITH ADMIN OPTION.
  • UNLIMITED TABLESPACE - Store your tables and the atlas_schema_revisions history table (or grant a bounded QUOTA on a specific tablespace).
  • EXECUTE ON SYS.DBMS_LOCK - The advisory lock Atlas takes during migrate apply. Without it: PLS-00201: identifier 'DBMS_LOCK' must be declared.
Use a least-privilege deploy user in production

PDBADMIN is the pluggable-database administrator. It works for a local demo, but for real deployments follow Oracle's principle of least privilege and connect as a dedicated user that owns the application schema. Since a user can create objects and grant privileges on its own schema with no special privilege, the deploy user needs only these grants and never any ANY privilege or the DBA role:

CREATE USER app IDENTIFIED BY "<password>";
GRANT CREATE SESSION, CREATE TABLE, CREATE ROLE TO app;   -- add CREATE VIEW / CREATE SEQUENCE if your schema uses them
GRANT SELECT_CATALOG_ROLE TO app;                         -- read the data dictionary for `schema inspect`
GRANT EXECUTE ON SYS.DBMS_LOCK TO app;
ALTER USER app QUOTA UNLIMITED ON users;                  -- your data tablespace

Provision this user once (the one-time privileged step above); Atlas then manages the tables it owns, the roles it creates, and their grants entirely under these minimal privileges. Oracle recommends keeping the schema owner separate from the runtime/application accounts, which connect using the roles you define below, for separation of duties. Point your schema files at this user's schema (schema "APP" instead of schema "PDBADMIN"), and inject the password from a secret manager rather than a file, following Atlas's dedicated migration-user pattern.

Configuring Atlas​

Roles and permissions are excluded from planning by default. Enable them with a schema.mode block in your project configuration:

atlas.hcl
env "local" {
  schemas = ["PDBADMIN"]
  url     = "oracle://PDBADMIN:Pssw0rd0995@localhost:1521/FREEPDB1?mode=database"
  dev     = "docker://oracle/free:latest-lite?t=10m&mode=database"
  migration {
    dir = "file://migrations"
  }
  schema {
    src = "file://schema.hcl"
    mode {
      roles       = true   // Inspect and manage roles
      permissions = true   // Inspect and manage GRANT / REVOKE
    }
  }
}
  • roles = true includes roles in inspection and planning.
  • permissions = true includes GRANT / REVOKE statements.
  • ?mode=database is required on both URLs. Roles are database-global objects, not schema-scoped, so Atlas can only inspect and plan them when connected in database mode. On the dev URL it lets Atlas compute the diff correctly; on the target URL it lets schema inspect see the existing roles and grants. Omit it on the target and inspection returns no roles.
  • The dev database is a temporary, throwaway instance Atlas uses to compute the diff.

Defining Roles​

Let's model an HR application with four roles:

RolePurpose
APP_READONLYRead-only access for reporting
APP_WRITERRead-write access, inherits APP_READONLY
APP_PAYROLLMay update salaries only
APP_ADMINStructural changes, inherits APP_WRITER
schema.hcl
schema "PDBADMIN" {
}

role "APP_READONLY" {
}

role "APP_WRITER" {
  member_of = [role.APP_READONLY]
}

role "APP_PAYROLL" {
}

role "APP_ADMIN" {
  member_of = [role.APP_WRITER]
}
  • Roles, not users - Oracle roles group privileges and are granted to users or other roles. Atlas creates them with NOT IDENTIFIED (no password), the standard form for application roles.
  • Inheritance - member_of maps to GRANT role TO role. APP_WRITER inherits everything granted to APP_READONLY, and APP_ADMIN inherits from APP_WRITER.
  • Identifiers - Oracle folds unquoted names to uppercase. Atlas always quotes them, so APP_READONLY stays APP_READONLY.

Defining Permissions​

Permissions reference the tables they apply to, so define those first, then attach the grants. Add the tables and grants below to the same schema file you started in Defining Roles (schema.hcl or schema.sql). Atlas reads the whole file as one desired state, so roles, tables, and grants must live together rather than in separate files.

schema.hcl
table "EMPLOYEES" {
  schema = schema.PDBADMIN
  column "ID" {
    null = false
    type = NUMBER
  }
  column "NAME" {
    null = false
    type = VARCHAR2(100)
  }
  column "EMAIL" {
    null = false
    type = VARCHAR2(200)
  }
  column "SALARY" {
    null = true
    type = NUMBER
  }
  primary_key "PK_EMPLOYEES" {
    columns = [column.ID]
  }
}

table "DEPARTMENTS" {
  schema = schema.PDBADMIN
  column "ID" {
    null = false
    type = NUMBER
  }
  column "NAME" {
    null = false
    type = VARCHAR2(100)
  }
  primary_key "PK_DEPARTMENTS" {
    columns = [column.ID]
  }
}

// Read-only: SELECT on all tables
permission {
  for_each   = [table.EMPLOYEES, table.DEPARTMENTS]
  for        = each.value
  to         = role.APP_READONLY
  privileges = ["SELECT"]
}

// Writer: full DML on EMPLOYEES
permission {
  to         = role.APP_WRITER
  for        = table.EMPLOYEES
  privileges = ["INSERT", "UPDATE", "DELETE"]
}

// Payroll: may update ONLY the SALARY column
permission {
  to         = role.APP_PAYROLL
  for        = table.EMPLOYEES.column.SALARY
  privileges = ["UPDATE"]
}

// Admin: may ALTER the table structure
permission {
  to         = role.APP_ADMIN
  for        = table.EMPLOYEES
  privileges = ["ALTER"]
}

for_each keeps permissions DRY: define the grant once, and Atlas expands it for every table at plan time.

Column-level privileges in Oracle

Oracle accepts a column list only for the INSERT, UPDATE, and REFERENCES privileges. SELECT and DELETE cannot be scoped to columns - they always apply to the whole table. The APP_PAYROLL grant above is column-level UPDATE, which is exactly the case Oracle allows.

WITH GRANT OPTION and roles

Atlas exposes grantable = true (Oracle's WITH GRANT OPTION) on permission blocks, but Oracle rejects it when the grantee is a role (ORA-01926). Use it only when granting to a user.

Prefer READ over SELECT for read-only roles

On Oracle 12.1.0.2 and later, consider granting READ instead of SELECT for purely read-only access. Plain SELECT also permits SELECT ... FOR UPDATE, which lets a "read-only" grantee lock rows - a denial-of-service vector. The READ object privilege removes that ability. Swap privileges = ["SELECT"] for privileges = ["READ"] on APP_READONLY if your reporting users never need to lock rows.

Generating the Migration​

Run atlas migrate diff to compare your desired state against the migration directory and write a new migration file:

atlas migrate diff initial --env local

Atlas creates a timestamped file under migrations/ containing every role, membership, table, and grant:

migrations/20260720143131_initial.sql
-- Create role "APP_READONLY"
CREATE ROLE "APP_READONLY" NOT IDENTIFIED;
-- Create role "APP_WRITER"
CREATE ROLE "APP_WRITER" NOT IDENTIFIED;
-- Grant role "APP_READONLY" to "APP_WRITER"
GRANT "APP_READONLY" TO "APP_WRITER";
-- Create role "APP_ADMIN"
CREATE ROLE "APP_ADMIN" NOT IDENTIFIED;
-- Grant role "APP_WRITER" to "APP_ADMIN"
GRANT "APP_WRITER" TO "APP_ADMIN";
-- Create role "APP_PAYROLL"
CREATE ROLE "APP_PAYROLL" NOT IDENTIFIED;
-- Add new table named "DEPARTMENTS"
CREATE TABLE "PDBADMIN"."DEPARTMENTS" (
  "ID" NUMBER NOT NULL,
  "NAME" VARCHAR2(100) NOT NULL,
  CONSTRAINT "PK_DEPARTMENTS" PRIMARY KEY ("ID")
);
-- Grant on table "DEPARTMENTS" to "APP_READONLY"
GRANT SELECT ON "PDBADMIN"."DEPARTMENTS" TO "APP_READONLY";
-- Add new table named "EMPLOYEES"
CREATE TABLE "PDBADMIN"."EMPLOYEES" (
  "ID" NUMBER NOT NULL,
  "NAME" VARCHAR2(100) NOT NULL,
  "EMAIL" VARCHAR2(200) NOT NULL,
  "SALARY" NUMBER,
  CONSTRAINT "PK_EMPLOYEES" PRIMARY KEY ("ID")
);
-- Grant on table "EMPLOYEES" to "APP_ADMIN"
GRANT ALTER ON "PDBADMIN"."EMPLOYEES" TO "APP_ADMIN";
-- Grant on table "EMPLOYEES" to "APP_READONLY"
GRANT SELECT ON "PDBADMIN"."EMPLOYEES" TO "APP_READONLY";
-- Grant on table "EMPLOYEES" to "APP_WRITER"
GRANT DELETE, INSERT, UPDATE ON "PDBADMIN"."EMPLOYEES" TO "APP_WRITER";
-- Grant on column "SALARY" of table "EMPLOYEES" to "APP_PAYROLL"
GRANT UPDATE ("SALARY") ON "PDBADMIN"."EMPLOYEES" TO "APP_PAYROLL";

Since grants depend on the roles and tables that back them, Atlas orders the statements automatically with roles and tables first, and grants after.

This file is the reviewable artifact the versioned workflow is built around: commit it, open it in a pull request, and let teammates read the exact GRANT/REVOKE statements before anything touches the database. You can also validate it in CI with atlas migrate lint, which flags risky or destructive changes automatically.

Applying the Migration​

Apply the pending migration to your database with atlas migrate apply:

atlas migrate apply --env local
Migrating to version 20260720143131 (1 migrations in total):

  -- migrating version 20260720143131
    -> CREATE ROLE "APP_READONLY" NOT IDENTIFIED
    -> CREATE ROLE "APP_WRITER" NOT IDENTIFIED
    -> GRANT "APP_READONLY" TO "APP_WRITER"
    -> CREATE ROLE "APP_ADMIN" NOT IDENTIFIED
    -> GRANT "APP_WRITER" TO "APP_ADMIN"
    -> CREATE ROLE "APP_PAYROLL" NOT IDENTIFIED
    -> CREATE TABLE "PDBADMIN"."DEPARTMENTS" ( ... )
    -> GRANT SELECT ON "PDBADMIN"."DEPARTMENTS" TO "APP_READONLY"
    -> CREATE TABLE "PDBADMIN"."EMPLOYEES" ( ... )
    -> GRANT ALTER ON "PDBADMIN"."EMPLOYEES" TO "APP_ADMIN"
    -> GRANT SELECT ON "PDBADMIN"."EMPLOYEES" TO "APP_READONLY"
    -> GRANT DELETE, INSERT, UPDATE ON "PDBADMIN"."EMPLOYEES" TO "APP_WRITER"
    -> GRANT UPDATE ("SALARY") ON "PDBADMIN"."EMPLOYEES" TO "APP_PAYROLL"
  -- ok (89.524439ms)

  -------------------------
  -- 1 migration
  -- 13 sql statements

Running it again is a no-op: Atlas tracks applied versions and reports No migration files to execute. To confirm what actually landed in the database at any point, jump to Inspecting the Result below. atlas schema inspect reads the live roles, memberships, and grants straight from Oracle.

Assigning Roles to Users​

The migration above creates the roles and attaches privileges to them, but a role only takes effect once it is granted to a login user. This is the payoff of the whole model: Oracle recommends granting privileges to roles rather than to users, then granting roles to users, so you manage access in one place and every user with the role reflects the change.

Following Oracle's separation-of-duties guidance, keep the schema owner separate from the runtime accounts that connect through these roles. Grant the roles to your application's login users:

-- Reporting user gets read-only access
GRANT "APP_READONLY" TO "HR_REPORTING";
-- Application service account gets read-write
GRANT "APP_WRITER" TO "HR_APP";
-- Payroll job gets the narrow column-level role
GRANT "APP_PAYROLL" TO "HR_PAYROLL";

Since these roles are NOT IDENTIFIED, Oracle enables them as default roles the moment they are granted. Each user picks up the role's privileges on their next login with no SET ROLE needed. Managing the login users themselves (identities, passwords, provisioning) is usually the job of a separate identity or account-management process, so this guide keeps user creation outside the schema-migration flow and focuses on the roles and grants Atlas manages as code.

Roles are disabled inside definer's-rights PL/SQL and views

One case where a granted role does not take effect: privileges granted through a role are inactive inside definer's-rights PL/SQL (packages, procedures, triggers) and in CREATE VIEW. If your application relies on stored program units, grant those object privileges directly to the owning schema rather than only through a role - or use invoker's rights (AUTHID CURRENT_USER). See Oracle's definer's vs invoker's rights.

Changing Permissions​

Security requirements change: suppose writers should no longer delete employees. Remove DELETE from the APP_WRITER grant in schema.hcl:

schema.hcl
  permission {
    to         = role.APP_WRITER
    for        = table.EMPLOYEES
-   privileges = ["INSERT", "UPDATE", "DELETE"]
+   privileges = ["INSERT", "UPDATE"]
  }

Generate the next migration:

atlas migrate diff revoke_delete --env local

Atlas plans exactly the REVOKE needed - nothing else:

migrations/20260720145012_revoke_delete.sql
-- Revoke on table "EMPLOYEES" from "APP_WRITER"
REVOKE DELETE ON "PDBADMIN"."EMPLOYEES" FROM "APP_WRITER";
Revoking column-level privileges

Oracle cannot revoke a single column from a multi-column grant. REVOKE always drops the whole privilege on the table. If APP_PAYROLL held UPDATE on both NAME and SALARY and you removed SALARY, Atlas plans a REVOKE UPDATE followed by a fresh GRANT UPDATE ("NAME") to land the remaining column. This is expected Oracle behavior, and Atlas handles the re-grant for you.

Inspecting the Result​

Confirm the live state at any time with atlas schema inspect:

atlas schema inspect --env local --format '{{ sql . }}'

The output reflects every role, membership, table, and grant. APP_WRITER now shows only GRANT INSERT, UPDATE ON "EMPLOYEES", with DELETE gone. Run this on any environment to confirm it matches the desired state. (Atlas's own atlas_schema_revisions table also appears; it stores migration history, not part of your schema.)

Next Steps​

Have questions? Feedback? Find our team on our Discord server or schedule a demo.