User Access Control

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.

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:

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.

The following functions can only be executed by administrators:

• access control: deny, grant, revoke, addAccessControl

• user control: resetPwd, createGroup, deleteGroup, createUser, deleteUser, addGroupMember, deleteGroupMember

Users with the DB_OWNER (or TABLE_OWNER) privilege must be the creator of the database (or table).

Note: When a user creates a table, he/she is granted the TABLE_OWNER privilege which cannot be transferred.

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

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();
   //output
   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);
   // output
   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.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