MySQL Database Security as Code (Versioned)

Managing database security through ad-hoc GRANT and REVOKE commands leads to drift, and permissions scattered across migration files make it hard to answer "who can access what?" Atlas lets you define roles, users, and permissions as code. With the versioned workflow, each change is captured in a migration file, reviewed by your team, and applied through CI/CD.

This guide covers the versioned workflow. For the declarative approach, see the declarative security guide.

Atlas Pro Feature

Roles, users, and permissions are available only to Atlas Pro users. To use this feature, run:

atlas login

Prerequisites

1. Docker
2. Atlas installed on your machine (installation guide)
3. An Atlas Pro account (run atlas login to authenticate)

Project Setup

Start by spinning up a local MySQL database using atlas tool docker:

export DATABASE_URL=$(atlas tool docker --url "docker://mysql/8" --name my-db)

Cleaning up

When you're done, stop the container with atlas tool docker kill --name my-db.

Database scope

MySQL roles and users are server-level objects, not schema-level. The target URL must use database scope (docker://mysql/8 without a schema suffix) so Atlas can manage both roles and schema objects together.

Create an atlas.hcl configuration file with roles and permissions enabled:

Atlas DDL (HCL)

atlas.hcl

env "local" {
  url = getenv("DATABASE_URL")
  dev = "docker://mysql/8"
  schema {
    src = "file://schema.my.hcl"
    mode {
      roles       = true   // Inspect and manage roles and users
      permissions = true   // Inspect and manage GRANT / REVOKE
    }
  }
  migration {
    dir = "file://migrations"
  }
}

SQL

atlas.hcl

env "local" {
  url = getenv("DATABASE_URL")
  dev = "docker://mysql/8"
  schema {
    src = "file://schema.sql"
    mode {
      roles       = true
      permissions = true
    }
  }
  migration {
    dir = "file://migrations"
  }
}

Note that sensitive = ALLOW is not set here. In the versioned workflow, passwords are not included in migration files by default. We will see how to inject them safely using template directories later in this guide.

Defining the Desired State

The schema file is the target state Atlas diffs against the migration directory. Let's model a SaaS order management application with tiered access:

Role	Purpose
app_readonly	Read-only access for reporting dashboards
app_writer	Read-write access for the application backend, inherits from app_readonly
app_admin	Full administrative access, inherits from app_writer

Two login users:

User	Host	Role membership
dashboard_user	% (any host)	app_readonly
api_user	% (any host)	app_writer

Atlas DDL (HCL)

schema.my.hcl

variable "api_password" {
  type = string
}

variable "dashboard_password" {
  type = string
}

role "app_readonly" {
  comment = "Read-only access for reporting"
}

role "app_writer" {
  comment   = "Read-write access for the application"
  member_of = [role.app_readonly]
}

role "app_admin" {
  comment   = "Administrative access"
  member_of = [role.app_writer]
}

user "dashboard_user@%" {
  password             = var.dashboard_password
  max_user_connections = 5
  comment              = "Reporting dashboard service account"
  member_of            = [role.app_readonly]
}

user "api_user@%" {
  password             = var.api_password
  max_user_connections = 20
  comment              = "Application API service account"
  member_of            = [role.app_writer]
}

table "users" {
  schema = schema.demo
  column "id" {
    type           = int
    null           = false
    auto_increment = true
  }
  column "name" {
    type = varchar(255)
    null = false
  }
  column "email" {
    type = varchar(255)
    null = false
  }
  primary_key {
    columns = [column.id]
  }
}

table "products" {
  schema = schema.demo
  column "id" {
    type           = int
    null           = false
    auto_increment = true
  }
  column "name" {
    type = varchar(255)
    null = false
  }
  column "price" {
    type = decimal(10,2)
    null = false
  }
  primary_key {
    columns = [column.id]
  }
}

table "orders" {
  schema = schema.demo
  column "id" {
    type           = int
    null           = false
    auto_increment = true
  }
  column "user_id" {
    type = int
    null = false
  }
  column "product_id" {
    type = int
    null = false
  }
  column "total" {
    type = decimal(10,2)
    null = false
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "fk_order_user" {
    columns     = [column.user_id]
    ref_columns = [table.users.column.id]
  }
  foreign_key "fk_order_product" {
    columns     = [column.product_id]
    ref_columns = [table.products.column.id]
  }
}

schema "demo" {
}

// Read-only: SELECT on all tables
permission {
  for_each   = [table.orders, table.products, table.users]
  for        = each.value
  to         = role.app_readonly
  privileges = [SELECT]
}

// Writer: SELECT, INSERT, UPDATE on orders and products
permission {
  for_each   = [table.orders, table.products]
  for        = each.value
  to         = role.app_writer
  privileges = [SELECT, INSERT, UPDATE]
}

// Column-level: api_user can only UPDATE the email column
permission {
  to         = user["api_user@%"]
  for        = table.users.column.email
  privileges = [UPDATE]
}

SQL

schema.sql

-- Create roles
CREATE ROLE `app_readonly`;
CREATE ROLE `app_writer`;
GRANT `app_readonly` TO `app_writer`;
CREATE ROLE `app_admin`;
GRANT `app_writer` TO `app_admin`;

-- Create users
CREATE USER `dashboard_user`@`%` IDENTIFIED BY '<sensitive>'
  WITH MAX_USER_CONNECTIONS 5;
CREATE USER `api_user`@`%` IDENTIFIED BY '<sensitive>'
  WITH MAX_USER_CONNECTIONS 20;

-- Assign roles
GRANT `app_readonly` TO `dashboard_user`@`%`;
GRANT `app_writer` TO `api_user`@`%`;

-- Create schema
CREATE DATABASE `demo`;

-- Create tables
CREATE TABLE `demo`.`users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `demo`.`products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `demo`.`orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `product_id` int NOT NULL,
  `total` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_order_user` FOREIGN KEY (`user_id`) REFERENCES `demo`.`users` (`id`),
  CONSTRAINT `fk_order_product` FOREIGN KEY (`product_id`) REFERENCES `demo`.`products` (`id`)
);

-- Permissions
GRANT SELECT ON `demo`.`orders` TO `app_readonly`;
GRANT SELECT ON `demo`.`products` TO `app_readonly`;
GRANT SELECT ON `demo`.`users` TO `app_readonly`;

GRANT SELECT, INSERT, UPDATE ON `demo`.`orders` TO `app_writer`;
GRANT SELECT, INSERT, UPDATE ON `demo`.`products` TO `app_writer`;

GRANT UPDATE (`email`) ON `demo`.`users` TO `api_user`@`%`;

Generating the Initial Migration

Run atlas migrate diff to generate the first migration file:

atlas migrate diff add_security \
  --env local \
  --var "api_password=s3cret" \
  --var "dashboard_password=r3port"

Atlas creates a migration directory with the generated SQL and a checksum file:

migrations/
├── 20260305120000_add_security.sql
└── atlas.sum

The generated migration contains every role, user, grant, and table:

migrations/20260305120000_add_security.sql

-- Create role "app_readonly"
CREATE ROLE `app_readonly`;
-- Create role "app_writer"
CREATE ROLE `app_writer`;
-- Grant role "app_readonly" to "app_writer"
GRANT `app_readonly` TO `app_writer`;
-- Create user "api_user"
CREATE USER `api_user` IDENTIFIED WITH caching_sha2_password
  WITH MAX_USER_CONNECTIONS 20 COMMENT "Application API service account";
-- Grant role "app_writer" to "api_user"
GRANT `app_writer` TO `api_user`;
-- Create role "app_admin"
CREATE ROLE `app_admin`;
-- Grant role "app_writer" to "app_admin"
GRANT `app_writer` TO `app_admin`;
-- Create user "dashboard_user"
CREATE USER `dashboard_user` IDENTIFIED WITH caching_sha2_password
  WITH MAX_USER_CONNECTIONS 5 COMMENT "Reporting dashboard service account";
-- Grant role "app_readonly" to "dashboard_user"
GRANT `app_readonly` TO `dashboard_user`;
-- Add new schema named "demo"
CREATE DATABASE `demo`;
-- Create "products" table
CREATE TABLE `demo`.`products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Grant privileges on table "products" to "app_readonly"
GRANT SELECT ON `demo`.`products` TO `app_readonly`;
-- Grant privileges on table "products" to "app_writer"
GRANT INSERT, SELECT, UPDATE ON `demo`.`products` TO `app_writer`;
-- Create "users" table
CREATE TABLE `demo`.`users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Grant privileges on table "users" to "app_readonly"
GRANT SELECT ON `demo`.`users` TO `app_readonly`;
-- Grant privileges on column "email" of table "users" to "api_user"
GRANT UPDATE (`email`) ON `demo`.`users` TO `api_user`;
-- Create "orders" table
CREATE TABLE `demo`.`orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `product_id` int NOT NULL,
  `total` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_order_product` (`product_id`),
  INDEX `fk_order_user` (`user_id`),
  CONSTRAINT `fk_order_product` FOREIGN KEY (`product_id`) REFERENCES `demo`.`products` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `fk_order_user` FOREIGN KEY (`user_id`) REFERENCES `demo`.`users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Grant privileges on table "orders" to "app_readonly"
GRANT SELECT ON `demo`.`orders` TO `app_readonly`;
-- Grant privileges on table "orders" to "app_writer"
GRANT INSERT, SELECT, UPDATE ON `demo`.`orders` TO `app_writer`;

Notice that passwords are not included. Atlas intentionally excludes them from versioned migration files - secrets should never be committed to source control.

Adding Passwords with Template Directories

To set passwords at deploy time, add ALTER USER ... IDENTIFIED BY statements to the migration and mark them with the -- atlas:sensitive directive so the values are never logged:

-- atlas:sensitive
ALTER USER `api_user` IDENTIFIED BY '{{ .api_password }}';
-- atlas:sensitive
ALTER USER `dashboard_user` IDENTIFIED BY '{{ .dashboard_password }}';

Then configure a template directory in atlas.hcl to inject the actual values at runtime:

atlas.hcl

variable "api_password" {
  type    = string
  default = "changeme"
}

variable "dashboard_password" {
  type    = string
  default = "changeme"
}

data "template_dir" "migrations" {
  path = "migrations"
  vars = {
    api_password       = var.api_password
    dashboard_password = var.dashboard_password
  }
}

env "local" {
  url = getenv("DATABASE_URL")
  dev = "docker://mysql/8"
  schema {
    src = "file://schema.my.hcl"
    mode {
      roles       = true
      permissions = true
    }
  }
  migration {
    dir = data.template_dir.migrations.url
  }
}

At deploy time, {{ .api_password }} and {{ .dashboard_password }} are replaced with real values from the template_dir vars. Source these from environment variables, or use the runtimevar data source to read passwords directly from AWS Secrets Manager, GCP Secret Manager, HashiCorp Vault, or any other supported secret store:

atlas.hcl

data "runtimevar" "api_pass" {
  url = "awssecretsmanager://api-db-password?region=us-east-1"
}

data "template_dir" "migrations" {
  path = "migrations"
  vars = {
    api_password = data.runtimevar.api_pass
  }
}

Applying Migrations

Apply to the target database:

atlas migrate apply --env local \
  --var "api_password=$API_PASSWORD" \
  --var "dashboard_password=$DASHBOARD_PASSWORD"

Migrating to version 20260305120000 (1 migration in total):

  -- migrating version 20260305120000
    -> CREATE ROLE `app_readonly`;
    -> CREATE ROLE `app_writer`;
    -> GRANT `app_readonly` TO `app_writer`;
    -> CREATE USER `api_user` IDENTIFIED WITH caching_sha2_password ...
    -> GRANT `app_writer` TO `api_user`;
    -> CREATE ROLE `app_admin`;
    -> GRANT `app_writer` TO `app_admin`;
    -> CREATE USER `dashboard_user` IDENTIFIED WITH caching_sha2_password ...
    -> GRANT `app_readonly` TO `dashboard_user`;
    -> CREATE DATABASE `demo`;
    -> CREATE TABLE `demo`.`products` ( ... );
    -> GRANT SELECT ON `demo`.`products` TO `app_readonly`;
    -> GRANT INSERT, SELECT, UPDATE ON `demo`.`products` TO `app_writer`;
    -> CREATE TABLE `demo`.`users` ( ... );
    -> GRANT SELECT ON `demo`.`users` TO `app_readonly`;
    -> GRANT UPDATE (`email`) ON `demo`.`users` TO `api_user`;
    -> CREATE TABLE `demo`.`orders` ( ... );
    -> GRANT SELECT ON `demo`.`orders` TO `app_readonly`;
    -> GRANT INSERT, SELECT, UPDATE ON `demo`.`orders` TO `app_writer`;
  -- ok

  -------------------------
  -- 1 migration
  -- 19 sql statements

With Atlas Cloud, push your migration directory and deploy from any CI/CD platform:

atlas migrate push app --env local

Making Incremental Changes

When requirements change, update the schema file and generate a new migration. Atlas computes only the diff. For example, adding an app_support role for the support team:

Atlas DDL (HCL)

Add to schema.my.hcl:

schema.my.hcl

role "app_support" {
  comment   = "Support team access"
  member_of = [role.app_readonly]
}

// Support can view and update orders
permission {
  to         = role.app_support
  for        = table.orders
  privileges = [SELECT, UPDATE]
}

SQL

Add to schema.sql:

schema.sql

CREATE ROLE `app_support`;
GRANT `app_readonly` TO `app_support`;

GRANT SELECT, UPDATE ON `demo`.`orders` TO `app_support`;

Generate the incremental migration:

atlas migrate diff add_support_role \
  --env local \
  --var "api_password=s3cret" \
  --var "dashboard_password=r3port"

Atlas generates only what changed - the new role and its grants:

migrations/20260305130000_add_support_role.sql

-- Create role "app_support"
CREATE ROLE `app_support`;
-- Grant role "app_readonly" to "app_support"
GRANT `app_readonly` TO `app_support`;
-- Grant privileges on table "orders" to "app_support"
GRANT SELECT, UPDATE ON `demo`.`orders` TO `app_support`;

The migration directory now holds both files. The sum of all migrations represents the current security state:

migrations/
├── 20260305120000_add_security.sql
├── 20260305130000_add_support_role.sql
└── atlas.sum

Next Steps

• Declarative workflow - describe the desired state and let Atlas figure out the diff
• Template directories - inject passwords at deploy time without exposing them in source control
• CI integration - lint security changes automatically on every PR
• HCL reference - all role and permission attributes
• CI/CD setup - deploy versioned migrations to production

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