Denodo Source Database Permissions For SQL Server

Denodo does not publish a concise list of what permissions denodo needs in a source database.  So, I thought I would document what is required for a SQL Server data source for denodo.

Source database Dendo Access Permissions

Assuming there is no need to write back to the source DB, the data source connection permissions in a SQL Server the denodo source database should be:

  • Read And View Definitions access
  • Create tables (for data movement optimization), and
  • Access to getSchemas()

Why Create Table, Not Just Temporary Table?

The reason by denodo needs create tables permissions, rather than just create temporary tables permissions, is that temporary tables are only valid for the same connection, and Data Movement makes use of multiple connections, so we can not rely on the temporary table mechanism.

What if you need to Write back capabilities to data sources connection?  

If you had a use case where users would need to write back to the SQL Server database via Denodo, the Insert/Update permissions would need to be added to the list above.

Denodo Reserved words – Virtual Query Language (VQL)

Here s another useful reference, which came from a customer question.  This customer was trying to create a denodo 7.0 VDP database, which denodo Virtual DataPort (VDP) would not except and was wondering why.  After a little research, it turned out to be VDP database name was a denodo reserved word.  While I did find the name finding the Denodo Reserved words for Virtual Query Language (VQL) were incorporated in the text of page which did not immediately return when performing Google Search for “denodo reserved words.”  Rather, the ‘List of Reserved Words’ for Denodo Virtual Query Language (VQL) was buried at the end of the Denodo VQL Syntax Conventions.

So, I thought I would pull the list of reserved words into a separate page and post the list of denodo VQL reserved words in this site for the next time some asks me for the list of Denodo Virtual Query Language (VQL) reserved words.

Denodo List Of Reserved VQL Words

  • ADD
  • ALL
  • ALTER
  • AND
  • ANY
  • ARN
  • AS
  • ASC
  • BASE
  • BOTH
  • CALL
  • CASE
  • CONNECT
  • CONTEXT
  • CREATE
  • CROSS
  • CURRENT_DATE | CURRENT_TIMESTAMP
  • CUSTOM
  • DATABASE
  • DEFAULT
  • DESC
  • DF
  • DISTINCT
  • DROP
  • EXISTS
  • FALSE
  • FETCH
  • FLATTEN
  • FROM
  • FULL
  • GRANT
  • GROUP BY
  • GS
  • HASH
  • HAVING
  • HTML
  • IF
  • INNER
  • INTERSECT,INTO
  • IS
  • JDBC
  • JOIN
  • LDAP
  • LEADING
  • LEFT
  • LIMIT
  • MERGE
  • MINUS
  • MY
  • NATURAL
  • NESTED
  • NOS
  • NOT
  • NULL
  • OBL
  • ODBC
  • OF
  • OFF
  • OFFSET
  • ON
  • ONE
  • OPT
  • OR
  • ORDER BY
  • ORDERED
  • PRIVILEGES
  • READ
  • REVERSEORDER
  • REVOKE
  • RIGHT
  • ROW
  • SELECT
  • SWAP
  • TABLE
  • TO
  • TRACE
  • TRAILING
  • TRUE
  • UNION
  • USER
  • USING
  • VIEW
  • WHEN
  • WHERE
  • WITH
  • WRITE
  • WS
  • ZERO

Denodo Related References

Denodo Virtual Query Language (VQL) Syntax Conventions > Denodo Reserved Words

ITPilot List of Reserved Words

What Did The Denodo Read Privilege Change To In Denodo 7.0?

Well, the question was asked today by a customer, what happened to the “Read” denodo user privilege between version 6 and version 7.  So, I had to a little research and thought to write it down here, in case anyone else happens to ask or wants to know.

The Denodo 6.0 “Read” privilege is now the “Execute” privilege in version 7.

According To The Denodo Documentation:

  • In Denodo 7.0, the privilege “Read” has been renamed to “Execute” to clarify its behavior. In VQL statements, the “READ” token can still be used to keep backward compatibility with existing VQL scripts.

The supporting Denodo change documentation page: 

Denodo Version 7 Privileges

The list of Denodo privileges and their descriptions can be found here:

Denodo Security Enforcement

As the Virtual DataPort Administration Guide, explains in the section “Types of Access Rights” section, on VDP databases, views, rows, and columns. The denodo role-based access mechanism controls how and what a user or user role can use in the virtual layer, including the data catalog.

Import Denodo Security Notes

  • Consumer security authorization is imposed at the object level, then Data Level
  • Consumer security authorization is not imposed on Modeling Layers/VDP Folders
  • Using a virtual database to partition projects or subjects is a Best Practice

Basically, the ability to grant security is as follows:

VDP Database

  •  Permissions grants include connection, creation, read, write and admin privileges over a VDP database.

VDP Views

  • Permissions grants include read, write, insert, update and delete privileges over a view.

VDP Columns Within a VDP View

  • Permissions grants include the denial of the projection specific columns /fields within a view.

Row Level Security

  • Row Level restrictions can be added to allow users to obtain only the rows that match a certain condition or to return all the rows masking the sensitive fields

Denodo Virtual DataPort (VDP) Administration Guide

 For more information, see these section denodo Virtual DataPort Administration Guide:

  • Section 12.2 of the guide describes the general concepts of user and access rights management in DataPort, while
  • Section 12.3 describes how privileges are managed and assigned to users and roles using the VDP Administration Tool.

Virtual DataPort Administration Guide

Related References