User and Access Right in Virtual DataPort

Denodo Virtual DataPort distinguishes two types of users:

  • Administrators. They can create, modify and delete databases without any limitation. Likewise, they can also create, modify and delete users. When the server is installed, a default administrator user is created with user name admin and password admin. There must always be at least one administrator user.

  • Normal users. They cannot create, modify or delete users or databases. Administrator users can grant them connection, read, create or write privileges to one or several databases or to specific views contained in them.

    An administrator can promote a normal user to administrator of a database of one or more databases, which means that this user will be able to perform administration tasks over these databases.

    A normal user with the role serveradmin can perform the same actions as an administrator.

A “Normal user” can have “Roles”, which are a set of access rights over databases and their views and stored procedures. Roles allow administrators to manage user privileges easily because by changing the privileges assigned to a role, they change the privileges of all the users that “belong” to that role.

Types of Access Rights

Virtual DataPort access rights are applied to a specific user or a role, to delimit the tasks they can perform over databases, views and stored procedures.

The following access rights only affect “Normal” users. That is because normal users promoted to local administrators of a given database can perform any task within that database and administrators can perform any task within the entire Server.

An administrator with the role “assignprivileges” can grant privileges to a user or a role, over an entire database or over specific views or stored procedures of a database.

You can grant the following privileges to a user or a role, over a database:

Connection Privilege

Users with the Connection privilege can connect to this database.

To revoke all the privileges of a user temporarily, revoke her roles and the connection privileges over all the databases.

Note

Even if a user is not allowed to connect to a database, she can query the views of that database over which she has read access, when she is connected to another database. However, as it cannot connect to that database, it will not be able to create, edit or drop its views.

Create Privilege

Users with the Create privilege can do the following:

  • Create data sources, views, stored procedures, Web services, etc. I.e. execute CREATE statements. If the user wants to create a derived view, she also needs to have Read access over the entire database or at least, over the views involved in the new view.

    The user will be able to manage all the elements it creates or owns.

  • Deploy and redeploy the Denodo Web services that they created.

  • Deploy and redeploy the auxiliary Web services of the widgets that they created.

Read Privilege

Users with the Read privilege over a database can do the following:

  • View the list of views and stored procedures of the database. I.e. execute LIST statements.

    Users that are not administrators or local administrators of the database cannot see the existing data sources, Web services or widgets, unless they are the owners of these elements.

  • View the schema of the views and stored procedures.

  • View the VQL of the following elements:

    • Derived views
    • Data sources, base views, Web services and widgets, but only if they are owned by the user.
  • Query any view/stored procedure of the database. I.e. execute SELECT/CALL statements.


If you grant this privilege over a specific views or stored procedure instead of a database, the user will be able to do the following:

  • The view/procedure will be listed in the “Elements Tree” of the administration tool.

  • View the schema of the view/procedure.

  • View the VQL of the view/procedure.

    To view the VQL of a base view, the user has to be the creator of the base view.

    To view the VQL of a derived view, the user also needs the “Read” privilege over the subviews of the view.

  • Query the view/procedure. I.e. execute SELECT/CALL statements.


You can grant more fine-grained READ privileges over specific views:

Metadata Privilege

Users with the Metadata privilege can do the following:

  • Open the dialogs Edit and Options of the views. However, without the create privilege, the user will not be able to modify the view.

  • Open the Tree view and Data lineage of the views. Note that the Tool will not display information about any data source unless the user owns them.

  • Obtain the execution plan of any query, without actually running the query.

    To see the query plan of a query, open the Execute dialog of the view and click Query plan. Alternatively, open the VQL Shell and prefix the query with DESC QUERYPLAN. For example,

    DESC QUERYPLAN
      SELECT count(*), sum(total)
      FROM invoice
      GROUP BY billing_state
    

If you grant the privilege metadata over a set of views/procedures instead of a database, the differences are:

  • The user will only have access to these views/procedures, not all the database.
  • The query plan will only show information about the views the user has read access. The Tool will not show anything related to data sources. E.g. the Tool will not display the SQL queries delegated to the databases.

The main usage of this privilege are:

  1. Allow developers to connect to the production servers and troubleshoot issues, without seeing real-live data. For example, to see the full execution plans of queries.
  2. Let us say that you store all the data sources and their base views on a central database and each project is created in its own database. Thanks to the metadata privilege, the developers of each project will be able to see the full query plan, not just part of it.

Write Privilege

If you grant the privilege Write to a user/role, implicitly you are granting Read.

Users with the Write privilege can do the following:

  • Delete the views and stored procedures of the database. A user can delete the data sources, Web services and widgets that she has created, but not the ones created by other users.
  • Modify the views and stored procedures of the database. Users can also alter their own data sources, Web services and widgets, but not modify the ones created by other users.
  • Execute INSERT, UPDATE and DELETE statements on views of this database.
  • Move elements across the existing folders of the database, except if that element is another folder.
  • Undeploy the Denodo Web services that they created.
  • Undeploy the auxiliary Web services of the widgets that they created.

If you grant this privilege over a specific view or stored procedure instead of a database, the user will be able to do the following:

  • Delete the view/procedure. If there are other elements that depend on the one being deleted, the user needs to have Write access over these other elements as well because they will be deleted on cascade.
  • Modify the view/procedure. The user can only modify a derived view if it has the “Read” privilege over the subviews of the view.
  • Move the view/procedure across the existing folders of the database.
  • Execute INSERT, UPDATE and DELETE statements over the view/procedure.

File Privilege

Users with the File privilege can browse through the directories listed in the dialog File privilege of the wizard Server Configuration.

This privilege is disabled by default and not visible in this dialog until you enable it. The section The FILE Privilege explains how to enable this privilege and how it affects users.

Admin Privilege

This privilege can only be assigned over an entire database. Users with this privilege have the privileges Connect, Create, Metadata, Read and Write over all the elements of that database. Besides, they can do the following tasks:

  • Set the configuration parameters of the database: I18N, cache, swap, etc.
  • Edit the description of the database.
  • Grant / revoke privileges to normal users over the elements of the database. It cannot grant the Admin privilege to other users.
  • Grant / revoke privileges to roles over the elements of the database.
  • Create / rename / drop folders.
  • List, deploy, redeploy and undeploy all the Denodo Web services and the auxiliary Web services of widgets.

A user with this privilege cannot do the following:

  • Create / delete users
  • Create / delete roles
  • Grant / revoke roles to users or other roles
  • Change users’ password
  • Create / drop databases
  • Grant Admin privileges to other users.

Insert, Update and Delete Privileges

Users with the Insert privilege can execute INSERT statements over the view.

Users with the Update privilege can execute UPDATE statements over the view.

Users with the Delete privilege can execute DELETE statements over the view.

These privileges are not applicable to stored procedures.

You can only grant these privileges over individual views, not databases. If you want to grant a user to privilege to run these statements over all the views of a database, grant her the Write privilege.

Column Privileges

When you grant READ access over a view to a user or a role, this user or the users that “belong” to this role, can query this view to obtain all its data. However, if you want some users to project only some of the columns of a view, you can use “Column privileges”.

For example, you can give a user the permission to execute a query that projects the fields iinc_id and summary of the view internet_inc, but forbid her from projecting the other fields of the view.

These privileges do not affect users that are global administrators or administrators of the database to which the view belongs. I.e. these users can project any column of the view regardless of their privileges.

These privileges do not affect the write/update/delete operation. They only affect the SELECT ones.

Row Restrictions

When a user or a role has READ access over a view, you can add restrictions to the rows returned to that user or users that belong to that role. That way, when certain users send a query to a view, they will only obtain the rows that match a certain criteria and/or some of the fields of some rows will be masked.

You can also specify that the results will only be filtered when the query ask for a list of specific fields.

For example, we can create a row restriction over a view employee to allow the user A to obtain only the rows that match the restriction position <> 'manager' when the query includes the salary attribute in the SELECT clause.

If you want to create a row restriction that takes into account more complex information to decide if the result of the query is filtered or not, you can develop a custom policy (see section Custom Policies of the Developer Guide).

These restrictions do not affect users that are global administrators or administrators of the database to which the queried view belongs. I.e. no row will be rejected or masked regardless of their privileges.

These restrictions do not affect the write/update/delete operation. They only affect the SELECT ones.

Roles

A role is a set of access rights that we can grant to users. The benefit of assigning roles to users instead of assigning privileges is that the management of permissions is easier. If you change the privileges of a role, the changes are applied to all the users that “belong” to that role. Without roles, you would have to edit the privileges of each user.

A user can have more than one role assigned and her “effective permissions” will be the union of the permissions assigned by each role. For example, if there is a role A that grants read permission over the database “admin” and another role B that grants read permission over the database “tests”, a user with the roles A and B will have read privileges over the databases “admin” and “tests”.

In addition, you can assign roles to other roles. This is called “Role inheritance”. For example, if you have the following roles:

  • A role vdp_developer with the privileges CONNECT, READ, CREATE and WRITE over the database admin.
  • A role itpilot_developer with the privileges CONNECT, READ, CREATE and WRITE over the database itpilot.

You can create a role denodo_developer that has assigned the roles vdp_developer and itpilot_developer to it. The users with this new role will have the privileges CONNECT, READ, CREATE and WRITE over the databases admin and itpilot.

The permissions assignable to a role are the same that we can assign to a user.

Virtual DataPort defines the following special roles:

  1. serveradmin is equivalent to being an administrator user of the Virtual DataPort server, except that it does not grant the privilege of connecting to Virtual DataPort via JMX. That is, a user with this role can manage databases, change settings of the Server, etc. A user with this role also needs the role “assignprivileges” (see below) to manage the privileges of users and roles.

  2. jmxadmin grants the privilege of using the Query Monitor of the Administration Tool and connecting to Virtual DataPort via JMX.

    You need this privilege to monitor the Server using JMX tools such as Oracle VisualVM, Oracle Java Mission Control, Nagios, etc.

  3. selfserviceadmin grants the privilege of modifying the settings of the Self Service Administration Tool.

  4. The scheduler_admin role is used by the Scheduler Administration Tool. The users that have this role assigned can perform any task in the Scheduler Administration Tool. This role is created during the installation process and cannot be modified or deleted.

    See more about this in the section Permissions of the Scheduler Administration Guide.

  5. assignprivileges grants the privilege of granting/revoking privileges to other users.

    Note

    Without this role, a user cannot grant/revoke privileges to the users/roles, not even an administrator.

    Take the following into account:

    • New administrator users have this role by default but you can revoke it from them.
    • This role cannot be modified. You cannot grant privileges or roles to it. This is why it is not listed in the “Role Management” panel, but it is listed in the “Assign roles” dialogs.
    • You can only assign it to administrators or to users that are administrators of at least one database.
    • An non-administrator user with this role can only modify the privileges of the databases for which it is an administrator.
    • Only administrators with this role can grant/revoke roles to users or other roles.
    • Only administrators with this role can modify the description of a role.

These roles are created during the installation process and cannot be modified nor deleted.