Access Control

Overview

On the Access Control page, administrators can directly grant database access permissions to users or groups.

Viewing the Access Control List

On the Access Control page, you can view the DB privileges and administrator role status granted to users and groups registered in QueryPie.

• Group / Users tab: Displays the list of groups and users and shows their status.
• Locked Accounts: You can check users who are locked due to account lock conditions and unlock them.

Viewing User/Group Access Control Details

Clicking on a specific user or group row in the Groups / Users tab list on the Access Control page opens a detail screen where you can view information about targets that have been granted privileges.

• Search: Can search by Connection Name
• Basic information
  • Type: Displays the classification type as USER or Group.
  • Auth Provider: Displays which authentication service provider the user uses for authentication.
  • Admin Roles: Displays the Admin Role assigned to the user.
  • Members: If the selected target is a Group, displays the members of that group.
  • Email: The user’s email address.
• List of targets granted privileges: If there are no connections with privileges granted, nothing is displayed in the list.
  • Connection Name: The name of the target connection.
  • Database Type **** : Database type (e.g. MySQL, MariaDB, PostgreSQL…)
  • Host: The Host address registered in the connection.
  • Port: The Port address registered in the connection.
  • Access Control Level: Indicates the level of access control. From 11.5.0, schema (logical DB) level control is available, so it is displayed as Host or Schema(Logical DB).
  • Assigned Privilege: The privilege assigned to the target.
  • Status: Displays the status of the privilege.
    • Active: The user has normal privileges for the connection.
    • Deactivated: The user has privileges for the connection but is in a deactivated state because they have not accessed the connection for a period set by the administrator.
    • Expired: The user’s privilege has been revoked because the expiration date has been reached.
  • Expiration date: Privilege expiration date
  • Renewed At: The time when the status was changed from deactivated to active by the administrator.
  • Granted At: The time when the privilege was granted.
  • Last Accessed At: The most recent time the user accessed the target.
  • Granted By: The administrator or approver who granted the privilege.

Granting Access Control Privileges - Host Level

1. Navigate to the Access Control menu from the Database settings menu.
2. Select the user or group to grant privileges to from the list and go to the detail page.
3. Click the Grant Privilege button.
4. In the popup window, select Host from Access Control Level. (Default: Host)
   

5. Select the connection to grant privileges to and choose a specific privilege type.

6. If necessary, set the privilege expiration date in Expiration date and click the Grant button.
7. You can also select multiple connections to grant privileges in bulk.
   

Users who have been granted privileges can now connect to the connection with those privileges, and the privilege grant history is recorded as an Access Control Granted event in the Access Control Logs.

Granting Access Control Privileges - Schema(Logical DB) Level

1. Navigate to the Access Control menu from the Database settings menu.
2. Select the user or group to grant privileges to from the list and go to the detail page.
3. Click the Grant Privilege button.
4. In the popup window, select Schema(Logical DB) from Access Control Level. (Default: Host)

5. Select the target connection from the Connections item. Schema level does not support multiple settings for multiple connections at once. When you select a connection, you can see that SELECT privilege is automatically granted to schemas that require select privilege in the privilege item on the right. Administrators can remove this item, but if they do, errors may occur when querying schema structure in Web editor or proxy, so it is recommended not to remove this item if possible.
6. Click the Add Item button to specify a specific schema (logical DB) to assign privileges to.
   A row is added below where you can add targets. For DBMS with schema structure (Oracle, Postgres, etc.), you must enter the target name in the format {database name}.{schema name}. For DBMS without schema structure like MySQL, enter only the logical DB name.
   - Oracle example: ORCLDB.TESTUSER
   - PostgreSQL example: testdb.test_schema
   - MySQL example: sakila

7. To assign privileges, first select the desired privilege type from the Host Level Privilege item at the top right of the popup window. (Schema level privileges can only be granted within the scope of host level privileges. For example, if you select Read/Write as Host Level Privilege, you can grant SELECT, INSERT, etc. within that scope, but if you select Read-Only as Host Level Privilege, you can only specify SELECT.)

8. Host Level Others and Host Level Functions displayed below are read-only. To modify them, you must modify the corresponding Privilege Type in the Databases > DB Access Control > Privilege Type menu. They are used to check upper-level information because Schema Level privileges are dependent on the properties of Host Level Privileges.
   

9. If necessary, set the privilege expiration date in Expiration date and click the Grant button.

Users who have been granted privileges can now connect to the connection with those privileges, and the privilege grant history is recorded as an Access Control Granted event in the Access Control Logs.

Revoking Access Control Privileges

1. Navigate to the Access Control menu from the Database settings menu.
2. Select the user or group to revoke privileges from in the list and go to the detail page.
3. Find the connection to revoke privileges from, select the checkbox, and revoke privileges through the Revoke button.
4. You can also select multiple connections to revoke privileges in bulk.

Users whose privileges have been revoked can no longer access the connection, and the privilege revocation history is recorded as an Access Control Revoked event in the Access Control Logs.

Unlocking Locked Connection Accounts

1. When a connection is locked due to exceeding the maximum number of DB account authentication failures (Maximum Login Failures) by the database connection security policy, it can be viewed in the Locked Account menu.
2. The number of DB account authentication failures and the time when it was locked are displayed together.
3. Select the item you want to unlock and click Unlock on the right to unlock the connection.

1. Connection lock and unlock history can be checked in the Audit > Databases > Account Lock History menu.

Access Control with Table Tags

When New DAC Policy Management is enabled in Databases > General > Configurations,

In Access Control, you can control access to only tables with specific tags for connections that specific users are allowed to access. The following conditions must be met to use this feature:

• The New DAC Policy Management feature must be enabled.
• A specific user must have privileges assigned to a specific connection.
• The target table must be registered as a path in Databases > Policy Management > Data Path and have tags assigned.

If the conditions are met, you can set it up as follows:

1. Go to the detailed screen of a specific connection with privileges assigned to a specific user in Databases > DB Access Control > Access Control.
2. In the detailed screen, select Tag-based table access as the Access Type and add tags. You can only select and use existing tags (tags assigned to tables).

If you enable the Tag-based table access feature but don’t register any tags, you won’t be able to access any tables in that connection.