Re: Security with Storage Plugins

[John Omernik <jo...@omernik.com>]

After talking about my other idea (Hidden workspaces) I remembered this
thread.  This doesn't supplant my other discussion, however I would like to
see if I couldn't get some discussion going here on workspace security
again.

On Wed, Nov 25, 2015 at 9:25 AM, John Omernik <jo...@omernik.com> wrote:

> After reviewing https://issues.apache.org/jira/browse/DRILL-2578 more
> closely, I think this is just part of the problem.  I created another JIRA
> ( https://issues.apache.org/jira/browse/DRILL-4129 ) to address this.
> This is really important for enterprise adoption, especially as we add more
> plugins that are accessing other data stores.
>
> John
>
>
>
>
> On Wed, Oct 28, 2015 at 11:14 PM, Ted Dunning <te...@gmail.com>
> wrote:
>
>> Saving workspace definitions into the file system has the desirable effect
>> of making them very flexible and it also has the virtue that you can use
>> file system permissions to control access to the underlying data. If you
>> have different database accounts, you can embed the account definition
>> into
>> the workspace definition and tie that to normal permissions by changing
>> the
>> permissions on the workspace definition. This is essentially identical to
>> the way that Drill handles views now.
>>
>> There is a bit of a question about how to tell Drill where to find such
>> definitions. Putting these definitions inside another workspace leads to
>> syntactic problems.  Putting them in a well known directory works, but
>> makes it harder to control scope.  Using a well known directory that is
>> recursively searched for definitions might provide useful scoping.
>>
>>
>>
>> On Tue, Oct 27, 2015 at 10:03 AM, <mi...@nomura.com> wrote:
>>
>> > I did raise a JIRA for the storage of plugins on the filesystem a while
>> > back - more for convenience than security. I think what you mentioned
>> would
>> > be useful.
>> >
>> > FYI - https://issues.apache.org/jira/browse/DRILL-2578
>> >
>> >
>> > -----Original Message-----
>> > From: John Omernik [mailto:john@omernik.com]
>> > Sent: 27 October 2015 13:58
>> > To: user
>> > Subject: Re: Security with Storage Plugins
>> >
>> > Then I think the idea of securing each storage plugin may be a good
>> idea.
>> > Just an off the cuff idea: What if we had an option to enable security
>> for
>> > storage plugins (an opt in process) that specified a filesystem
>> location as
>> > a root location for storage plugins.
>> >
>> > Each storage plugin created would get a directory under that root.
>> >
>> > STORAGE_PLUGIN_SECURITY_ROOT="maprfs://data/storage_plugins"
>> >
>> >
>> > Then if I had a Mongo plugin named labmongo,  a jdbc plugin named
>> > labmysql, and a hbase plugin named labhbase it would create directories
>> > that would be world readable as such:
>> >
>> > /data/storage_plugins/labmongo
>> > /data/storage_plugins/labmysql
>> > /data/storage_plugins/labhbase
>> >
>> > These would be "world readable" as to be "visible"  If you didn't want
>> > them to be visible to users, you could change the root permissions to be
>> > limiting, but only users who can read them will have them shown in "show
>> > databases"
>> >
>> > In those directories there would be an automatically created a directory
>> > called "security" or "default"
>> >
>> > Permissions and ownership (for impersonation) for the plugin would be
>> set
>> > by setting the filesystem permissions on that directory
>> (default/security)
>> >
>> > Then you could create views for the storage plugin itself that would be
>> > located in the root:
>> > /data/storage_plugins/labmobgo/view_limited.json
>> > /data/storage_plugins/labmongo/view_other_limited.json
>> >
>> > And use permissions on those views like we do with permissions on
>> > filesystem locations.
>> >
>> > In addition, this model would allow us to create workspaces that are
>> > specific to certain tables within the storage plugin (because now we'd
>> have
>> > a place to store those workspaces) and those works spaces could have
>> > permissions too.
>> >
>> > I can see potential pitfalls here, however, this gives flexibility and
>> it
>> > matches the security model for the filesystem plugin in that people
>> > wouldn't have "one" way to do security for filesystem plugins, and
>> another
>> > for non-filesystem plugins. It could help increase adoption and ease
>> people
>> > into using it through familiarity.
>> >
>> > Just a thought.
>> >
>> > John
>> >
>> >
>> >
>> > On Tue, Oct 27, 2015 at 12:01 AM, Tomer Shiran <ts...@dremio.com>
>> wrote:
>> >
>> > > Got it
>> > >
>> > > Yes, you can certainly have multiple Mongo datastores set up in Drill.
>> > > Or multiple JDBC datastores, ...
>> > >
>> > > On Mon, Oct 26, 2015 at 1:12 PM, John Omernik <jo...@omernik.com>
>> wrote:
>> > >
>> > > > I was just saying that if we are only allowed "one" mongo storage
>> > > > plugin, (or one of any given type, hbase, jdbc) then we'd need to do
>> > > > something
>> > > that
>> > > > passes the identify through from the user, in a manual process of
>> > > > some sort. But if I can have multiple mongo plugins, and different
>> > > > permissions on them I have more flexibility.
>> > > >
>> > > > The scenario I am thinking about is in enterprises that have some
>> > > > old database server, that perhaps you can connect to it with jdbc to
>> > > > pull
>> > > data
>> > > > out, but no one on the dev team wants to implement any changes to
>> > > > the system. (I.e. no granular security) They implement security
>> > > > through a strict IP based (firewalls etc) security model: Very weak
>> to
>> > be sure.
>> > > > However, if something could be setup where the drill bits were given
>> > > access
>> > > > to the IP, it could then have security built on top of that using
>> > > > views
>> > > (if
>> > > > we had permissions on the storage plugin).  This is not the "right"
>> > > > way
>> > > to
>> > > > do data, and I am not advocating it, it would just be a nice tool to
>> > > > use for those orgs that where there is a possibility of access data,
>> > > > and
>> > > using
>> > > > it in a better way, but then using drill to help clean things up
>> > > > from a security perspective.
>> > > >
>> > > >
>> > > >
>> > > > On Mon, Oct 26, 2015 at 2:34 PM, Tomer Shiran <ts...@dremio.com>
>> > > wrote:
>> > > >
>> > > > > If the devs didn't lock down the Mongo cluster, then I'm not sure
>> > > > > that limiting the number of storage plugins in the Drill cluster
>> can
>> > help.
>> > > At
>> > > > > the end of the day, the rouge user could access the Mongo cluster
>> > > > > irrespective of Drill, using the Mongo API, or they could even set
>> > > > > up
>> > > > their
>> > > > > own Drill cluster and access it from there.
>> > > > >
>> > > > > Or maybe I misunderstood the point you were making...
>> > > > >
>> > > > > On Mon, Oct 26, 2015 at 12:01 PM, John Omernik <jo...@omernik.com>
>> > > wrote:
>> > > > >
>> > > > > > Yes, I would say that approach (permissions to the plugins
>> > > themselves)
>> > > > > is a
>> > > > > > more ubiquitous solution, and allows Drill to be the place to
>> > > > > > set permissions as exposed to the users. I.e. sometimes there
>> > > > > > are just
>> > > poor
>> > > > > > setups with poor security (a mongo DB setup where the devs
>> > > > > > didn't
>> > > lock
>> > > > it
>> > > > > > down) by using a storage plugin permission setup, we can "add"
>> > > security
>> > > > > via
>> > > > > > drill even when the source data doesn't have any.
>> > > > > >
>> > > > > > One quick question as it pertains to this discussion, there
>> > > > > > aren't
>> > > any
>> > > > > > limitations on how many Mongo plugins, or how many jdbc plugins
>> > > > > > we
>> > > can
>> > > > > have
>> > > > > > are there? That would be show stopper for applying security from
>> > > > > > this perspective.
>> > > > > >
>> > > > > > John
>> > > > > >
>> > > > > >
>> > > > > >
>> > > > > > On Mon, Oct 26, 2015 at 1:34 PM, Jacques Nadeau
>> > > > > > <ja...@dremio.com>
>> > > > > > wrote:
>> > > > > >
>> > > > > > > Agreed.
>> > > > > > >
>> > > > > > > We can also provide a general solution: storage plugin access
>> > > > > > permissions.
>> > > > > > > If someone can't access the storage plugin, then we could use
>> > > > > > > Drill
>> > > > > views
>> > > > > > > to manage security. The problem right now is that everyone has
>> > > access
>> > > > > to
>> > > > > > > every storage plugin (and in the case of JDBC, Mongo and
>> > > > > > > HBase,
>> > > that
>> > > > > > means
>> > > > > > > it is an open window into the underlying system).
>> > > > > > >
>> > > > > > > --
>> > > > > > > Jacques Nadeau
>> > > > > > > CTO and Co-Founder, Dremio
>> > > > > > >
>> > > > > > > On Mon, Oct 26, 2015 at 11:00 AM, Keys Botzum <
>> > > kbotzum@maprtech.com>
>> > > > > > > wrote:
>> > > > > > >
>> > > > > > > > Identity assertion from drill via jdbc to the underlying
>> > > > > > > > database
>> > > > is
>> > > > > > > > needed. It's been years since I looked at this but most
>> > > > > > > > databases
>> > > > > have
>> > > > > > a
>> > > > > > > > way of doing this. The technique was unique to each database
>> > > > > > > > in
>> > > the
>> > > > > > past.
>> > > > > > > >
>> > > > > > > > Keys
>> > > > > > > > _______________________________ Keys Botzum Senior Principal
>> > > > > > > > Technologist kbotzum@maprtech.com
>> > > > > > > > 443-718-0098
>> > > > > > > > MapR Technologies
>> > > > > > > > http://www.mapr.com
>> > > > > > > > On Oct 26, 2015 1:54 PM, "John Omernik" <jo...@omernik.com>
>> > > wrote:
>> > > > > > > >
>> > > > > > > > > So if I create a JDBC Storage Plugin with JSON below.  I
>> > > > > > > > > now
>> > > > have a
>> > > > > > > > storage
>> > > > > > > > > plugin available to all users in drill. There is no
>> > > > > > > > > limiting
>> > > who
>> > > > > can
>> > > > > > > use
>> > > > > > > > > that (that I can tell).  So then in this case, I have a
>> > > > > > > > > user
>> > > > > (myuser)
>> > > > > > > who
>> > > > > > > > > is authenticating to the Mysql server. That means, ALL
>> > > > > > > > > users of
>> > > > > drill
>> > > > > > > > will
>> > > > > > > > > have the access rights of myuser on the MySQL Server. This
>> > > > > > > > > is
>> > > not
>> > > > > an
>> > > > > > > > ideal
>> > > > > > > > > situation.
>> > > > > > > > >
>> > > > > > > > > Ideally, we could have a "service" user of some sort
>> > > > > > > > > connected
>> > > > > > between
>> > > > > > > > > Drill and the MySQL Server, this would be the equivalent
>> > > > > > > > > of the
>> > > > > super
>> > > > > > > > user
>> > > > > > > > > on drill.  Then from there, we could create views with
>> > > > permissions
>> > > > > > > (just
>> > > > > > > > > like filesystem based views) where we could select who
>> > > > > > > > > could
>> > > > select
>> > > > > > > from
>> > > > > > > > > view, allowing us to wrap some security around the
>> situation.
>> > > > In a
>> > > > > > > > perfect
>> > > > > > > > > world, we'd have a way to pass the credentials to the JDBC
>> > > driver
>> > > > > > from
>> > > > > > > > the
>> > > > > > > > > query, but that seems fraught with issues
>> > > > > > > > > (usernames/passwords
>> > > in
>> > > > > the
>> > > > > > > > query
>> > > > > > > > > logs to start with).  We could create multiple storage
>> > > > > > > > > plugins
>> > > > for
>> > > > > > > > > different levels of access to the various JDBC tables, but
>> > > > > > > > > we'd
>> > > > > still
>> > > > > > > > have
>> > > > > > > > > no way of controlling who could use the various plugins.
>> > > (Unless
>> > > > I
>> > > > > am
>> > > > > > > > > missing something obvious here).   Basically,  as I see it
>> > now,
>> > > > > there
>> > > > > > > is
>> > > > > > > > no
>> > > > > > > > > way to limit who can use a storage plugin in drill.
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > > > Storage Plugins Creation:
>> > > > > > > > >
>> > > > > > > > > {
>> > > > > > > > >
>> > > > > > > > >   "type": "jdbc",
>> > > > > > > > >
>> > > > > > > > >   "driver": "com.mysql.jdbc.Driver",
>> > > > > > > > >
>> > > > > > > > >   "url": "jdbc:mysql://localhost:3306/mydb",
>> > > > > > > > >
>> > > > > > > > >   "username": "myuser",
>> > > > > > > > >
>> > > > > > > > >   "password": "mypass",
>> > > > > > > > >
>> > > > > > > > >   "enabled": false
>> > > > > > > > >
>> > > > > > > > > }
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > > > On Mon, Oct 26, 2015 at 12:00 PM, Neeraja Rentachintala <
>> > > > > > > > > nrentachintala@maprtech.com> wrote:
>> > > > > > > > >
>> > > > > > > > > > Hi John
>> > > > > > > > > >
>> > > > > > > > > > Can you please elaborate on what you mean by
>> > > > > > > > > > authentication
>> > > to
>> > > > > the
>> > > > > > > > source
>> > > > > > > > > > system is going to be wide open in Drill.
>> > > > > > > > > >
>> > > > > > > > > > Drill is a query layer on data sources and will respect
>> > > > > > > > > > the
>> > > > > > > underlying
>> > > > > > > > > > storage permissions without having to manage centralized
>> > > > security
>> > > > > > > > > > permissions at Drill layer through user impersonation.
>> > > > > > > > > > Users
>> > > > can
>> > > > > > use
>> > > > > > > > > Drill
>> > > > > > > > > > views if they need more granular access to the data.
>> > > > > > > > > >
>> > > > > > > > > > I would be interested in learning more about your use
>> > > > > > > > > > case to
>> > > > > > secure
>> > > > > > > > the
>> > > > > > > > > > storage plugin/connections.
>> > > > > > > > > >
>> > > > > > > > > > thanks
>> > > > > > > > > >
>> > > > > > > > > > On Mon, Oct 26, 2015 at 6:33 AM, John Omernik <
>> > > > john@omernik.com>
>> > > > > > > > wrote:
>> > > > > > > > > >
>> > > > > > > > > > > Hey all -
>> > > > > > > > > > >
>> > > > > > > > > > > On file system based storage plugins, security is
>> > > > > > > > > > > straight
>> > > > > > forward
>> > > > > > > > with
>> > > > > > > > > > > filesystem permissions etc.  How do we secure storage
>> > > > plugins?
>> > > > > > It
>> > > > > > > > > would
>> > > > > > > > > > > seem we would want a situation where people could not
>> > > access
>> > > > > > > certain
>> > > > > > > > > > > storage plugins especially since authentication to the
>> > > source
>> > > > > > > system
>> > > > > > > > is
>> > > > > > > > > > > going to be "wide open" (i.e. there is no pass through
>> > > > > > > > > > > auth
>> > > > to
>> > > > > a
>> > > > > > > > > backend
>> > > > > > > > > > > Mongo server, JDBC system, or Hbase setup)  thus how
>> > > > > > > > > > > do we
>> > > > wrap
>> > > > > > > > > security
>> > > > > > > > > > > around these?
>> > > > > > > > > > >
>> > > > > > > > > > > Even basic security... i.e. only X user can use them,
>> > > > > > > > > > > so we
>> > > > can
>> > > > > > use
>> > > > > > > > > them
>> > > > > > > > > > to
>> > > > > > > > > > > load parquet tables or something where we can apply
>> > > security.
>> > > > > > > > > > >
>> > > > > > > > > > > Thoughts?
>> > > > > > > > > > >
>> > > > > > > > > > > John
>> > > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > >
>> > > > > > > >
>> > > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > > > --
>> > > > > Tomer Shiran
>> > > > > CEO and Co-Founder, Dremio
>> > > > >
>> > > >
>> > >
>> > >
>> > >
>> > > --
>> > > Tomer Shiran
>> > > CEO and Co-Founder, Dremio
>> > >
>> >
>> >
>> > This e-mail (including any attachments) is private and confidential, may
>> > contain proprietary or privileged information and is intended for the
>> named
>> > recipient(s) only. Unintended recipients are strictly prohibited from
>> > taking action on the basis of information in this e-mail and must
>> contact
>> > the sender immediately, delete this e-mail (and all attachments) and
>> > destroy any hard copies. Nomura will not accept responsibility or
>> liability
>> > for the accuracy or completeness of, or the presence of any virus or
>> > disabling code in, this e-mail. If verification is sought please
>> request a
>> > hard copy. Any reference to the terms of executed transactions should be
>> > treated as preliminary only and subject to formal written confirmation
>> by
>> > Nomura. Nomura reserves the right to retain, monitor and intercept
>> e-mail
>> > communications through its networks (subject to and in accordance with
>> > applicable laws). No confidentiality or privilege is waived or lost by
>> > Nomura by any mistransmission of this e-mail. Any reference to "Nomura"
>> is
>> > a reference to any entity in the Nomura Holdings, Inc. group. Please
>> read
>> > our Electronic Communications Legal Notice which forms part of this
>> e-mail:
>> > http://www.Nomura.com/email_disclaimer.htm
>> >
>> >
>>
>
>