Inspect User and Database Permissions with 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?

Prerequisites#

1. Docker installed
2. Node.js >= v18

Start Bytebase#

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

Create Service Account#

Run Demo#

1. Go to Bytebase API Example repo and clone it.

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

3. Go to subfolder permission-check, and run the following commands to start the demo application.

   pnpm i && pnpm dev

4. Open the demo in your browser, you'll see the following page.

5. 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:

1. In page.tsx, we fetch all roles by calling the /v1/roles API before any user selection.

2. 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));

3. 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
   }

4. We compare the roles with the given IAM to find matches:

   rolesWithPermission.some((role) => role.name === iam.role);

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

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

7. 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 }));
     }
   }

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

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

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

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

3. 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
   }

4. Handle CEL conditions:

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

5. 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:

1. Inspect which users have access to specific databases.
2. 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.