User Access Control
Users and Groups
DolphinDB uses user accounts and groups for access control. With user groups, you can manage users with similar access privileges. A user can belong to 0, 1 or multiple groups; a group can have 0, 1 or multiple users.
Only administrators are allowed to create administrators, users and groups.
Administrators can
grant
/deny
/revoke
access to
users or groups. When a DolphinDB cluster is started for the first time, it creates
a super admin with user ID "admin" and password "123456". This super admin has all
access privileges and cannot be deleted.
A newly-created administrator, user or group does not have any privilege.
Access Privilege Types
You can grant, deny, or revoke privileges with commands grant, deny, or revoke, which is marked as "allow", "deny" or "none" in the permission state. The following table shows privilege types supported in DolphinDB:
Privilege Type | Object/Level | Description | New in Version |
---|---|---|---|
TABLE_READ | global(*), table(dfs://db/tb) | Read tables | |
TABLE_WRITE | global(*), table(dfs://db/tb) | Write to tables | |
TABLE_INSERT | global(*), table(dfs://db/tb) | Insert into tables | 2.00.9/1.30.21 |
TABLE_UPDATE | global(*), table(dfs://db/tb) | Update tables | 2.00.9/1.30.21 |
TABLE_DELETE | global(*), table(dfs://db/tb) | Delete tables | 2.00.9/1.30.21 |
DBOBJ_CREATE | global(*), database(dfs://db) | Create tables in specific databases | |
DBOBJ_DELETE | global(*), database(dfs://db) | Delete tables and schema from specific databases | |
DB_READ | global(*), database(dfs://db) | Read tables in specific databases | 2.00.9/1.30.21 |
DB_WRITE | global(*), database(dfs://db) | Write to tables in specific databases | 2.00.9/1.30.21 |
DB_INSERT | global(*), database(dfs://db) | Insert into tables in specific databases | 2.00.9/1.30.21 |
DB_UPDATE | global(*), database(dfs://db) | Update tables in specific databases | 2.00.9/1.30.21 |
DB_DELETE | global(*), database(dfs://db) | Delete tables from specific databases | 2.00.9/1.30.21 |
VIEW_EXEC | global (*), function view (viewName), namespace (<namespace>::*) | Execute specific function views | |
DB_OWNER | global(* or unspecified),grant databases with specific prefix (dfs://{dbPrefix}*) |
Users with this privilege can grant other users with the following privileges on the databases created by themselves: TABLE_READ, TABLE_WRITE, TABLE_INSERT, TABLE_UPDATE, TABLE_DELETE, DBOBJ_CREATE, DBOBJ_DELETE, DB_READ, DB_WRITE, DB_INSERT, DB_UPDATE, DB_DELETE |
|
VIEW_OWNER |
Permission for regular users to create function views. Only administrators can
|
2.00.10.4/1.30.22.4 | |
DB_MANAGE | grant databases with specific prefix (dfs://{dbPrefix}*) | Manage specific databases,
including:
|
|
SCRIPT_EXEC | global(* or unspecified) | Execute scripts | |
TEST_EXEC | global(* or unspecified) | Execute test scripts | |
QUERY_RESULT_ MEM_LIMIT | memory size (in GB) | Set the memory limit for a query result | 2.00.9/1.30.21 |
TASK_GROUP_ MEM_LIMIT | memory size (in GB) | Set the memory limit for a task group | 2.00.9/1.30.21 |
COMPUTE_GROUP_EXEC | Use compute groups for computing | 3.00.2 |
If a user creates a scheduled job involving read/write/update operations on tables/databases with scheduleJob, this user must be granted relevant privileges when the scheduled job is executed.
Compatibility:
-
Version 2.00.3 onwards supports grant, deny, or revoke privileges TABLE_READ or TABLE_WRITE on shared tables, shared stream tables and streaming engines.
-
New features and improvements in version 2.00.9:
-
Extended privilege types at table level (TABLE_INSERT/TABLE_UPDATE/TABLE_DELETE) and database level (DB_INSERT/DB_UPDATE/DB_DELETE).
-
Modified DB_MANGE privilege which no longer permits database creations. Users with this privilege can only perform DDL operations on databases.
-
Modified DB_OWNER privilege which enables users to create databases with specified prefixes.
-
Added privilege types QUERY_RESULT_MEM_LIMIT and TASK_GROUP_MEM_LIMIT to set the upper limit of the memory usage of queries.
-
To enable access control on shared stream tables, ensure that both the publishers and the subscribers have the appropriate privileges of the tables involved in streaming.
-
Before subscribing to a stream table, a user must have:
-
TABLE_READ privilege to read the stream table;
-
Both TABLE_READ and TABLE_WRITE privileges on the local table where the subscribed data will be saved.
-
-
To write to a shared stream table on the publisher side, a user must have TABLE_READ and TABLE_WRITE privileges.
-
If publisher and subscriber are not on the same node, the object must be specified as "nodeAlias:tableName". For example, deny(`amy,TABLE_READ,"DFS_NODE1:st"), where DFS_NODE1 is the node storing the stream table and st is the table name.
-
Only the owner of a stream table or administrators can delete a shared stream table.
Rules on Users' Access Privileges
Permission Levels
For the privileges applied to databases and tables, the permission scope can be global (*), database or table.
For the privilege VIEW_EXEC, its permission scope can be global (*), namespace (<namespace>::*), or function view.
When you grant
, deny
, or
revoke
privileges to an object, the system first checks
whether a permission state at a higher level exists.
-
If it does not exist, privileges on the objects at the same level as the target are revoked first. Then the
grant
,deny
, orrevoke
operation is executed. -
If there exists
-
"allow" state at a higher level, only
deny
operation takes effect. -
"deny" state at a higher level, then
grant
,deny
, orrevoke
does not take effect.
-
Examples
Example 1. First deny
at table level, then
grant
at database level:
deny(`userA, TABLE_READ, "dfs://testdb/pt") grant(`userA, DB_READ, "dfs://testdb")
Suppose you first deny
userA from accessing the table "pt" in
the database "dfs://testdb". When you grant
userA with the
DB_READ privilege on testdb at database level, the system will:
-
Revoke READ privileges on all tables in the database;
-
Clear the "deny" state of userA to table pt;
-
Grant userA READ access to all tables in the database.
Now userA's permission state for table pt is "allow".
Example 2. First grant
at database level, then
revoke
at table level:
grant(`userA, DB_READ, "dfs://testdb") revoke(`userA, TABLE_READ, "dfs://testdb/pt")
Suppose you first grant
userA with the DB_READ privilege on
testdb at database level. revoke
or grant
operation at table level does not take effect as a higher-level privilege
already exists.
grant
at database level, then deny
at table
level:
grant(`userA, DB_READ, "dfs://testdb") deny(`userA, TABLE_READ, "dfs://testdb/pt")
In this case, the deny operation can take effect. The permission state of userA is "deny" on table pt, and "allow" on other tables in the database.
Rules
A user's access privileges are determined by its own access privileges and the
privileges of the groups to which the user belongs. You can check these
privileges with getUserAccess
and
getGroupAccess
.
For example, this is how userA's read privilege on table pt in database testdb is determined:
The system searches the privileges of userA and its groups to check whether there exists "deny" state at global, database testdb and table pt level.
-
If "deny" state exists, userA cannot read table pt.
-
If "allow" state exists (without "deny" state), userA is allowed to read table pt.
-
If neither of the state exists, the permission state of userA to pt is "none"
Access-Related Functions
The following functions can only be executed by administrators:
-
access control: deny, grant, revoke, addAccessControl
-
user control: resetPwd, createGroup, deleteGroup, createUser, deleteUser, addGroupMember, deleteGroupMember
Privileges Required by DDL/DML Operations
Users with the DB_OWNER (or TABLE_OWNER) privilege must be the creator of the database (or table).
Function | Required Privileges (Before version 1.30.21/2.00.9) | Required Privileges (Since version 1.30.21/2.00.9) |
---|---|---|
database/createDB (create a database) | DB_MANAGE, DB_OWNER | DB_OWNER |
dropDatabase | DB_MANAGE, DB_OWNER | DB_MANAGE, DB_OWNER |
createDimensionTable | DBOBJ_CREATE, DB_OWNER | DBOBJ_CREATE, DB_OWNER, DB_MANAGE |
dropTable | DBOBJ_DELETE, DB_OWNER | DBOBJ_DELETE, DB_MANAGE, DB_OWNER |
createPartitionedTable | DB_MANAGE, DB_OWNER | DBOBJ_CREATE, DB_MANAGE, DB_OWNER |
renameTable | DBOBJ_DELETE, DB_OWNER | TABLE_OWNER or TABLE_READwithDBOBJ_CREATE, DB_OWNER, or DB_MANAGE |
loadTable | TABLE_OWNER, TABLE_READ, VIEW_EXEC | TABLE_READ, TABLE_OWNER, VIEW_EXEC |
addPartitions | DB_MANAGE, DB_OWNER | DB_MANAGE, DB_OWNER |
dropPartition | DBOBJ_DELETE, DB_OWNER | If deleteSchema=false: DB_MANAGE, DB_OWNER, DB_DELETE, TABLE_DELETE (global)If deleteSchema=true:DB_MANAGE, DB_OWNER |
addColumn | TABLE_OWNER, DBOBJ_CREATE | TABLE_OWNER or TABLE_READwithDBOBJ_CREATE, DB_OWNER, or DB_MANAGE |
dropColumns! | DB_MANAGE, DB_OWNER | TABLE_OWNER or TABLE_READwithDB_MANAGE, DB_OWNER, or DBOBJ_DELETE |
rename! | DB_MANAGE, DB_OWNER | TABLE_OWNER or TABLE_READwithDBOBJ_CREATE, DB_OWNER, or DB_MANAGE |
replaceColumn! | TABLE_OWNER or TABLE_READwithDBOBJ_CREATE, DB_OWNER, or DB_MANAGE | |
setColumnComment | TABLE_OWNER, DBOBJ_CREATE | TABLE_OWNER or TABLE_READwithDBOBJ_CREATE, DB_OWNER, or DB_MANAGE |
truncate | TABLE_OWNER, TABLE_WRITE, VIEW_EXEC | TABLE_WRITE, TABLE_OWNER, VIEW_EXEC |
upsert/SQL update | TABLE_OWNER, VIEW_EXEC | TABLE_WRITE, TABLE_OWNER, VIEW_EXEC |
SQL delete | TABLE_OWNER, TABLE_WRITE, VIEW_EXEC | TABLE_WRITE, TABLE_OWNER, VIEW_EXEC |
Note: When a user creates a table, he/she is granted the TABLE_OWNER privilege which cannot be transferred.
Examples
-
Log in the system as an administrator, create datebase dfs://db1 and create table pt1 in the datebase.
login(`admin, `123456); n=1000000 ID=rand(10, n) x=rand(100, n) t1=table(ID, x) db=database("dfs://db1", HASH, [INT, 2]); pt1 = db.createPartitionedTable(t1, `pt1, `ID) pt1.append!(t1)
-
Create a group "football" with 3 members: EliManning, JoeFlacco, and DeionSanders. All members of the group "football" can read the table dfs://db1/pt1, and user DeionSanders can create or delete databases.
createUser(`EliManning, "AB123!@") createUser(`JoeFlacco, "CD234@#") createUser(`DeionSanders, "EF345#$") createGroup(`football, `EliManning`JoeFlacco`DeionSanders) grant(`football, TABLE_READ, "dfs://db1/pt1") grant("DeionSanders", DB_MANAGE);
The user EliManning cannot create databases:
login(`EliManning, "AB123!@"); db=database("dfs://db2", HASH, [INT, 2]); db = database("dfs://db2", HASH, [4,2]) => Not granted to create or delete databases.
-
Add 2 new members to the group "football", and remove JoeFlacco from the group. Use getUsersByGroupId to get a list of the members of the group "football".
login(`admin, `123456); createUser(`AlexSmith, "GH456$%") createUser(`NickFoles, "IJ567%^") addGroupMember(`AlexSmith`NickFoles, `football) deleteGroupMember(`JoeFlacco, `football) getUsersByGroupId(`football); // output ["AlexSmith","DeionSanders","EliManning","NickFoles"]
-
Create a group "baseball" with 3 members: CliffLee, ShoheiOhtani, and DeionSanders.
createUser(`CliffLee, "GH456$%") createUser(`ShoheiOhtani, "IJ567%^") createGroup(`baseball, `CliffLee`ShoheiOhtani`DeionSanders)
DeionSanders belongs to 2 groups. Use getGroupsByUserId to get a list of the groups that DeionSanders belongs to.
getGroupsByUserId(`DeionSanders); // output ["football","baseball"]
Set the following privileges:
grant(`baseball, DBOBJ_CREATE, "dfs://db1") deny(`baseball, TABLE_READ, "dfs://db1/pt1") deny(`baseball, DB_MANAGE);
Group "football" can read the table dfs://db1/pt1, but group "baseball" is denied this privilege. Therefore DeionSanders does not have this privilege.
login(`DeionSanders, "EF345#$"); t = loadTable("dfs://db1","pt1"); t = loadTable("dfs://db1", "pt1") => Not granted to read table dfs://db1/pt1
Although DeionSanders has been granted the privilege to create or delete databases in step 2, group "baseball" is denied the privilege in step 4. As a member of the group "baseball", DeionSanders is also denied this privilege. DeionSanders can get back this privilege if he leaves the team "baseball", or if the denial of the privilege to team "baseball" is revoked, or if team "baseball" is granted the privilege.
-
Grant a function view privilege to group "baseball" to count the number of rows of table dfs://db1/pt1.
login(`admin, `123456); def countPt1(){ return exec count(*) from loadTable("dfs://db1","pt1") } addFunctionView(countPt1) grant("baseball", VIEW_EXEC, "countPt1");
Although a "basecall" group member cannot read table dfs://db1/pt1, he can execute the view function countQuotes to get the number of rows of table countPt1. Log in as User ShoheiOhtani, execute the following script:
login(`ShoheiOhtani, "IJ567%^"); countPt1(); //output 1000000
-
Grant a function view privilege to group "baseball" to calculate the maximum value of a specified column of table dfs://db1/pt1 conditional on the value of ID.
login(`admin, `123456); def getMax(column, idValue){ return exec max(column) from loadTable("dfs://db1","pt1") where id=idValue } addFunctionView(getMax) grant("baseball", VIEW_EXEC, "getMax");
User CliffLee can log in and execute the function
getMax
.login(`CliffLee, "GH456$%") getMax(x, 6); // output 99
-
The administrator grants the privilege of DB_OWNER to user MitchTrubisky:
login(`admin, `123456); createUser(`MitchTrubisky, "JI3564^") grant(`MitchTrubisky,DB_OWNER);
MitchTrubisky creates the table dfs://dbMT/dt and allows user NickFoles to read from the table:
login(`MitchTrubisky, "JI3564^"); db = database("dfs://dbMT", VALUE, 1..10) t=table(1..1000 as id, rand(100, 1000) as x) dt = db.createDimensionTable(t, "dt").append!(t) grant(`NickFoles, TABLE_READ, "dfs://dbMT/dt");
User NickFoles can log in and read data from dfs://dbMT/dt to conduct calculation:
login(`NickFoles, "IJ567%^") select max(x)-min(x) from loadTable("dfs://dbMT"gi, "dt"); // output 99