User and Access Rights in Virtual DataPort¶
Users¶
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 passwordadmin
. 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, Execute, 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.
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 execute permission over the database “admin” and another role B that grants execute permission over the database “tests”, a user with the roles A and B will have execute 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 role:
A role
vdp_developer
with the privilegesCONNECT
,EXECUTE
,CREATE
andWRITE
over the databaseadmin
.
You can create a role denodo_developer
that has assigned the role
vdp_developer
to it. The users with this new role will have the
privileges CONNECT
, EXECUTE
, CREATE
and WRITE
over
the admin
database.
The permissions assignable to a role are the same that we can assign to a user.
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.
For the particular case of views, Denodo allows to define privileges at different levels of granularity:
General or “coarse-grained” view privileges: To delimit whether a user/role can execute, edit, insert, update, delete on a certain view, as well as see its metadata as a whole.
Fine-grained view privileges: It allows specifying what particular data of a view should be visible to a user/role. There are different kinds of find-grained privileges:
Column restrictions: Limit the columns a user/role can use on a query
Row restrictions: Limits the rows that are visible to a user/role, allowing to mask values and defining custom policies for complex criteria.
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.
Privileges at Database Level¶
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.
Create Privilege¶
Users with the Create privilege can do the following:
Create and drop 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 Execute access over the entire database or at least, over the views involved in the new view.
To allow users to create temporary tables but not other types of elements, grant the role create_temporary_table
to these users.
When a user create an element, by default, METADATA and WRITE privileges are automatically assigned. It only happens for users that exists in Virtual DataPort (it does not happen for Kerberos/LDAP/SAML/OAUTH/DnDToken authenticated users).
Create Data Source Privilege¶
Users with the Create Data Source privilege can do the following:
Create and drop data sources. I.e. execute
CREATE DATASOURCE
statements.
Create View Privilege¶
Users with the Create View privilege can create/drop views, stored procedures and types. I.e. execute CREATE WRAPPER
, CREATE TYPE
, CREATE WRAPPER
and CREATE PROCEDURE
statements. This includes materialized and temporary tables.
In addition, they need the privilege “METADATA” over the views directly involved in the new view, or their database.
To allow users to create temporary tables but not other types of elements, grant these users the role create_temporary_table
.
Create Data Service Privilege¶
Users with the Create Data Service privilege over a database can do the following:
Create and drop web services (REST and SOAP) and JMS listeners. I.e. execute
CREATE REST/SOAP WEBSERVICE
andCREATE LISTENER JMS
statements.
Create Folder Privilege¶
Users with the Create Folder privilege can do the following:
Create and drop folders i.e. execute
CREATE FOLDER
statements.
Execute Privilege¶
Users with the Execute privilege over a database can do the following:
Perform introspection of data sources.
Query any view/stored procedure of the database. I.e. execute
SELECT
/CALL
statements.
If you grant this privilege to a user or role, over a specific views or stored procedure instead of a database, the user will be able to do the following:
Query the view/procedure. I.e. execute
SELECT
/CALL
statements.
You can grant more fine-grained EXECUTE privileges over specific views:
Column privileges (see section Column Privileges)
Row restrictions (see section Row Restrictions)
Metadata Privilege¶
Users with the Metadata privilege can do the following:
View the list of views and stored procedures of the database. I.e. execute
LIST
statements.View the schema of the views and stored procedures.
Open the dialogs Edit and Options of the views. However, without the write privilege, the user will not be able to modify the view.
Open the Tree view and Data lineage of the views.
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 Execute 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 goals of this privilege are:
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.
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¶
Users with the Write privilege over a database can do the following:
Modify and delete the data sources, views, web services (REST and SOAP) and stored procedures of the database.
Move elements across the existing folders of the database, except if that element is another folder.
Deploy, redeploy and undeploy web services
View the VQL of data sources, views, stored procedures, JMS listeners and web services (REST and SOAP). The Write privilege is not required if the user has the role metadata_export (it only needs the metadata privilege to view VQL).
If you grant this privilege over a specific element (data source, view…) instead of a database, the user will be able to do the following:
Delete the element. 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 element. The user can only modify a derived view if it has the “Execute” privilege over the subviews of the view.
Move the element across the existing folders of the database.
View the VQL of the element.
File Privilege¶
Users with the File privilege can browse through the directories listed in the dialog Privileges of the wizard Server Configuration.
Admin Privilege¶
This privilege can only be assigned over an entire database. Users with this privilege have the privileges Connect, Create, Metadata, Execute and Write over all the elements of that database. Besides, they can do the following tasks:
Set the configuration parameters of the database: 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.
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. The column restrictions
may affect to INSERT
operations.
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.