MySQL Database Security as Code (Declarative)

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 - and automatically plans the changes needed to bring any database in line with the desired state.

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)

Configuring Atlas

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

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

Cleaning up

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

Roles and permissions are excluded from inspection and schema management by default. To enable them, add a schema.mode block to your project configuration:

Atlas DDL (HCL)

atlas.hcl

env "local" {
  url = getenv("DATABASE_URL")
  schema {
    src = "file://schema.my.hcl"
    mode {
      roles       = true   // Inspect and manage roles and users
      permissions = true   // Inspect and manage GRANT / REVOKE
      sensitive   = ALLOW  // Allow password handling in declarative mode
    }
  }
}

SQL

atlas.hcl

env "local" {
  url = getenv("DATABASE_URL")
  schema {
    src = "file://schema.sql"
    mode {
      roles       = true
      permissions = true
      sensitive   = ALLOW
    }
  }
}

• roles = true - include roles and users in inspection and planning.
• permissions = true - include GRANT / REVOKE statements.
• sensitive = ALLOW - let Atlas manage passwords declaratively (they are always masked as <sensitive> in output).

Defining Roles and Users

Let's model a SaaS order management application with three tiers of 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 connect to the database at runtime:

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]
}

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`@`%`;

• Roles vs users - a user block creates a login account with IDENTIFIED BY. Roles cannot authenticate.
• Host specification - MySQL users include a host part. Use @% for any host, @localhost for local connections, or @10.0.0.% for subnet-restricted access.
• Inheritance - member_of creates a role hierarchy. app_writer inherits all privileges of app_readonly, and app_admin inherits everything from app_writer.
• Passwords - defined through input variables, never hardcoded. Inject real values at runtime from AWS Secrets Manager, GCP Secret Manager, HashiCorp Vault, or any other Atlas data source.
• Connection limits - max_user_connections caps the number of simultaneous connections per user. MySQL also supports max_connections (per-hour), max_questions, and max_updates.

Defining Permissions

With roles in place, grant privileges at the table and column level. First, define the tables that permissions will reference:

Atlas DDL (HCL)

schema.my.hcl

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" {
}

SQL

schema.sql

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

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

CREATE TABLE `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 `users` (`id`),
  CONSTRAINT `fk_order_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
);

Next, add the permissions:

Atlas DDL (HCL)

schema.my.hcl

// 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

-- Read-only: SELECT on all tables
GRANT SELECT ON `orders` TO `app_readonly`;
GRANT SELECT ON `products` TO `app_readonly`;
GRANT SELECT ON `users` TO `app_readonly`;

-- Writer: read-write on orders and products
GRANT SELECT, INSERT, UPDATE ON `orders` TO `app_writer`;
GRANT SELECT, INSERT, UPDATE ON `products` TO `app_writer`;

-- Column-level: api_user can only update email
GRANT UPDATE (`email`) ON `users` TO `api_user`@`%`;

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

note

For users with a host specification, use bracket notation in HCL: user["api_user@%"].

Applying Changes

Run atlas schema apply to diff the desired state against the live database and execute the changes:

atlas schema apply --env local \
  --var "api_password=s3cret" \
  --var "dashboard_password=r3port"

Atlas produces a plan showing every role, user, grant, and table it will create:

Planning migration statements (18 in total):

  -- 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 role "app_admin":
    -> CREATE ROLE `app_admin`;
  -- grant role "app_writer" to "app_admin":
    -> GRANT `app_writer` TO `app_admin`;
  -- create user "dashboard_user@%":
  -- atlas:sensitive:
    -> CREATE USER `dashboard_user`@`%` IDENTIFIED BY (sensitive)
       WITH MAX_USER_CONNECTIONS 5 COMMENT "Reporting dashboard service account";
  -- grant role "app_readonly" to "dashboard_user@%":
    -> GRANT `app_readonly` TO `dashboard_user`@`%`;
  -- create user "api_user@%":
  -- atlas:sensitive:
    -> CREATE USER `api_user`@`%` IDENTIFIED BY (sensitive)
       WITH MAX_USER_CONNECTIONS 20 COMMENT "Application API service account";
  -- grant role "app_writer" to "api_user@%":
    -> GRANT `app_writer` TO `api_user`@`%`;
  -- create "users" table:
    -> CREATE TABLE `users` ( ... );
  -- grant privileges on table "users" to "app_readonly":
    -> GRANT SELECT ON `users` TO `app_readonly`;
  -- grant privileges on column "email" of table "users" to "api_user@%":
    -> GRANT UPDATE (`email`) ON `users` TO `api_user`@`%`;
  -- create "products" table:
    -> CREATE TABLE `products` ( ... );
  -- grant privileges on table "products" to "app_readonly":
    -> GRANT SELECT ON `products` TO `app_readonly`;
  -- grant privileges on table "products" to "app_writer":
    -> GRANT INSERT, SELECT, UPDATE ON `products` TO `app_writer`;
  -- create "orders" table:
    -> CREATE TABLE `orders` ( ... );
  -- grant privileges on table "orders" to "app_readonly":
    -> GRANT SELECT ON `orders` TO `app_readonly`;
  -- grant privileges on table "orders" to "app_writer":
    -> GRANT INSERT, SELECT, UPDATE ON `orders` TO `app_writer`;

-------------------------------------------

? Approve or abort the plan:
  ▸ Approve and apply
    Abort

After approving, verify with atlas schema inspect:

atlas schema inspect --env local

The output reflects every role, user, and permission - a single, readable snapshot of your entire security posture. Run this on any environment to confirm it matches the desired state.

Next Steps

• CI integration - lint security changes automatically on every PR
• HCL reference - all role and permission attributes
• CI/CD setup - deploy declarative schemas to production
• PostgreSQL security guide - same workflow for PostgreSQL

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