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.

Note: 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	Notes
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	New in Version 2.00.9/1.30.21
TABLE_UPDATE	global(*), table(dfs://db/tb)	Update tables	New in Version 2.00.9/1.30.21
TABLE_DELETE	global(*), table(dfs://db/tb)	Delete tables	New in Version 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	New in Version 2.00.9/1.30.21
DB_WRITE	global(*), database(dfs://db)	Write to tables in specific databases	New in Version 2.00.9/1.30.21
DB_INSERT	global(*), database(dfs://db)	Insert into tables in specific databases	New in Version 2.00.9/1.30.21
DB_UPDATE	global(*), database(dfs://db)	Update tables in specific databases	New in Version 2.00.9/1.30.21
DB_DELETE	global(*), database(dfs://db)	Delete tables from specific databases	New in Version 2.00.9/1.30.21
VIEW_EXEC	global(*), function view(viewName)	Execute specific function views
DB_OWNER	global(* or unspecified), grant databases with specific prefix (dfs://{dbPrefix}*)	- Database level: create databases; delete databases created by themselves - Table level (in databases owned by themselves): create/delet/rename tables; add/delete partitions; add/delete columns 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
DB_MANAGE	grant databases with specific prefix (dfs://{dbPrefix}*)	Manage specific databases, including: - Database level: delete databases - Table level: create/delete/rename tables; add/delete partitions; add/delete/rename/replace columns
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	New in Version 2.00.9/1.30.21.
TASK_GROUP_MEM_LIMIT	memory size (in GB)	Set the memory limit for a task group	New in Version 2.00.9/1.30.21.

Note: 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 1.30.15/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 1.30.21/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

Based on the application scope, permissions are divided into three levels: global (*), database and table.

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, or revoke 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, or revoke 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”

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
createTable	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_READ with DBOBJ_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_READ with DBOBJ_CREATE, DB_OWNER, or DB_MANAGE
dropColumns!	DB_MANAGE, DB_OWNER	TABLE_OWNER or TABLE_READ with DB_MANAGE, DB_OWNER, or DBOBJ_DELETE
rename!	DB_MANAGE, DB_OWNER	TABLE_OWNER or TABLE_READ with DBOBJ_CREATE, DB_OWNER, or DB_MANAGE
replaceColumn!		TABLE_OWNER or TABLE_READ with DBOBJ_CREATE, DB_OWNER, or DB_MANAGE
setColumnComment	TABLE_OWNER, DBOBJ_CREATE	TABLE_OWNER or TABLE_READ with DBOBJ_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 :doc:/FunctionsandCommands/FunctionReferences/g/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);

["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);

["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();
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);
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.createTable(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");
99

User Access Control

Users and Groups

Access Privilege Types

Rules on Users’ Access Privileges

Permission Levels

Examples

Rules

Access-Related Functions

Privileges Required by DDL/DML Operations

Examples