User and Database Permissions with Bytebase API

Bytebase is a database DevSecOps platform designed for developers, security, DBA, and platform engineering teams. While it offers an intuitive GUI for managing database schema changes and access control, some teams may want to integrate Bytebase into their existing DevOps platforms using the Bytebase API.

In our previous tutorial, we demonstrated how to create a schema change using the Bytebase API. This tutorial will focus on inspect user and database permissions in Bytebase, it’s OK if you haven’t gone through the previous tutorial.

By following this guide, you’ll learn how to:

Set up and configure API access.
For a given database, which users have been granted permissions?
For a given user, which databases have been granted permissions?

Repository

https://github.com/bytebase/example-api/tree/main/permission-check

Prerequisites

Docker installed
Node.js >= v18

Start Bytebase

Make sure your Docker daemon is running. Copy and paste the commands to start Bytebase.

docker run --rm --init \
  --name bytebase \
  --publish 8080:8080 --pull always \
  --volume ~/.bytebase/data:/var/opt/bytebase \
  bytebase/bytebase:3.8.0

Bytebase is now running via Docker, and you can access it via localhost:8080. Register the first admin account which will be granted Workspace Admin.

Create Service Account

Log in as Workspace Admin, and go to IAM & Admin > Users & Groups. Click + Add User, fill in with api-sample, choose the Workspace DBA role sufficient for this tutorial and click Confirm.
Find the newly created service account and Copy Service Key. We will use this token to authenticate the API calls.

Run Demo

Go to Bytebase API Example repo and clone it.
Copy env-template.local to .env.local.Update the variables.
- NEXT_PUBLIC_BB_URL: http://localhost:8080
- NEXT_PUBLIC_BB_SERVICE_ACCOUNT: api-example
- NEXT_PUBLIC_BB_SERVICE_KEY: service key copied in previous step
Go to subfolder permission-check, and run the following commands to start the demo application.
```
pnpm i && pnpm dev
```
Open the demo in your browser, you’ll see the following page.
To enhance the demo’s realism, go to Bytebase and add more users and set up user permissions:
- Add new users in Security & Policy > Users & Groups
- Grant roles:
  - For workspace-level access: Security & Policy > Members & Roles
  - For project-specific access: Navigate to a project and use Manage > Members

Inspect Permissions

Database + Permission => Users

The left side form demonstrates a scenario where you have a database and want to inspect who has access to it. This is useful for continuously tracking access to sensitive databases and ensuring only authorized users have permissions.

Let’s dig into the code:

In page.tsx, we fetch all roles by calling the /v1/roles API before any user selection.
In db-fetch-user-permission.tsx, after the user selects a project, database, and permission, we filter the roles with the given permission:
```
const rolesWithPermission = allRoles.filter((role) => role.permissions.includes(permission));
```

Bytebase has two levels of Identity Access Management (IAM): Workspace and Project. We fetch both:

Workspace-level IAM: /v1/workspaces/*:getIamPolicy API
Project-level IAM: /v1/projects/PROJECT_ID:getIamPolicy API

The IAM object structure varies slightly between levels. Here’s an example of a workspace-level IAM:

{
  "role": "roles/workspaceDBA",
  "members": [
    "user:api-sample@service.bytebase.com",
    "user:a@bytebase.com",
    "user:admin2@x.com"
  ],
  "condition": {
    "expression": "",
    "title": "",
    "description": "",
    "location": ""
  },
  "parsedExpr": null
}

We compare the roles with the given IAM to find matches:
```
rolesWithPermission.some((role) => role.name === iam.role);
```

Some project-level IAM objects include a condition field for fine-grained access control. For example:

{
  "role": "roles/sqlEditorUser",
  "members": ["user:dev@x.com", "user:dev2@x.com"],
  "condition": {
    "expression": "(resource.database in [\"instances/test-sample-instance/databases/hr_test\"])",
    "title": "SQL Editor User hr_prod",
    "description": "",
    "location": ""
  }
}

This grants the SQL Editor User role only for the hr_test database in test-sample-instance.

When processing IAM policies, handle CEL (Common Expression Language) format conditions carefully. In this demo, we parse these conditions using a custom parseCelExpression function:
```
const celValue = await parseCelExpression(iam.condition.expression);
```
This function parseCelExpression, adapted from Bytebase’s CEL plugin and cel.ts, parses the condition expression string. It is primarily based on the convertFromCELString function in cel.ts, though you could use convertFromExpr to create your own. The function returns an array of database resources with their associated expiration times. This function and related CEL parsing logic are implemented in a custom plugins/cel folder within the demo project.

We then check if the conditions are met for the specific database:

for (let dbrs of celValue.databaseResources) {
  if (dbrs.databaseName == database) {
    return iam.members.map((member) => ({ member, expiredTime }));
  }
}

The members array may include both users and groups. To handle groups, use the v1/groups API:

{
   "role": "roles/projectQuerier",
   "members": ["user:dev2@x.com", "group:bbbb@x.com"],
   ...
}

By following these steps, you can effectively determine which users have access to a specific database, taking into account both direct user assignments and group memberships, as well as any conditional access rules.

User + Permission => Databases

The right side form demonstrates a scenario where you have a user and want to inspect which databases they have access to. This is useful for ongoing tracking of user permissions, especially when roles change or users leave the company.

Fetch all roles using the /v1/roles API in page.tsx.

In user-fetch-db-permission.tsx, filter roles based on the selected permission:

const rolesWithPermission = allRoles.filter((role) => role.permissions.includes(permission));

Fetch and compare workspace-level and project-level IAM policies:

Workspace-level check:

const hasUserWorkspacePermission = (rolesWithPermission, rolesToBeMatched) => {
  if (rolesToBeMatched.length === 0) return false;
  return rolesToBeMatched.some((roleToBeMatched) =>
    rolesWithPermission.some(
      (roleWithPermission) =>
        roleWithPermission.name === roleToBeMatched.role &&
        roleToBeMatched.members.includes(`user:${user}`),
    ),
  );
};

Project-level check:

for (const project of allProjects) {
  const projectIamData = await fetchData(
    `/api/projectiam/${encodeURIComponent(project.name.split('/')[1])}`,
  );
  const userHasMatchedRoles = getUserProjectPermissionRoles(
    rolesWithPermission,
    projectIamData.bindings,
    userGroups.length > 0,
    project.name,
  );
  // ... process matched roles
}

Handle CEL conditions:

if (role.condition && role.condition.expression === '') {
  userHasFullProjectPermission = true;
} else {
  const celValue = await parseCelExpression(role.condition.expression);
  celsConverted.push(celValue);
}

Group should be taken into consideration too.

return refinedRolesToBeMatched.filter((roleToBeMatched) => {
  const memberMatch = roleToBeMatched.members.includes(`user:${user}`);
  const groupMatch =
    hasGroups &&
    userGroups.some((group) =>
      roleToBeMatched.members.includes(group.replace('groups/', 'group:')),
    );
  return memberMatch || groupMatch;
});

By following these steps, you can effectively identify all databases a specific user has access to, taking into account various permission levels and conditions.

Summary

Congratulations! You’ve successfully created a user and database permission inspection tool using the Bytebase API. This allows you to:

Inspect which users have access to specific databases.
Inspect which databases a particular user can access.

By leveraging these API calls within existing DevOps platforms, organizations can improve their database access governance and security posture.

On this page

Repository
Prerequisites
Start Bytebase
Create Service Account
Run Demo
Inspect Permissions
Database + Permission => Users
User + Permission => Databases
Summary

By following this guide, you’ll learn how to:

Set up and configure API access.
For a given database, which users have been granted permissions?
For a given user, which databases have been granted permissions?

Repository

https://github.com/bytebase/example-api/tree/main/permission-check

Prerequisites

Docker installed
Node.js >= v18

Start Bytebase

Make sure your Docker daemon is running. Copy and paste the commands to start Bytebase.

docker run --rm --init \
  --name bytebase \
  --publish 8080:8080 --pull always \
  --volume ~/.bytebase/data:/var/opt/bytebase \
  bytebase/bytebase:3.8.0

Bytebase is now running via Docker, and you can access it via localhost:8080. Register the first admin account which will be granted Workspace Admin.

Create Service Account

Log in as Workspace Admin, and go to IAM & Admin > Users & Groups. Click + Add User, fill in with api-sample, choose the Workspace DBA role sufficient for this tutorial and click Confirm.
Find the newly created service account and Copy Service Key. We will use this token to authenticate the API calls.

Run Demo

Go to Bytebase API Example repo and clone it.
Copy env-template.local to .env.local.Update the variables.
- NEXT_PUBLIC_BB_URL: http://localhost:8080
- NEXT_PUBLIC_BB_SERVICE_ACCOUNT: api-example
- NEXT_PUBLIC_BB_SERVICE_KEY: service key copied in previous step
Go to subfolder permission-check, and run the following commands to start the demo application.
```
pnpm i && pnpm dev
```
Open the demo in your browser, you’ll see the following page.
To enhance the demo’s realism, go to Bytebase and add more users and set up user permissions:
- Add new users in Security & Policy > Users & Groups
- Grant roles:
  - For workspace-level access: Security & Policy > Members & Roles
  - For project-specific access: Navigate to a project and use Manage > Members

Inspect Permissions

Database + Permission => Users

Let’s dig into the code:

In page.tsx, we fetch all roles by calling the /v1/roles API before any user selection.
In db-fetch-user-permission.tsx, after the user selects a project, database, and permission, we filter the roles with the given permission:
```
const rolesWithPermission = allRoles.filter((role) => role.permissions.includes(permission));
```

Bytebase has two levels of Identity Access Management (IAM): Workspace and Project. We fetch both:

Workspace-level IAM: /v1/workspaces/*:getIamPolicy API
Project-level IAM: /v1/projects/PROJECT_ID:getIamPolicy API

The IAM object structure varies slightly between levels. Here’s an example of a workspace-level IAM:

{
  "role": "roles/workspaceDBA",
  "members": [
    "user:api-sample@service.bytebase.com",
    "user:a@bytebase.com",
    "user:admin2@x.com"
  ],
  "condition": {
    "expression": "",
    "title": "",
    "description": "",
    "location": ""
  },
  "parsedExpr": null
}

We compare the roles with the given IAM to find matches:
```
rolesWithPermission.some((role) => role.name === iam.role);
```

Some project-level IAM objects include a condition field for fine-grained access control. For example:

{
  "role": "roles/sqlEditorUser",
  "members": ["user:dev@x.com", "user:dev2@x.com"],
  "condition": {
    "expression": "(resource.database in [\"instances/test-sample-instance/databases/hr_test\"])",
    "title": "SQL Editor User hr_prod",
    "description": "",
    "location": ""
  }
}

This grants the SQL Editor User role only for the hr_test database in test-sample-instance.

When processing IAM policies, handle CEL (Common Expression Language) format conditions carefully. In this demo, we parse these conditions using a custom parseCelExpression function:
```
const celValue = await parseCelExpression(iam.condition.expression);
```
This function parseCelExpression, adapted from Bytebase’s CEL plugin and cel.ts, parses the condition expression string. It is primarily based on the convertFromCELString function in cel.ts, though you could use convertFromExpr to create your own. The function returns an array of database resources with their associated expiration times. This function and related CEL parsing logic are implemented in a custom plugins/cel folder within the demo project.

We then check if the conditions are met for the specific database:

for (let dbrs of celValue.databaseResources) {
  if (dbrs.databaseName == database) {
    return iam.members.map((member) => ({ member, expiredTime }));
  }
}

The members array may include both users and groups. To handle groups, use the v1/groups API:

{
   "role": "roles/projectQuerier",
   "members": ["user:dev2@x.com", "group:bbbb@x.com"],
   ...
}

User + Permission => Databases

Fetch all roles using the /v1/roles API in page.tsx.

In user-fetch-db-permission.tsx, filter roles based on the selected permission:

const rolesWithPermission = allRoles.filter((role) => role.permissions.includes(permission));

Fetch and compare workspace-level and project-level IAM policies:

Workspace-level check:

const hasUserWorkspacePermission = (rolesWithPermission, rolesToBeMatched) => {
  if (rolesToBeMatched.length === 0) return false;
  return rolesToBeMatched.some((roleToBeMatched) =>
    rolesWithPermission.some(
      (roleWithPermission) =>
        roleWithPermission.name === roleToBeMatched.role &&
        roleToBeMatched.members.includes(`user:${user}`),
    ),
  );
};

Project-level check:

for (const project of allProjects) {
  const projectIamData = await fetchData(
    `/api/projectiam/${encodeURIComponent(project.name.split('/')[1])}`,
  );
  const userHasMatchedRoles = getUserProjectPermissionRoles(
    rolesWithPermission,
    projectIamData.bindings,
    userGroups.length > 0,
    project.name,
  );
  // ... process matched roles
}

Handle CEL conditions:

if (role.condition && role.condition.expression === '') {
  userHasFullProjectPermission = true;
} else {
  const celValue = await parseCelExpression(role.condition.expression);
  celsConverted.push(celValue);
}

Group should be taken into consideration too.

return refinedRolesToBeMatched.filter((roleToBeMatched) => {
  const memberMatch = roleToBeMatched.members.includes(`user:${user}`);
  const groupMatch =
    hasGroups &&
    userGroups.some((group) =>
      roleToBeMatched.members.includes(group.replace('groups/', 'group:')),
    );
  return memberMatch || groupMatch;
});

By following these steps, you can effectively identify all databases a specific user has access to, taking into account various permission levels and conditions.

Summary

Congratulations! You’ve successfully created a user and database permission inspection tool using the Bytebase API. This allows you to:

Inspect which users have access to specific databases.
Inspect which databases a particular user can access.

By leveraging these API calls within existing DevOps platforms, organizations can improve their database access governance and security posture.

On this page

Repository
Prerequisites
Start Bytebase
Create Service Account
Run Demo
Inspect Permissions
Database + Permission => Users
User + Permission => Databases
Summary

​Repository

​Prerequisites

​Start Bytebase

​Create Service Account

​Run Demo

​Inspect Permissions

​Database + Permission => Users

​User + Permission => Databases

​Summary

​Repository

​Prerequisites

​Start Bytebase

​Create Service Account

​Run Demo

​Inspect Permissions

​Database + Permission => Users

​User + Permission => Databases

​Summary

Repository

Prerequisites

Start Bytebase

Create Service Account

Run Demo

Inspect Permissions

Database + Permission => Users

User + Permission => Databases

Summary

Repository

Prerequisites

Start Bytebase

Create Service Account

Run Demo

Inspect Permissions

Database + Permission => Users

User + Permission => Databases

Summary