Common access management queries

There are occasions when privileges intersect or combine to produce unexpected results, the following commands can be used to narrow the issue using an admin account

### Listing the grants and roles for a user[​](#listing-the-grants-and-roles-for-a-user "Direct link to Listing the grants and roles for a user")

```
SHOW GRANTS FOR row_and_column_user
```

```
Query id: 6a73a3fe-2659-4aca-95c5-d012c138097b

┌─GRANTS FOR row_and_column_user───────────────────────────┐
│ GRANT A_rows_users, column1_users TO row_and_column_user │
└──────────────────────────────────────────────────────────┘
```

### List roles in ClickHouse[​](#list-roles-in-clickhouse "Direct link to List roles in ClickHouse")

```
SHOW ROLES
```

```
Query id: 1e21440a-18d9-4e75-8f0e-66ec9b36470a

┌─name────────────┐
│ A_rows_users    │
│ column1_users   │
└─────────────────┘
```

### Display the policies[​](#display-the-policies "Direct link to Display the policies")

```
SHOW ROW POLICIES
```

```
Query id: f2c636e9-f955-4d79-8e80-af40ea227ebc

┌─name───────────────────────────────────┐
│ A_row_filter ON db1.table1             │
│ allow_other_users_filter ON db1.table1 │
└────────────────────────────────────────┘
```

### View how a policy was defined and current privileges[​](#view-how-a-policy-was-defined-and-current-privileges "Direct link to View how a policy was defined and current privileges")

```
SHOW CREATE ROW POLICY A_row_filter ON db1.table1
```

```
Query id: 0d3b5846-95c7-4e62-9cdd-91d82b14b80b

┌─CREATE ROW POLICY A_row_filter ON db1.table1────────────────────────────────────────────────┐
│ CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users │
└─────────────────────────────────────────────────────────────────────────────────────────────┘
```

## Example commands to manage roles, policies, and users[​](#example-commands-to-manage-roles-policies-and-users "Direct link to Example commands to manage roles, policies, and users")

The following commands can be used to:

- delete privileges
- delete policies
- unassign users from roles
- delete users and roles

Tip

Run these commands as an admin user or the `default` user

### Remove privilege from a role[​](#remove-privilege-from-a-role "Direct link to Remove privilege from a role")

```
REVOKE SELECT(column1, id) ON db1.table1 FROM A_rows_users;
```

### Delete a policy[​](#delete-a-policy "Direct link to Delete a policy")

```
DROP ROW POLICY A_row_filter ON db1.table1;
```

### Unassign a user from a role[​](#unassign-a-user-from-a-role "Direct link to Unassign a user from a role")

```
REVOKE A_rows_users FROM row_user;
```

### Delete a role[​](#delete-a-role "Direct link to Delete a role")

```
DROP ROLE A_rows_users;
```

### Delete a user[​](#delete-a-user "Direct link to Delete a user")

```
DROP USER row_user;
```

## Summary[​](#summary "Direct link to Summary")

This article demonstrated the basics of creating SQL users and roles and provided steps to set and modify privileges for users and roles. For more detailed information on each please refer to our user guides and reference documentation.

- [Admin user](#admin-user)- [Passwordless authentication](#passwordless-authentication)
    - [Granular access control](#granular-access-control)- [Test admin privileges](#test-admin-privileges)- [Non-admin users](#non-admin-users)
        - [Preparation](#preparation)- [Verification](#verification)
          - [Testing role privileges with column restricted user](#testing-role-privileges-with-column-restricted-user)- [Testing role privileges with row restricted user](#testing-role-privileges-with-row-restricted-user)- [Modifying users and roles](#modifying-users-and-roles)- [Troubleshooting](#troubleshooting)
              - [Listing the grants and roles for a user](#listing-the-grants-and-roles-for-a-user)- [List roles in ClickHouse](#list-roles-in-clickhouse)- [Display the policies](#display-the-policies)- [View how a policy was defined and current privileges](#view-how-a-policy-was-defined-and-current-privileges)- [Example commands to manage roles, policies, and users](#example-commands-to-manage-roles-policies-and-users)
                - [Remove privilege from a role](#remove-privilege-from-a-role)- [Delete a policy](#delete-a-policy)- [Unassign a user from a role](#unassign-a-user-from-a-role)- [Delete a role](#delete-a-role)- [Delete a user](#delete-a-user)- [Summary](#summary)

Was this page helpful?