Database Security and User Access Control

DolphinDB access control system uses groups to manage users with the same access privileges. A user can belong to 0, 1 or multiple groups; a group can have 0, 1 or multiple users.

A user's access privileges are determined by her own access privileges and the privileges of the groups that she belongs to. You can grant or deny the access of a user or a group (See Section Rules on Users Access Privileges).

Users can log in (out) with command login (logout). Users can change the login password with changePWd.

There are 2 types of administrators in DolphinDB: super admin and admin.

When a DolphinDB cluster is started for the first time, it creates a super admin with user ID of "admin" and password of "123456". This super admin has all access privileges mentioned in Section Access Privilege Types and cannot be deleted.

The super admin can create or delete users (including admin) and add them to any group. By default, newly created users don't have any access privileges. However, administrators can grant/deny/revoke access to users or groups. Administrators can use resetPwd to modify the login password.

Functions or commands that an administrator can use:

• addGroupMember: Add a user to a group or multiple groups
• createUser: Create a user
• deleteUser: Delete a user
• createGroup: Create a group
• deleteGroup: Delete a group
• deleteGroupMember: Remove a user from a group or multiple groups, or remove multiple users from a group
• getUserAccess: Obtain the privileges for users, without taking into account the privileges for the groups the users belong to
• getUserList: Obtain a list of user names other than the administrators
• getGroupList: Obtain a list of group names
• resetPwd: Reset a user's password

For detailed usage of the above commands and functions, please see DolphinDB Function Reference.

The following table summarises the difference among super admin, admin and regular user.

Please note that when discussing the rules below, a user is viewed as belonging to a special group with herself as the only member. The access privileges are determined by the privileges of the groups that she belongs to.

Different groups may specify different access privileges to the same user. For these situations the following rules apply:

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

For the second situation, there are 2 ways for an administrator to grant the privilege to the user:

• Execute grant in all groups where the user's privilege is denied.
• revoke the denial in all groups where the user's privilege is denied:
  • the user then has the privilege if it has been granted in other groups;
  • if it has not been granted in other groups, the user can have this privilege after an administrator grants it to her.

The following example describes how the privileges are determined when a user's privileges conflict with that of a group to which she belongs:

createUser("user1","123456")  
createUser("user2","123456")  
createGroup("group1")  
createGroup("group2")  
addGroupMember(["user1","user2"],"group1")
addGroupMember(["user1","user2"],"group2")
grant("user1",TABLE_READ,"*")  
deny("group1",TABLE_READ,"dfs://db1/t1")  
deny("group2",TABLE_READ,"dfs://db1/t2")

Now user1 can read any table except "dfs://db1/t1" and "dfs://db1/t2".

The following script shows the situation when the privileges of the users' groups conflict based on the above example:

grant("user2",TABLE_WRITE,"*")  
deny("group1",TABLE_WRITE,"*")  
grant("group2",TABLE_WRITE,"dfs://db1/t2")  

Now user1 and user2 cannot write to any table.

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 these databases, create or delete tables or partitions in these databases, grant/deny/revoke the following privileges of other users: TABLE_READ, TABLE_WRITE, DBOBJ_CREATE, DBOBJ_DELETE
8. SCRIPT_EXEC: execute scripts
9. TEST_EXEC: execute testing scripts

Note:

• All the 9 types of access privileges are applicable to DFS databases and tables;
• Only TABLE_READ and TABLE_WRITE are applicable to shared in-memory tables, stream tables and streaming engines.

In DFS databases, administrators can set the users' privileges with grant/deny/revoke. You can specify one of the 9 access privileges in Section Access Privilege Types for the parameter accessType in these commands. The parameter objs indicates the object to which it is applied. It must be specified when accessType is specified as one of the first 5 privileges.

The following 4 examples illustrate how to set access privileges.

Example 1

Log in as "admin":

login(`admin, `123456)

Create user NickFoles with password AB123!@. Users can change their own password with command changePwd.

createUser("NickFoles","AB123!@")  

Grant the privilege to read any DFS table to user "NickFoles":

grant("NickFoles",TABLE_READ,"*") 

Deny user "NickFoles" the privilege to create or manage databases:

deny("NickFoles",DB_MANAGE)   

Create group "SBMVP", and add "NickFoles" to the group:

createGroup("SBMVP","NickFoles")  

Grant the privilege to create tables in databases "dfs://db1" and "dfs://db2" to group "SBMVP":

grant("SBMVP",DBOBJ_CREATE,["dfs://db1","dfs://db2"])    

Now, user "NickFoles" has the following access privileges:

• Can read all tables.
• Cannot create or delete databases.
• Can create tables in databases "dfs://db1" and "dfs://db2".

Example 2

You can add users to a group to set the access privileges conveniently:

createUser("EliManning", "AB123!@")  
createUser("JoeFlacco","CD234@#")  
createUser("DeionSanders","EF345#$")  
createGroup("football", ["EliManning","JoeFlacco","DeionSanders"])  
grant("football", TABLE_READ, "dfs://TAQ/quotes")  
grant("DeionSanders", DB_MANAGE)  

The example creates 3 users "EliManning", "JoeFlacco", and "DeionSanders" and 1 group "football". All these users belong to the group. Grant the group the privilege to read table "dfs://TAQ/quotes", and grant user "DeionSanders" the privilege to create or delete databases.

Example 3

We can use command grant or deny to grant or deny certain privileges on all objects (*). For example, to grant user "JoeFlacco" the privilege to read all DFS tables:

grant("JoeFlacco",TABLE_READ,"*")  

After we grant or deny privileges on all objects to a user or a group, we can only revoke the privilege on all objects. If we try to revoke the privilege on any specific object, the operation would not be effective:

revoke("JoeFlacco",TABLE_READ,"dfs://db1/t1")

The command above has no effect.

revoke("JoeFlacco",TABLE_READ,"*")

The command above successfully revokes the privilege of "JoeFlacco" to read all DFS tables.

Similarly, after we grant or deny certain privilege to a group, we can revoke the privilege from the group, but we cannot revoke the privilege from a group member.

Example 4

If a user possesses the DB_OWNER privilege, she can grant other users the privileges on the databases created by her.

Create two users "CliffLee" and "MitchTrubisky", and grant "MitchTrubisky" the privilege DB_OWNER.

createUser(`CliffLee, "GH456$%")
createUser(`MitchTrubisky, "JI3564^")
grant(`MitchTrubisky,DB_OWNER);

Log in as "MitchTrubisky". Create table "dfs://dbMT/dt" and grant "CliffLee" the privilege to read 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(`CliffLee, TABLE_READ, "dfs://dbMT/dt");

Now "CliffLee" is granted with the permission to access the data table "dfs://dbMT/dt" created by "MitchTrubisky".

Administrators can set the TABLE_READ and TABLE_WRITE privileges for the shared in-memory tables in the same manner as that for a DFS database.

Create an admin "MitchTrubisky"

createUser("MitchTrubisky","JI3564^",,true)

Log in as "MitchTrubisky". Create an in-memory table and share it as "st1". Grant user "CliffLee" the privilege to read the table.

login(`MitchTrubisky, "JI3564^")
share table(1:0, `time`sym`volume, [TIMESTAMP, SYMBOL, INT]) as st1
grant("CliffLee", TABLE_READ, "st1")

Check the column TABLE_READ_allowed returned by getUserAccess. Now user "CliffLee" has the privilege to read the table "st1".

getUserAccess("CliffLee")

Please note that before "MitchTrubisky" uses command grant (or deny) on st1, the table can be read and written to by any user. However, after the table permission is modified, access control is applied and only the administrators or the table creator "MitchTrubisky" have the privileges to read or write to the table.

You can also revoke the granted privileges:

revoke("CliffLee", TABLE_READ, "st1")

You can execute addAccessControl to apply access control:

share table(1:0, `time`sym`volume, [TIMESTAMP, SYMBOL, INT]) as st2
addAccessControl(shareTable2)

There are 2 ways to enable HTTPS:

• Add "enableHTTPS=true" in the configuration file of the cluster controller (controller.cfg);

• Add -enableHTTPS true in the command line when starting the cluster controller.

You need to install server authentication certificate at each server in DolphinDB for secure connections. There should be a certificate on the controller and each of the agent nodes. Data nodes use the certificate located at the agent node on the same server.