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);
}
}
```
