You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Alexey Roytman <al...@oracle.com> on 2017/10/01 12:06:14 UTC
newbie: DriverManager.getConnection(url, user, password) and "lost"
credentials in SchemaFactory's Map
Dear colleagues, I'm doing my first steps in creating custom
SchemaFactory for accessing some endpoint.
My question is as following:
if I do DriverManager.getConnection(url, user, password);
why then:
SchemaFactory.create(SchemaPlus, String, Map<String, Object>)
gets the map without user and password?
I shall not create a model file (because it's a plain text with
password); I shall not pass username and password in URL (e.g.
";schema.username=...;schema.password=..." or model inline) because it's
saved as plain text.
I cannot change the call from DriverManager.getConnection(url, user,
password) to DriverManager.getConnection(url, Properties info) with
custom Properties, as I don't control that code.
My test code basically does this:
String user;
String password;
String url;
...
// got user, password and url from somewhere
// the url is of form: "jdbc:calcite:schemaFactory=MySchemaFactory"
Class.forName("org.apache.calcite.jdbc.Driver");
java.sql.Connection conn = DriverManager.getConnection(url, user,
password);
And the MySchemaFactory.java looks like this:
---
public class MySchemaFactory implements
org.apache.calcite.schema.SchemaFactory {
public MySchemaFactory() {
System.out.println("Factory");
}
@Override
public Schema create(SchemaPlus schemaPlus, String s,
java.util.Map<String, Object> map) {
String url = (String)map.get("url");
String username = (String)map.get("username");
String password = (String)map.get("password");
// do something with url, username and password...
return null; // just for demonstration
}
}
---
Instead of MySchemaFactory I can use
org.apache.calcite.adapter.cassandra.CassandraSchemaFactory, with same
result: the map has no credentials.
Does anyone have any idea of a secure way of passing credentials?..
Maybe, I need to write my own class extending
org.apache.calcite.jdbc.Driver?
What the right Calcite's way?
- Alexey.
Re: newbie: DriverManager.getConnection(url, user, password) and
"lost" credentials in SchemaFactory's Map
Posted by Alexey Roytman <al...@oracle.com>.
Julian, I don't use the "jdbcUrl", "jdbcUser", "jdbcPassword" per-se,
and I cannot define them in the model (in file -- for security reasons;
in Properties -- because I cannot change the call type to getConnection).
If I need to implement javax.sql.DataSource, I also need to implement
javax.sql.Connection, and then I need to implement javax.sql.Statement.
And I wanted to eliminate implementing the latter (SQL parsing and
executing), as I want to use Calcite framework to do all the work for me...
- Alexey.
Re: newbie: DriverManager.getConnection/3 - extending
org.apache.calcite.jdbc.Driver
Posted by Alexey Roytman <al...@oracle.com>.
Right, the DataSource has only 2 methods, but to implement them I need
to implement java.sql.Connection, too. And the latter will cause me to
implement java.sql.Statement. Otherwise I have no place to insert my
logic into.
Anyway, there is more important case: I generate inline model
dynamically. So, I have no ability to use MyDataSource at all, because
there is no model file to define MyDataSource class name at all. And the
only option left is to extend calcite's Driver.
Thank you, Julian.
- Alexey.
On 10/04/2017 11:59 PM, Julian Hyde wrote:
> DataSource only has two methods, so if you use it to connect to your underlying data source, accessing username and password or other authentication means from who knows where, then you wouldn’t have that much work to do.
>
> Your approach might work too.
>
> Julian
>
>
>> On Oct 3, 2017, at 4:25 AM, Alexey Roytman <al...@oracle.com> wrote:
>>
>> I did not take the approach of implementing javax.sql.DataSource as this causes implementation of java.sql.Connection and java.sql.Statement; and I want to use Calcite framework for parsing and executing SQL in Statement.
>>
>> So, the code I use now is as following:
>>
>> public class MyDriver extends org.apache.calcite.jdbc.Driver {
>> public static final String CONNECT_STRING_PREFIX = "jdbc:my:";
>> static { new MyDriver().register(); }
>> public MyDriver() { super(); }
>> @Override protected String getConnectStringPrefix() { return CONNECT_STRING_PREFIX; }
>> private static ThreadLocal<String> cached_username = new ThreadLocal<String>();
>> private static ThreadLocal<String> cached_password = new ThreadLocal<String>();
>> public static String getCachedUsername() { return cached_username.get(); } // used by SchemaFactory.create()
>> public static String getCachedPassword() { return cached_password.get(); } // used by SchemaFactory.create()
>> @Override
>> public java.sql.Connection connect(String url, Properties info) throws SQLException {
>> if (info != null) {
>> // same properties as in DriverManager.getConnection(String url, String username, String password)
>> String username = info.getProperty("user");
>> String password = info.getProperty("password");
>> if (username != null && password != null) {
>> cached_username.set(username);
>> cached_password.set(password);
>> }
>> }
>> return super.connect(url, info);
>> }
>> }
>> public class MySchemaFactory implements SchemaFactory {
>> public MySchemaFactory() {}
>> @Override
>> public Schema create(SchemaPlus schemaPlus, String s, Map<String, Object> map) {
>> String url = (String)map.get("url");
>> String username = (String)map.get("username");
>> String password = (String)map.get("password");
>> if (username == null && password == null) {
>> username = MyDriver.getCachedUsername();
>> password = MyDriver.getCachedPassword();
>> }
>> // ... here we do some real work, but for simplicity we...
>> return null;
>> }
>> }
>> // and then in the main code...
>> String user;
>> String password;
>> String url;
>> // got user, password and url from somewhere...
>> // url= "jdbc:my:schemaFactory=MySchemaFactory;schema=demodb;schema.url=..."
>> Class.forName("MyDriver");
>> java.sql.Connection conn = DriverManager.getConnection(url, user, password);
>> ...
>>
>>
>> The questions are:
>> Q1. Is this approach too ugly?
>> Q2. Is this approach against the ideology of Calcite?
>> Q3. Can I have more then one schema just by url tags, without model file/inline/Properties-argument?
>>
>> - Alexey.
>>
>>
>> On 10/01/2017 03:06 PM, Alexey Roytman wrote:
>>> Dear colleagues, I'm doing my first steps in creating custom SchemaFactory for accessing some endpoint.
>>> My question is as following:
>>> if I do DriverManager.getConnection(url, user, password);
>>> why then:
>>> SchemaFactory.create(SchemaPlus, String, Map<String, Object>)
>>> gets the map without user and password?
>>>
>>> I shall not create a model file (because it's a plain text with password); I shall not pass username and password in URL (e.g. ";schema.username=...;schema.password=..." or model inline) because it's saved as plain text.
>>>
>>> I cannot change the call from DriverManager.getConnection(url, user, password) to DriverManager.getConnection(url, Properties info) with custom Properties, as I don't control that code.
>>>
>>> My test code basically does this:
>>> String user;
>>> String password;
>>> String url;
>>> ...
>>> // got user, password and url from somewhere
>>> // the url is of form: "jdbc:calcite:schemaFactory=MySchemaFactory"
>>> Class.forName("org.apache.calcite.jdbc.Driver");
>>> java.sql.Connection conn = DriverManager.getConnection(url, user, password);
>>>
>>> And the MySchemaFactory.java looks like this:
>>> ---
>>> public class MySchemaFactory implements org.apache.calcite.schema.SchemaFactory {
>>> public MySchemaFactory() {
>>> System.out.println("Factory");
>>> }
>>> @Override
>>> public Schema create(SchemaPlus schemaPlus, String s, java.util.Map<String, Object> map) {
>>> String url = (String)map.get("url");
>>> String username = (String)map.get("username");
>>> String password = (String)map.get("password");
>>> // do something with url, username and password...
>>> return null; // just for demonstration
>>> }
>>> }
>>> ---
>>>
>>> Instead of MySchemaFactory I can use org.apache.calcite.adapter.cassandra.CassandraSchemaFactory, with same result: the map has no credentials.
>>>
>>> Does anyone have any idea of a secure way of passing credentials?..
>>> Maybe, I need to write my own class extending org.apache.calcite.jdbc.Driver?
>>> What the right Calcite's way?
>>>
>>> - Alexey.
>>>
Re: newbie: DriverManager.getConnection/3 - extending
org.apache.calcite.jdbc.Driver
Posted by Julian Hyde <jh...@apache.org>.
DataSource only has two methods, so if you use it to connect to your underlying data source, accessing username and password or other authentication means from who knows where, then you wouldn’t have that much work to do.
Your approach might work too.
Julian
> On Oct 3, 2017, at 4:25 AM, Alexey Roytman <al...@oracle.com> wrote:
>
> I did not take the approach of implementing javax.sql.DataSource as this causes implementation of java.sql.Connection and java.sql.Statement; and I want to use Calcite framework for parsing and executing SQL in Statement.
>
> So, the code I use now is as following:
>
> public class MyDriver extends org.apache.calcite.jdbc.Driver {
> public static final String CONNECT_STRING_PREFIX = "jdbc:my:";
> static { new MyDriver().register(); }
> public MyDriver() { super(); }
> @Override protected String getConnectStringPrefix() { return CONNECT_STRING_PREFIX; }
> private static ThreadLocal<String> cached_username = new ThreadLocal<String>();
> private static ThreadLocal<String> cached_password = new ThreadLocal<String>();
> public static String getCachedUsername() { return cached_username.get(); } // used by SchemaFactory.create()
> public static String getCachedPassword() { return cached_password.get(); } // used by SchemaFactory.create()
> @Override
> public java.sql.Connection connect(String url, Properties info) throws SQLException {
> if (info != null) {
> // same properties as in DriverManager.getConnection(String url, String username, String password)
> String username = info.getProperty("user");
> String password = info.getProperty("password");
> if (username != null && password != null) {
> cached_username.set(username);
> cached_password.set(password);
> }
> }
> return super.connect(url, info);
> }
> }
> public class MySchemaFactory implements SchemaFactory {
> public MySchemaFactory() {}
> @Override
> public Schema create(SchemaPlus schemaPlus, String s, Map<String, Object> map) {
> String url = (String)map.get("url");
> String username = (String)map.get("username");
> String password = (String)map.get("password");
> if (username == null && password == null) {
> username = MyDriver.getCachedUsername();
> password = MyDriver.getCachedPassword();
> }
> // ... here we do some real work, but for simplicity we...
> return null;
> }
> }
> // and then in the main code...
> String user;
> String password;
> String url;
> // got user, password and url from somewhere...
> // url= "jdbc:my:schemaFactory=MySchemaFactory;schema=demodb;schema.url=..."
> Class.forName("MyDriver");
> java.sql.Connection conn = DriverManager.getConnection(url, user, password);
> ...
>
>
> The questions are:
> Q1. Is this approach too ugly?
> Q2. Is this approach against the ideology of Calcite?
> Q3. Can I have more then one schema just by url tags, without model file/inline/Properties-argument?
>
> - Alexey.
>
>
> On 10/01/2017 03:06 PM, Alexey Roytman wrote:
>> Dear colleagues, I'm doing my first steps in creating custom SchemaFactory for accessing some endpoint.
>> My question is as following:
>> if I do DriverManager.getConnection(url, user, password);
>> why then:
>> SchemaFactory.create(SchemaPlus, String, Map<String, Object>)
>> gets the map without user and password?
>>
>> I shall not create a model file (because it's a plain text with password); I shall not pass username and password in URL (e.g. ";schema.username=...;schema.password=..." or model inline) because it's saved as plain text.
>>
>> I cannot change the call from DriverManager.getConnection(url, user, password) to DriverManager.getConnection(url, Properties info) with custom Properties, as I don't control that code.
>>
>> My test code basically does this:
>> String user;
>> String password;
>> String url;
>> ...
>> // got user, password and url from somewhere
>> // the url is of form: "jdbc:calcite:schemaFactory=MySchemaFactory"
>> Class.forName("org.apache.calcite.jdbc.Driver");
>> java.sql.Connection conn = DriverManager.getConnection(url, user, password);
>>
>> And the MySchemaFactory.java looks like this:
>> ---
>> public class MySchemaFactory implements org.apache.calcite.schema.SchemaFactory {
>> public MySchemaFactory() {
>> System.out.println("Factory");
>> }
>> @Override
>> public Schema create(SchemaPlus schemaPlus, String s, java.util.Map<String, Object> map) {
>> String url = (String)map.get("url");
>> String username = (String)map.get("username");
>> String password = (String)map.get("password");
>> // do something with url, username and password...
>> return null; // just for demonstration
>> }
>> }
>> ---
>>
>> Instead of MySchemaFactory I can use org.apache.calcite.adapter.cassandra.CassandraSchemaFactory, with same result: the map has no credentials.
>>
>> Does anyone have any idea of a secure way of passing credentials?..
>> Maybe, I need to write my own class extending org.apache.calcite.jdbc.Driver?
>> What the right Calcite's way?
>>
>> - Alexey.
>>
>
Re: newbie: DriverManager.getConnection/3 - extending
org.apache.calcite.jdbc.Driver
Posted by Alexey Roytman <al...@oracle.com>.
I did not take the approach of implementing javax.sql.DataSource as this
causes implementation of java.sql.Connection and java.sql.Statement; and
I want to use Calcite framework for parsing and executing SQL in Statement.
So, the code I use now is as following:
public class MyDriver extends org.apache.calcite.jdbc.Driver {
public static final String CONNECT_STRING_PREFIX = "jdbc:my:";
static { new MyDriver().register(); }
public MyDriver() { super(); }
@Override protected String getConnectStringPrefix() { return
CONNECT_STRING_PREFIX; }
private static ThreadLocal<String> cached_username = new
ThreadLocal<String>();
private static ThreadLocal<String> cached_password = new
ThreadLocal<String>();
public static String getCachedUsername() { return
cached_username.get(); } // used by SchemaFactory.create()
public static String getCachedPassword() { return
cached_password.get(); } // used by SchemaFactory.create()
@Override
public java.sql.Connection connect(String url, Properties info)
throws SQLException {
if (info != null) {
// same properties as in DriverManager.getConnection(String
url, String username, String password)
String username = info.getProperty("user");
String password = info.getProperty("password");
if (username != null && password != null) {
cached_username.set(username);
cached_password.set(password);
}
}
return super.connect(url, info);
}
}
public class MySchemaFactory implements SchemaFactory {
public MySchemaFactory() {}
@Override
public Schema create(SchemaPlus schemaPlus, String s, Map<String,
Object> map) {
String url = (String)map.get("url");
String username = (String)map.get("username");
String password = (String)map.get("password");
if (username == null && password == null) {
username = MyDriver.getCachedUsername();
password = MyDriver.getCachedPassword();
}
// ... here we do some real work, but for simplicity we...
return null;
}
}
// and then in the main code...
String user;
String password;
String url;
// got user, password and url from somewhere...
// url= "jdbc:my:schemaFactory=MySchemaFactory;schema=demodb;schema.url=..."
Class.forName("MyDriver");
java.sql.Connection conn = DriverManager.getConnection(url, user,
password);
...
The questions are:
Q1. Is this approach too ugly?
Q2. Is this approach against the ideology of Calcite?
Q3. Can I have more then one schema just by url tags, without model
file/inline/Properties-argument?
- Alexey.
On 10/01/2017 03:06 PM, Alexey Roytman wrote:
> Dear colleagues, I'm doing my first steps in creating custom
> SchemaFactory for accessing some endpoint.
> My question is as following:
> if I do DriverManager.getConnection(url, user, password);
> why then:
> SchemaFactory.create(SchemaPlus, String, Map<String, Object>)
> gets the map without user and password?
>
> I shall not create a model file (because it's a plain text with
> password); I shall not pass username and password in URL (e.g.
> ";schema.username=...;schema.password=..." or model inline) because
> it's saved as plain text.
>
> I cannot change the call from DriverManager.getConnection(url, user,
> password) to DriverManager.getConnection(url, Properties info) with
> custom Properties, as I don't control that code.
>
> My test code basically does this:
> String user;
> String password;
> String url;
> ...
> // got user, password and url from somewhere
> // the url is of form: "jdbc:calcite:schemaFactory=MySchemaFactory"
> Class.forName("org.apache.calcite.jdbc.Driver");
> java.sql.Connection conn = DriverManager.getConnection(url, user,
> password);
>
> And the MySchemaFactory.java looks like this:
> ---
> public class MySchemaFactory implements
> org.apache.calcite.schema.SchemaFactory {
> public MySchemaFactory() {
> System.out.println("Factory");
> }
> @Override
> public Schema create(SchemaPlus schemaPlus, String s,
> java.util.Map<String, Object> map) {
> String url = (String)map.get("url");
> String username = (String)map.get("username");
> String password = (String)map.get("password");
> // do something with url, username and password...
> return null; // just for demonstration
> }
> }
> ---
>
> Instead of MySchemaFactory I can use
> org.apache.calcite.adapter.cassandra.CassandraSchemaFactory, with same
> result: the map has no credentials.
>
> Does anyone have any idea of a secure way of passing credentials?..
> Maybe, I need to write my own class extending
> org.apache.calcite.jdbc.Driver?
> What the right Calcite's way?
>
> - Alexey.
>
Re: newbie: DriverManager.getConnection(url, user, password) and
"lost" credentials in SchemaFactory's Map
Posted by Julian Hyde <jh...@apache.org>.
Rather than connecting to JDBC using “jdbcUrl", “jdbcUser", “jdbcPassword” operands of the JDBC adapter you could instead connect to a data source. Write a class that implements javax.sql.DataSource, and include the class name in the model with a “dataSource” operand.
Your class can authenticate with the back-end database any way it likes.
Julian
> On Oct 1, 2017, at 5:06 AM, Alexey Roytman <al...@oracle.com> wrote:
>
> Dear colleagues, I'm doing my first steps in creating custom SchemaFactory for accessing some endpoint.
> My question is as following:
> if I do DriverManager.getConnection(url, user, password);
> why then:
> SchemaFactory.create(SchemaPlus, String, Map<String, Object>)
> gets the map without user and password?
>
> I shall not create a model file (because it's a plain text with password); I shall not pass username and password in URL (e.g. ";schema.username=...;schema.password=..." or model inline) because it's saved as plain text.
>
> I cannot change the call from DriverManager.getConnection(url, user, password) to DriverManager.getConnection(url, Properties info) with custom Properties, as I don't control that code.
>
> My test code basically does this:
> String user;
> String password;
> String url;
> ...
> // got user, password and url from somewhere
> // the url is of form: "jdbc:calcite:schemaFactory=MySchemaFactory"
> Class.forName("org.apache.calcite.jdbc.Driver");
> java.sql.Connection conn = DriverManager.getConnection(url, user, password);
>
> And the MySchemaFactory.java looks like this:
> ---
> public class MySchemaFactory implements org.apache.calcite.schema.SchemaFactory {
> public MySchemaFactory() {
> System.out.println("Factory");
> }
> @Override
> public Schema create(SchemaPlus schemaPlus, String s, java.util.Map<String, Object> map) {
> String url = (String)map.get("url");
> String username = (String)map.get("username");
> String password = (String)map.get("password");
> // do something with url, username and password...
> return null; // just for demonstration
> }
> }
> ---
>
> Instead of MySchemaFactory I can use org.apache.calcite.adapter.cassandra.CassandraSchemaFactory, with same result: the map has no credentials.
>
> Does anyone have any idea of a secure way of passing credentials?..
> Maybe, I need to write my own class extending org.apache.calcite.jdbc.Driver?
> What the right Calcite's way?
>
> - Alexey.
>