User Access Control

User access control in DolphinDB only applies to a cluster.

Administrators, Users and Groups

An administrator can create other administrators, users and groups. A user that is not an administrator cannot create other users or groups.

When a DolphinDB cluster is started for the first time, it creates an administrator with user ID of “admin” and password of “123456”. This administrator has all access privileges and cannot be deleted. Other administrators do not have any access privilege when they are newly created.

An administrator can grant, revoke or deny access privileges to other administrators, users and groups.

A user can belong to 0, 1, or multiple groups. Adding a user to a group or removing a user from a group may affect the access privileges of the user.

Access Privileges

DolphinDB supports the following 9 types of access privileges:

1. TABLE_READ: read tables

2. TABLE_WRITE: write to tables

3. DBOBJ_CREATE: create database objects (tables) in a database

4. DBOBJ_DELETE: delete database objects (tables) in a database

5. VIEW_EXEC: execute view functions

6. DB_MANAGE: create and delete databases.

7. DB_OWNER: create databases. For the databases she created, she can delete the databases, create or delete tables, create or delete partitions, grant/deny/revoke the following privileges of other users: TABLE_READ, TABLE_WRITE, DBOBJ_CREATE, DBOBJ_DELETE.

8. SCRIPT_EXEC: execute script

9. TEST_EXEC: execute testing script

To set access privileges, use commands grant, deny and revoke . The 8 access privileges above are used as values for the parameter accessType in these commands. To set the first 5 privileges, we also need to specify the objects that these privileges apply to.

A user’s access privileges are determined by her own access privileges and the privileges of the groups that she belongs to.

  • If a user is granted a privilege in at least one group and is not denied this privilege in any other group, then the user has this privilege.

  • If a user is denied a privilege in at least one group, then the user does not have this privilege, even though she may have been granted the privilege in other groups. The user can have this privilege if an administrator uses revoke or grant to neutralize all the denials regarding this privilege and the user is granted this privilege in at least one group.

When a user schedules a job, she does not need to have access privileges on the objects that are involved. However, if the user does not have privileges to access the objects when the scheduled job is set to be executed, the scheduled job will not be executed.

Streaming-related access management (since 1.30.15/2.00.3):

1. Administrators can grant, deny and revoke users’ privileges to READ and WRITE shared stream/in-memory tables and streaming engines.

2. A user must have the TABLE_READ privilege to a stream table before subscribing to it.

3. Both TABLE_READ and TABLE_WRITE privileges are required to write to a shared stream table.

4. When the publisher and the subscriber of a stream table are not on the same node, specify the table (objs) as “nodeAlias:tableName”. For example, deny(`amy, TABLE_READ,"DFS_NODE1:st"), where DFS_NODE1 is the node alias corresponding to the stream table and st is the name of the stream table.

5. Only the creator of a stream table or administrator can delete the table.

6. To revoke access privileges that were previously granted, it is recommended to use the command revoke.

7. A user must have the TABLE_WRITE privilege to a table before saving subscribed streaming data to it.

Examples

1. 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)

2. 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.

3. 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);

["AlexSmith","DeionSanders","EliManning","NickFoles"]

4. 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://db2")
$ 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.

5. 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

6. 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

7. 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", "dt");

99