原始网页:https://www.cockroachlabs.com/docs/stable/roles.html


(new in v2.0) 角色是将一组具有相同权限的用户组织在一起,便于对用户及权限进行管理。

目前可使用如下SQL语句进行角色和权限的管理:

相关术语

英文 中文 简介
Role 角色 一个包含任意数量的用户和其他角色的组合
Role admin 角色管理员 能够授予其他用户角色的特殊用户,可以使用指令WITH ADMIN OPTION授予权限成为角色管理员
Superuser/Admin 超级管理员 拥有admin角色的用户,能够创建删除角色,数据库默认创建了admin角色且不可删除。
root root 数据库默认有一个授予admin角色的用户
Inherit 继承 将角色授予用户或其他角色的行为
Direct member 直接成员 一个角色的直接成员
Indirect member 间接成员 一个角色的间接成员

示例

准备

cockroach start --insecure --store=roles --host=localhost

创建用户、角色与授权

cockroach user set maxroach --insecure
cockroach sql --insecure
CREATE DATABASE test_roles;
SET DATABASE = test_roles;
CREATE ROLE system_ops;
SHOW ROLES;

+------------+
|  rolename  |
+------------+
| admin      |
| system_ops |
+------------+
GRANT CREATE, SELECT ON DATABASE test_roles TO system_ops;
SHOW GRANTS ON DATABASE test_roles;

+------------+--------------------+------------+------------+
|  Database  |       Schema       |    User    | Privileges |
+------------+--------------------+------------+------------+
| test_roles | crdb_internal      | admin      | ALL        |
| test_roles | crdb_internal      | root       | ALL        |
| test_roles | crdb_internal      | system_ops | CREATE     |
| test_roles | crdb_internal      | system_ops | SELECT     |
| test_roles | information_schema | admin      | ALL        |
| test_roles | information_schema | root       | ALL        |
| test_roles | information_schema | system_ops | CREATE     |
| test_roles | information_schema | system_ops | SELECT     |
| test_roles | pg_catalog         | admin      | ALL        |
| test_roles | pg_catalog         | root       | ALL        |
| test_roles | pg_catalog         | system_ops | CREATE     |
| test_roles | pg_catalog         | system_ops | SELECT     |
| test_roles | public             | admin      | ALL        |
| test_roles | public             | root       | ALL        |
| test_roles | public             | system_ops | CREATE     |
| test_roles | public             | system_ops | SELECT     |
+------------+--------------------+------------+------------+
GRANT system_ops TO maxroach;

测试

退出当前shell,尝试用maxroach用户操作数据库test_roles

cockroach sql --user=maxroach --database=test_roles --insecure
CREATE TABLE employees (
    id UUID DEFAULT uuid_v4()::UUID PRIMARY KEY,
    profile JSONB
);
DROP TABLE employees;

pq: user maxroach does not have DROP privilege on relation employees

maxroach用户不能删除employees表,因为system_ops角色只有该表的CRETAESELECT语句权限,没有删除该表的权限。

SHOW GRANTS ON TABLE employees;

+------------+--------+-----------+------------+------------+
|  Database  | Schema |   Table   |    User    | Privileges |
+------------+--------+-----------+------------+------------+
| test_roles | public | employees | admin      | ALL        |
| test_roles | public | employees | root       | ALL        |
| test_roles | public | employees | system_ops | CREATE     |
| test_roles | public | employees | system_ops | SELECT     |
+------------+--------+-----------+------------+------------+
cockroach sql --insecure
REVOKE ALL ON DATABASE test_roles FROM system_ops;

SHOW GRANTS ON DATABASE test_roles;

+------------+--------------------+-------+------------+
|  Database  |       Schema       | User  | Privileges |
+------------+--------------------+-------+------------+
| test_roles | crdb_internal      | admin | ALL        |
| test_roles | crdb_internal      | root  | ALL        |
| test_roles | information_schema | admin | ALL        |
| test_roles | information_schema | root  | ALL        |
| test_roles | pg_catalog         | admin | ALL        |
| test_roles | pg_catalog         | root  | ALL        |
| test_roles | public             | admin | ALL        |
| test_roles | public             | root  | ALL        |
+------------+--------------------+-------+------------+
REVOKE ALL ON TABLE test_roles.* FROM system_ops;

SHOW GRANTS ON TABLE test_roles.*;

+------------+--------+-----------+-------+------------+
|  Database  | Schema |   Table   | User  | Privileges |
+------------+--------+-----------+-------+------------+
| test_roles | public | employees | admin | ALL        |
| test_roles | public | employees | root  | ALL        |
+------------+--------+-----------+-------+------------+
DROP ROLE system_ops;