Skip to main content

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" --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.

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.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
    }
  }
}
  • 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:

RolePurpose
app_readonlyRead-only access for reporting dashboards
app_writerRead-write access for the application backend, inherits from app_readonly
app_adminFull administrative access, inherits from app_writer

Two login users connect to the database at runtime:

UserHostRole membership
dashboard_user% (any host)app_readonly
api_user% (any host)app_writer
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]
}
  • 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.
  • Role activation - in MySQL, granted roles are inactive by default. To make them take effect at login, run SET DEFAULT ROLE ALL TO 'user'@'host' for each user, or enable activate_all_roles_on_login globally on the server.
  • 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_QUERIES_PER_HOUR, and MAX_UPDATES_PER_HOUR.

Defining Permissions​

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

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

function "get_order_total" {
  schema      = schema.demo
  data_access = READS_SQL_DATA
  arg "order_id" {
    type = int
  }
  return = decimal(10,2)
  as     = <<-SQL
  BEGIN
    DECLARE result DECIMAL(10,2);
    SELECT total INTO result FROM `orders` WHERE id = order_id;
    RETURN result;
  END
  SQL
}

procedure "update_order_total" {
  schema      = schema.demo
  data_access = MODIFIES_SQL_DATA
  arg "p_order_id" {
    type = int
  }
  arg "p_total" {
    type = decimal(10,2)
  }
  as = <<-SQL
  BEGIN
    UPDATE `orders` SET total = p_total WHERE id = p_order_id;
  END
  SQL
}

Next, add the permissions:

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

// Function-level: app_readonly can call get_order_total
permission {
  to         = role.app_readonly
  for        = function.get_order_total
  privileges = [EXECUTE]
}

// Procedure-level: only app_admin can call update_order_total
permission {
  to         = role.app_admin
  for        = procedure.update_order_total
  privileges = [EXECUTE]
}

for_each keeps permissions DRY - define the grant once, and Atlas expands it for every table at plan time. For functions and procedures, use EXECUTE as the privilege and reference the routine directly with function.<name> or procedure.<name>.

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, table, function, and procedure it will create:

Planning migration statements (23 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 (sensitive)@(sensitive) IDENTIFIED BY (sensitive)
       WITH MAX_USER_CONNECTIONS 5 COMMENT (sensitive);
  -- grant role "app_readonly" to "dashboard_user@%":
    -> GRANT `app_readonly` TO `dashboard_user`@`%`;
  -- create user "api_user@%":
  -- atlas:sensitive:
    -> CREATE USER (sensitive)@(sensitive) IDENTIFIED BY (sensitive)
       WITH MAX_USER_CONNECTIONS 20 COMMENT (sensitive);
  -- grant role "app_writer" to "api_user@%":
    -> GRANT `app_writer` TO `api_user`@`%`;
  -- add new schema named "demo":
    -> CREATE DATABASE `demo`;
  -- create "get_order_total" function:
    -> CREATE FUNCTION `demo`.`get_order_total` ( ... ) RETURNS decimal(10,2)
       READS SQL DATA BEGIN ... END;
  -- grant privileges on function "get_order_total" to "app_readonly":
    -> GRANT EXECUTE ON FUNCTION `demo`.`get_order_total` TO `app_readonly`;
  -- create "update_order_total" procedure:
    -> CREATE PROCEDURE `demo`.`update_order_total` ( ... )
       MODIFIES SQL DATA BEGIN ... END;
  -- grant privileges on procedure "update_order_total" to "app_admin":
    -> GRANT EXECUTE ON PROCEDURE `demo`.`update_order_total` TO `app_admin`;
  -- create "users" table:
    -> CREATE TABLE `demo`.`users` ( ... );
  -- 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 "products" table:
    -> CREATE TABLE `demo`.`products` ( ... );
  -- 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 "orders" table:
    -> CREATE TABLE `demo`.`orders` ( ... );
  -- 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`;

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

? 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, permission, function, and procedure in a single, readable snapshot of your entire security posture. Run this on any environment to confirm it matches the desired state.

Next Steps​

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