You can translate the question and the replies:

Custom policies - Trying to get data from Denodo base view

Using Denodo 8.0, I'm trying to build a custom policy that will be applied to a specific derived view, that works this way: When user tries to access the data of a derived view, the policy checks to see if the current user is in a config table. If the user is in the config table, accept the query, and if they are not, reject the query. The config table is a base view created in a Denodo virtual database (db1 below). I can't figure out if I'm doing it the right way or not. When I use the code below, it rejects the query but I tested it with a user that WAS in the config table, so something is wrong. ``` package com.denodo.vdp.demo.policy.custom; import java.util.Arrays; import java.util.List; import java.util.*; import java.sql.*; import; import; import; import; import; import; import java.util.logging.Logger; import org.apache.log4j.Logger; import com.denodo.common.custom.annotations.*; import com.denodo.common.custom.policy.*; @CustomElement(type = CustomElementType.VDPCUSTOMPOLICY, name = "if_in_sec_table") public class if_in_sec_table { Connection conn = null; Statement stmt = null; @CustomContext private static CustomRestrictionPolicyContext context; @CustomExecutor public CustomRestrictionPolicyValue execute() { String v_user = context.getCurrentUserName(); v_user = ("'"+v_user+"'"); //logger.error("Test"); CustomRestrictionPolicyType policyType = null; try { JmxConnection conn = context.getJmxConnection(); Statement stmt = ((Connection) conn).createStatement(); String user_id_query = String.format("SELECT distinct userid FROM db1.sec_table_den WHERE UPPER(userid) = UPPER(%s);", v_user); ResultSet rset = stmt.executeQuery(user_id_query); int rowCount = 0; while( { String id = rset.getString("userid"); System.out.println("ID is :" + id); ++rowCount; } if (rowCount == 0) { policyType = CustomRestrictionPolicyType.REJECT; } else{ policyType = CustomRestrictionPolicyType.ACCEPT; } } catch (Exception e) { context.log(LogLevel.ERROR, "Error establishing a connection with the JMX interface: " + e.getMessage()); policyType = CustomRestrictionPolicyType.REJECT; } return new CustomRestrictionPolicyValue(policyType); } } ```
19-03-2021 16:09:55 -0400

6 Answers

Hi, For accessing a view using a Java class, I would make a JDBC connection to the Virtual DataPort Server in the class and query the view. The syntax I would use to establish a JDBC connection would be as follows : ``` Class.forName("com.denodo.vdp.jdbc.Driver"); connection = DriverManager.getConnection(<database url>,<user>,<password>); ``` Having said that, if the query sent from the Java class to the Virtual DataPort is not getting executed I would check if the credentials passed to the JDBC connections are correct. You can refer to a similar community [Q&A]( for more information. Suppose if you still need help and if you are a valid support user then you can raise a support case on [Denodo Support Site]( so that our support team can help you Hope this helps!
Denodo Team
22-03-2021 08:51:48 -0400
Thanks for your answer. How would I go about an add the username and password to a properties file instead of hardcoding it? Where would this properties file be located? Also, if we have 10 different custom policies that need to use this jdbc connection with username and password, could all 10 custom policies use the same properties file?
22-03-2021 09:15:29 -0400
I have the same requirement. Very interested in how to avoid hardcoding username/password in java code
23-03-2021 11:23:23 -0400
Hi, In order to create a JDBC connection to the Virtual Dataport server using a** .properties** file inside a Java class, I would perform the following steps: * Create a** .properties file** with the Connection URI, username, password as key=value format. For instance, I had the values in the following syntax ``` vdp_jdbc.DATABASEURI=<vdp connection URL> vdp_jdbc.USERNAME=<username> vdp_jdbc.USERPASSWORD=<password> ``` * Access the properties file using file reader library of Java(Eg: FileInputSteam, java.util.Properties, etc.,) Once the connection is established, Virtual DataPort can be queried from within the Java class. Hope this helps!
Denodo Team
29-03-2021 08:47:28 -0400
I tried the above but I'm getting this error message: Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
15-08-2021 23:31:43 -0400
Hi, If I get the error similar to **com.mysql.jdbc.Driver'. This is deprecated. The new driver class is**, then I would check if the driver class is valid or deprecated. I would use the new driver class for MySQL that is **com.mysql.cj.jdbc.Driver** to try to connect to the source instead of the deprecated version. You could contact your SQL administrator for more information on the deprecated driver class for MySQL. Hope this helps!
Denodo Team
23-08-2021 04:36:06 -0400
You must sign in to add an answer. If you do not have an account, you can register here