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.
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
CURRENT_DATE | CURRENT_TIMESTAMP
Denodo Related References
Denodo Virtual Query Language (VQL) Syntax Conventions > Denodo Reserved Words
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.
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:
Permissions grants include connection, creation, read, write and admin privileges over a VDP database.
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