Discussion:
multiple users and passwords
(too old to reply)
Knapp, Michael
2017-05-02 15:33:03 UTC
Permalink
Raw Message
Drill Developers and Supporters,

I am hoping to use drill to query a SQL databaes. There will be many different users accessing the drill web console, and each of them have separate credentials for accessing the database. I have the requirement of supporting drill queries to the database using the credentials provided by the current user. I am struggling to find a way to do this in drill because I noticed that:

· The documentation instructs me to provide the username and password in the storage plugin, either in the ‘url’ field or as separate ‘username’ and ‘password’ fields.

· As far as I know, Drill does not support user logins or various permission models.

So as I see it, if a person can reach the drill web console, then they can also see all of the storage plugin configurations. That means they can see the passwords in clear text. If I opened this up to multiple users, then each of them could see everybody else’s passwords. I cannot simply create a system account to perform queries on behalf of others because we have auditing requirements.

I also noticed that completed queries are logged in the “Profiles” tab on the console. So if somehow I configure things such that credentials are passed in a query, they would still be visible to other users by viewing completed queries. So I would also need to prevent that somehow.

Does anybody know how I can provide drill with each user’s credentials without sharing them with every user?

I don’t see any way to provide credentials in a select statement to my database, it looks like it can only be provided while forming a connection.

I was thinking, maybe I can write a new storage plugin that wraps the RDBMS plugin, and consumes credentials by some other method. I don’t see any documentation on how to write your own storage plugin.

Any ideas or suggestions would be greatly appreciated.

Michael Knapp
________________________________________________________

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.
Kunal Khatua
2017-05-02 18:36:56 UTC
Permalink
Raw Message
Have you had a look at this link?

https://drill.apache.org/docs/configuring-user-authentication

Configuring User Authentication - Apache Drill<https://drill.apache.org/docs/configuring-user-authentication/>
drill.apache.org
Authentication is the process of establishing confidence of authenticity. A Drill client user is authenticated when a drillbit process running in a Drill cluster ...


- Kunal

________________________________
From: Knapp, Michael <***@capitalone.com>
Sent: Tuesday, May 2, 2017 8:33:03 AM
To: ***@drill.apache.org
Cc: Chagani, Hassan; Swift, John
Subject: multiple users and passwords

Drill Developers and Supporters,

I am hoping to use drill to query a SQL databaes. There will be many different users accessing the drill web console, and each of them have separate credentials for accessing the database. I have the requirement of supporting drill queries to the database using the credentials provided by the current user. I am struggling to find a way to do this in drill because I noticed that:

· The documentation instructs me to provide the username and password in the storage plugin, either in the ‘url’ field or as separate ‘username’ and ‘password’ fields.

· As far as I know, Drill does not support user logins or various permission models.

So as I see it, if a person can reach the drill web console, then they can also see all of the storage plugin configurations. That means they can see the passwords in clear text. If I opened this up to multiple users, then each of them could see everybody else’s passwords. I cannot simply create a system account to perform queries on behalf of others because we have auditing requirements.

I also noticed that completed queries are logged in the “Profiles” tab on the console. So if somehow I configure things such that credentials are passed in a query, they would still be visible to other users by viewing completed queries. So I would also need to prevent that somehow.

Does anybody know how I can provide drill with each user’s credentials without sharing them with every user?

I don’t see any way to provide credentials in a select statement to my database, it looks like it can only be provided while forming a connection.

I was thinking, maybe I can write a new storage plugin that wraps the RDBMS plugin, and consumes credentials by some other method. I don’t see any documentation on how to write your own storage plugin.

Any ideas or suggestions would be greatly appreciated.

Michael Knapp
________________________________________________________

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.
Knapp, Michael
2017-05-02 18:55:45 UTC
Permalink
Raw Message
Sorry I noticed that documentation/link after I sent the original message. I also found the documentation on “Configuring User Impersonation” and “Configuring Inbound Impersonation” to be useful and relevant.

I am not sure that these will be adequate though. Drill supports inbound impersonation, but I think I need the opposite, outbound impersonation.

For example, I can setup Drill to use LDAP, and “Joe” can login to the machine. He may do a query joining the database with another source. Drill can use impersonation to execute these queries as Joe. Unfortunately though, Joe’s credentials for the backend database may not be the same as his LDAP credentials, and they may be different for the other data sources. Joe could configure the storage plugins to use his database username/password, but wouldn’t that also make his password visible to all users?

I guess I can summarize this with one question: Can Drill support separate storage plugin configurations per user?

On 5/2/17, 2:36 PM, "Kunal Khatua" <***@mapr.com> wrote:

Have you had a look at this link?

https://drill.apache.org/docs/configuring-user-authentication

Configuring User Authentication - Apache Drill<https://drill.apache.org/docs/configuring-user-authentication/>
drill.apache.org
Authentication is the process of establishing confidence of authenticity. A Drill client user is authenticated when a drillbit process running in a Drill cluster ...


- Kunal

________________________________
From: Knapp, Michael <***@capitalone.com>
Sent: Tuesday, May 2, 2017 8:33:03 AM
To: ***@drill.apache.org
Cc: Chagani, Hassan; Swift, John
Subject: multiple users and passwords

Drill Developers and Supporters,

I am hoping to use drill to query a SQL databaes. There will be many different users accessing the drill web console, and each of them have separate credentials for accessing the database. I have the requirement of supporting drill queries to the database using the credentials provided by the current user. I am struggling to find a way to do this in drill because I noticed that:

· The documentation instructs me to provide the username and password in the storage plugin, either in the ‘url’ field or as separate ‘username’ and ‘password’ fields.

· As far as I know, Drill does not support user logins or various permission models.

So as I see it, if a person can reach the drill web console, then they can also see all of the storage plugin configurations. That means they can see the passwords in clear text. If I opened this up to multiple users, then each of them could see everybody else’s passwords. I cannot simply create a system account to perform queries on behalf of others because we have auditing requirements.

I also noticed that completed queries are logged in the “Profiles” tab on the console. So if somehow I configure things such that credentials are passed in a query, they would still be visible to other users by viewing completed queries. So I would also need to prevent that somehow.

Does anybody know how I can provide drill with each user’s credentials without sharing them with every user?

I don’t see any way to provide credentials in a select statement to my database, it looks like it can only be provided while forming a connection.

I was thinking, maybe I can write a new storage plugin that wraps the RDBMS plugin, and consumes credentials by some other method. I don’t see any documentation on how to write your own storage plugin.

Any ideas or suggestions would be greatly appreciated.

Michael Knapp
________________________________________________________

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.


________________________________________________________

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete
Sudheesh Katkam
2017-05-02 20:06:51 UTC
Permalink
Raw Message
Drill supports impersonation (“outbound”) to HDFS and Hive; this works because the client API allows for inbound impersonation.

In your use-case, does the backend database allow Drill to impersonate the end user (“Joe”) i.e. does the database support inbound impersonation? If so, there still need to be some changes made in Drill to support that; please open a ticket in that case.

Passing credentials from one service to another is messy. Instead if service A supports inbound impersonation (or proxy users), then service A can verify service B’s credentials once, and allow service B to impersonate end users (maybe based on some policies, like Drill). This will avoid having to pass through the end user’s credentials.

- Sudheesh
Post by Knapp, Michael
Sorry I noticed that documentation/link after I sent the original message. I also found the documentation on “Configuring User Impersonation” and “Configuring Inbound Impersonation” to be useful and relevant.
I am not sure that these will be adequate though. Drill supports inbound impersonation, but I think I need the opposite, outbound impersonation.
For example, I can setup Drill to use LDAP, and “Joe” can login to the machine. He may do a query joining the database with another source. Drill can use impersonation to execute these queries as Joe. Unfortunately though, Joe’s credentials for the backend database may not be the same as his LDAP credentials, and they may be different for the other data sources. Joe could configure the storage plugins to use his database username/password, but wouldn’t that also make his password visible to all users?
I guess I can summarize this with one question: Can Drill support separate storage plugin configurations per user?
Have you had a look at this link?
https://drill.apache.org/docs/configuring-user-authentication
Configuring User Authentication - Apache Drill<https://drill.apache.org/docs/configuring-user-authentication/>
drill.apache.org
Authentication is the process of establishing confidence of authenticity. A Drill client user is authenticated when a drillbit process running in a Drill cluster ...
- Kunal
________________________________
Sent: Tuesday, May 2, 2017 8:33:03 AM
Cc: Chagani, Hassan; Swift, John
Subject: multiple users and passwords
Drill Developers and Supporters,
· The documentation instructs me to provide the username and password in the storage plugin, either in the ‘url’ field or as separate ‘username’ and ‘password’ fields.
· As far as I know, Drill does not support user logins or various permission models.
So as I see it, if a person can reach the drill web console, then they can also see all of the storage plugin configurations. That means they can see the passwords in clear text. If I opened this up to multiple users, then each of them could see everybody else’s passwords. I cannot simply create a system account to perform queries on behalf of others because we have auditing requirements.
I also noticed that completed queries are logged in the “Profiles” tab on the console. So if somehow I configure things such that credentials are passed in a query, they would still be visible to other users by viewing completed queries. So I would also need to prevent that somehow.
Does anybody know how I can provide drill with each user’s credentials without sharing them with every user?
I don’t see any way to provide credentials in a select statement to my database, it looks like it can only be provided while forming a connection.
I was thinking, maybe I can write a new storage plugin that wraps the RDBMS plugin, and consumes credentials by some other method. I don’t see any documentation on how to write your own storage plugin.
Any ideas or suggestions would be greatly appreciated.
Michael Knapp
________________________________________________________
The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.
________________________________________________________
The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender an
Sudheesh Katkam
2017-05-02 20:25:32 UTC
Permalink
Raw Message
To clarify:

Passing *end user* credentials from one service to another *to reach the target service* is messy.

On May 2, 2017, at 1:06 PM, Sudheesh Katkam <***@mapr.com<mailto:***@mapr.com>> wrote:

Drill supports impersonation (“outbound”) to HDFS and Hive; this works because the client API allows for inbound impersonation.

In your use-case, does the backend database allow Drill to impersonate the end user (“Joe”) i.e. does the database support inbound impersonation? If so, there still need to be some changes made in Drill to support that; please open a ticket in that case.

Passing credentials from one service to another is messy. Instead if service A supports inbound impersonation (or proxy users), then service A can verify service B’s credentials once, and allow service B to impersonate end users (maybe based on some policies, like Drill). This will avoid having to pass through the end user’s credentials.

- Sudheesh

On May 2, 2017, at 11:55 AM, Knapp, Michael <***@capitalone.com<mailto:***@capitalone.com>> wrote:

Sorry I noticed that documentation/link after I sent the original message. I also found the documentation on “Configuring User Impersonation” and “Configuring Inbound Impersonation” to be useful and relevant.

I am not sure that these will be adequate though. Drill supports inbound impersonation, but I think I need the opposite, outbound impersonation.

For example, I can setup Drill to use LDAP, and “Joe” can login to the machine. He may do a query joining the database with another source. Drill can use impersonation to execute these queries as Joe. Unfortunately though, Joe’s credentials for the backend database may not be the same as his LDAP credentials, and they may be different for the other data sources. Joe could configure the storage plugins to use his database username/password, but wouldn’t that also make his password visible to all users?

I guess I can summarize this with one question: Can Drill support separate storage plugin configurations per user?

On 5/2/17, 2:36 PM, "Kunal Khatua" <***@mapr.com<mailto:***@mapr.com>> wrote:

Have you had a look at this link?

https://drill.apache.org/docs/configuring-user-authentication

Configuring User Authentication - Apache Drill<https://drill.apache.org/docs/configuring-user-authentication/>
drill.apache.org<http://drill.apache.org>
Authentication is the process of establishing confidence of authenticity. A Drill client user is authenticated when a drillbit process running in a Drill cluster ...


- Kunal

________________________________
From: Knapp, Michael <***@capitalone.com<mailto:***@capitalone.com>>
Sent: Tuesday, May 2, 2017 8:33:03 AM
To: ***@drill.apache.org<mailto:***@drill.apache.org>
Cc: Chagani, Hassan; Swift, John
Subject: multiple users and passwords

Drill Developers and Supporters,

I am hoping to use drill to query a SQL databaes. There will be many different users accessing the drill web console, and each of them have separate credentials for accessing the database. I have the requirement of supporting drill queries to the database using the credentials provided by the current user. I am struggling to find a way to do this in drill because I noticed that:

· The documentation instructs me to provide the username and password in the storage plugin, either in the ‘url’ field or as separate ‘username’ and ‘password’ fields.

· As far as I know, Drill does not support user logins or various permission models.

So as I see it, if a person can reach the drill web console, then they can also see all of the storage plugin configurations. That means they can see the passwords in clear text. If I opened this up to multiple users, then each of them could see everybody else’s passwords. I cannot simply create a system account to perform queries on behalf of others because we have auditing requirements.

I also noticed that completed queries are logged in the “Profiles” tab on the console. So if somehow I configure things such that credentials are passed in a query, they would still be visible to other users by viewing completed queries. So I would also need to prevent that somehow.

Does anybody know how I can provide drill with each user’s credentials without sharing them with every user?

I don’t see any way to provide credentials in a select statement to my database, it looks like it can only be provided while forming a connection.

I was thinking, maybe I can write a new storage plugin that wraps the RDBMS plugin, and consumes credentials by some other method. I don’t see any documentation on how to write your own storage plugin.

Any ideas or suggestions would be greatly appreciated.

Michael Knapp
________________________________________________________

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.


________________________________________________________

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.
Loading...