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 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}*)
  • 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

VIEW_OWNER

Permission for regular users to create function views.

Only administrators can grant/deny/revoke this access. After users have been granted this permission, they can perform the following operations:

  • Add function views with addFunctionView. The users will automatically have the VIEW_EXEC permission for the views after creation.

  • Drop the views they have created with dropFunctionView.

  • grant/deny/revoke the VIEW_EXEC permission on the views they created to other users.

  • Check all the views they have created with getFunctionViews().

2.00.10.4/1.30.22.4
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 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
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 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:

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

  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