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 javax.management.ObjectName; import javax.management.openmbean.CompositeDataSupport; import javax.management.MBeanServerConnection; import javax.management.remote.JMXConnector; import javax.management.remote.JMXConnectorFactory; import javax.management.remote.JMXServiceURL; 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(rset.next()) { 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); } } ```
user
19-03-2021 16:09:55 -0400

4 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](https://community.denodo.com/answers/question/details?questionId=9060g0000004FSlAAM&Access+to+view+data+in+CustomPolicy+class) 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](https://support.denodo.com/) 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?
user
22-03-2021 09:15:29 -0400
I have the same requirement. Very interested in how to avoid hardcoding username/password in java code
user
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 ``` driver.class.name=com.denodo.vdp.jdbc.Driver 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
You must sign in to add an answer. If you do not have an account, you can register here