Role-Based Access

Overview

At checkout, the broker creates a temporary MySQL user derived from the requesting user’s email and grants a specific privilege (e.g. SELECT) on a target table. At checkin, the privilege is revoked and the user is dropped. Admin credentials are retrieved from AWS Secrets Manager at runtime.

Use this pattern for scoped, least-privilege access — for example, a data analyst who needs read access to a specific table for a limited time.

Before You Begin

The broker is running and connected (see Getting Started)
mysql client, aws CLI, and jq are installed on the broker host
The broker host has an IAM role with secretsmanager:GetSecretValue permission
The broker service account (britive_svc) has been created in Aurora MySQL with the required permissions

Checkout Routine

Full script: Aurora MySQL/permissions/role-member/

Environment variables:

Variable	Default	Notes
`user`	—	Injected — requesting user’s email; username is derived by stripping domain and non-alphanumeric chars
`host`	—	Aurora cluster endpoint
`dburl`	—	Same as `host` — the cluster endpoint
`secret`	—	AWS Secrets Manager secret name or ARN containing admin credentials
`table`	—	Target table in `database.table` format (e.g. `myapp.orders`)
`role`	`SELECT`	MySQL privilege to grant (e.g. `SELECT`, `INSERT`, `SELECT,INSERT`)

Retrieve credentials and create user:

Aurora MySQL/permissions/role-member/checkout_sql_role.sh

# Derive MySQL username from email
DB_USER=$(echo "$user" | cut -d'@' -f1 | tr -cd '[:alnum:]')

# Retrieve admin credentials from Secrets Manager
SECRET_JSON=$(aws secretsmanager get-secret-value --secret-id "$secret" --query SecretString --output text)
ADMIN_USER=$(echo "$SECRET_JSON" | jq -r '.username')
ADMIN_PASS=$(echo "$SECRET_JSON" | jq -r '.password')

# Generate a random password for the temp user
DB_PASSWORD=$(openssl rand -base64 16 | tr -d '=/+')

# Create temp user and grant privilege on the target table
mysql -h "$dburl" -u "$ADMIN_USER" -p"$ADMIN_PASS" <<EOF
CREATE USER IF NOT EXISTS '${DB_USER}'@'%' IDENTIFIED BY '${DB_PASSWORD}';
GRANT ${role} ON ${table} TO '${DB_USER}'@'%';
FLUSH PRIVILEGES;
EOF

echo "username=${DB_USER}"
echo "password=${DB_PASSWORD}"
echo "host=${host}"

Checkin Routine

Full script: Aurora MySQL/permissions/role-member/

Revoke and drop the user:

Aurora MySQL/permissions/role-member/checkin_sql_role.sh

DB_USER=$(echo "$user" | cut -d'@' -f1 | tr -cd '[:alnum:]')

SECRET_JSON=$(aws secretsmanager get-secret-value --secret-id "$secret" --query SecretString --output text)
ADMIN_USER=$(echo "$SECRET_JSON" | jq -r '.username')
ADMIN_PASS=$(echo "$SECRET_JSON" | jq -r '.password')

mysql -h "$dburl" -u "$ADMIN_USER" -p"$ADMIN_PASS" <<EOF
REVOKE ALL PRIVILEGES ON ${table} FROM '${DB_USER}'@'%';
DROP USER IF EXISTS '${DB_USER}'@'%';
FLUSH PRIVILEGES;
EOF

echo "User ${DB_USER} revoked and dropped."

DROP USER IF EXISTS makes checkin idempotent — if the user was already removed (e.g. manual cleanup), checkin completes without error.

Configure in Britive

Create a response template

Go to Resource Manager → Response Templates → New Template. Add fields for username, password, and host from the checkout output.

Create a permission

Go to Resource Manager → Resource Type Permissions → New Permission. Set Language to Shell.

Paste the checkout and checkin routines into the respective fields.

Declare variables:

Variable	System defined	Notes
`user`	Yes	Injected automatically
`host`	No	Set per resource
`dburl`	No	Set per resource
`secret`	No	Set per resource
`table`	No	Set on permission or profile
`role`	No	e.g. `SELECT`

Under Response Templates, attach the template you created.

Create a profile

Go to Resource Manager → Profiles → New Profile. Set an expiration (e.g. 4h). Under Associations, select the resource label(s) for the Aurora clusters. Under Permissions, add the permission above.

Add a policy

Under Policies, assign members and configure approval conditions.

aurora-mysql-role.tf

# Response template — surfaces MySQL credentials
resource "britive_resource_manager_response_template" "mysql_creds" {
name                     = "MySQL-Credentials"
description              = "Returns MySQL username, password, and host on checkout"
show_on_ui               = true
is_console_access_enabled = false
template_data            = "Host: {{host}}\nUsername: {{username}}\nPassword: {{password}}"
}

# Resource type — defines the AuroraMySQLCluster class
resource "britive_resource_manager_resource_type" "aurora" {
name        = "AuroraMySQLCluster"
description = "Amazon Aurora MySQL cluster"

parameters {
  param_name   = "dburl"
  param_type   = "string"
  is_mandatory = true
}
parameters {
  param_name   = "host"
  param_type   = "string"
  is_mandatory = true
}
parameters {
  param_name   = "secret"
  param_type   = "string"
  is_mandatory = true
}
}

# Permission — role-based checkout/checkin routines
resource "britive_resource_manager_resource_type_permission" "aurora_role" {
name             = "Aurora-MySQL-RoleAccess"
resource_type_id = britive_resource_manager_resource_type.aurora.id
description      = "JIT MySQL user with scoped table privilege"
code_language    = "Shell"

checkout_code_file = "${path.module}/scripts/checkout.sh"
checkin_code_file  = "${path.module}/scripts/checkin.sh"

variables          = ["user", "host", "dburl", "secret", "table", "role"]
response_templates = [britive_resource_manager_response_template.mysql_creds.name]
}

# Resource label — categorize by environment
resource "britive_resource_manager_resource_label" "env" {
name        = "environment"
description = "Deployment environment"
label_color = "#2563eb"

values { name = "Production";  description = "Production clusters" }
values { name = "Staging";     description = "Staging clusters" }
}

# Resource — a specific Aurora cluster
resource "britive_resource_manager_resource" "aurora_prod" {
name          = "aurora-mysql-prod"
description   = "Production Aurora MySQL cluster"
resource_type = britive_resource_manager_resource_type.aurora.name

parameter_values = {
  dburl  = "cluster.cluster-xxx.us-east-1.rds.amazonaws.com"
  host   = "cluster.cluster-xxx.us-east-1.rds.amazonaws.com"
  secret = "britive/aurora-mysql/svc-credentials"
}

resource_labels = {
  environment = "Production"
}
}

# Profile — JIT role-based MySQL access
resource "britive_resource_manager_profile" "aurora_role" {
name                = "Aurora-MySQL-RoleAccess"
description         = "JIT scoped MySQL access via temporary user"
expiration_duration = 14400000   # 4 hours in milliseconds

associations {
  label_key = "environment"
  values    = ["Production"]
}
}

# Profile permission
resource "britive_resource_manager_profile_permission" "aurora_role" {
profile_id = britive_resource_manager_profile.aurora_role.id
name       = britive_resource_manager_resource_type_permission.aurora_role.name
version    = "latest"

variables {
  name              = "table"
  value             = "myapp.orders"
  is_system_defined = false
}
variables {
  name              = "role"
  value             = "SELECT"
  is_system_defined = false
}
}

# Profile policy
resource "britive_resource_manager_profile_policy" "aurora_role" {
profile_id  = britive_resource_manager_profile.aurora_role.id
policy_name = "Aurora-MySQL-RoleAccess-Policy"

members = jsonencode({
  tags             = [{ name = "data-analysts" }]
  users            = []
  serviceIdentities = []
  aiIdentities     = []
})

condition   = jsonencode({ ipAddress = null, timeOfAccess = null })
access_type = "Allow"
consumer    = "resourceprofile"
is_active   = true
is_draft    = false
}

Verify

Check out the profile

Navigate to My Access → find the profile → Check Out. The MySQL host, username, and password appear in the checkout response.

Connect and confirm access

mysql -h <returned-host> -u <returned-username> -p<returned-password>

-- Confirm the user and privilege
SELECT current_user();
SHOW GRANTS;
-- Expected: GRANT SELECT ON myapp.orders TO 'username'@'%'

Check in

Return to My Access → Check In.

Confirm access is revoked

mysql -h <host> -u <returned-username> -p<returned-password>
# Expected: ERROR 1045 (28000): Access denied

Troubleshoot

Symptom	Cause	Fix
`ERROR 1045: Access denied` during checkout	Service account lacks `GRANT OPTION`	Grant `WITH GRANT OPTION` to `britive_svc` on the managed table
`ERROR 1142: command denied` on `GRANT`	Wrong privilege name	Use uppercase MySQL privilege names: `SELECT`, `INSERT`, `UPDATE`
`Unable to locate credentials`	Broker IAM role missing Secrets Manager permission	Attach `secretsmanager:GetSecretValue` to the broker instance profile
User still exists after checkin	Checkin routine failed	Check broker logs; run `DROP USER IF EXISTS 'username'@'%';` manually
`table` variable not resolved	Variable not declared on the permission	Add `table` to the permission variable list

Last updated on July 8, 2026

Getting Started Temporary User Access