[pm-h] Fwd: Protecting with Stored Procedures

Julian Brown jlbprof at gmail.com
Sat Jan 13 09:52:27 EST 2018


I had a further discussion with the Bedrock guy about the socket being
accessible to all.   He said write my own access "plugin" and put whatever
security on it I choose.   I choose to use a unix socket but otherwise it
will be identical to the db plugin.

Julian

---------- Forwarded message ----------
From: David Barrett <dbarrett at expensify.com>
Date: Fri, Jan 12, 2018 at 6:01 PM
Subject: Re: Protecting with Stored Procedures
To: Julian Brown <jlbprof at gmail.com>
Cc: Bedrock <bedrock at googlegroups.com>


Hello!  Some thoughts:

1. No database has trustworthy access control.  I don't think there is a
security professional in the world that would advise seriously relying upon
the ACL built into MySQL or anything else.  I think it's better to just
acknowledge that if someone can execute arbitrary SQL on your database,
they can probably find a way to get access to everything.  Accordingly, the
Bedrock::DB plugin doesn't provide any access controls.  Additionally, if
someone is already on the database itself with root access, you're screwed
either way.  At that point no amount of database controls will help.

2. Rather, if you want to lock down your server (and I strongly encourage
you to) I suggest you *disable* the DB plugin (and thereby disable direct
SQL access to the database).  Rather, you should write stored procedures in
a *real* programming language: C++.  After all, the DB plugin just exposes
a single command -- "Query" -- that accepts SQL and outputs the results.
You can see the full code of the DB plugin here: https://github.com/
Expensify/Bedrock/blob/master/plugins/DB.cpp  To be clear, this isn't a
SQLite plugin (though you are free to do that if you want, and we have done
it to extend the SQL syntax with additional functionality), it's a Bedrock
plugin that specifies which external commands to expose to the client, and
what each of them should do.  Another example of a plugin would be our
Cache or Jobs plugins: https://github.com/Expensify/Bedrock/tree/master/
plugins

3. The result is that Bedrock only exposes the exact commands that you
write in your plugin (or any plugins you enable), and thus you can
implement whatever access control framework that is appropriate for your
application -- as that's going to be highly application specific anyway.
(After all, even if you did trust MySQL's access controls, you still
couldn't use it for your end users.)  In our case, we generate an
"authToken" using a serverside encryption key in the "Authenticate" command
(which takes a username/password), and then every other call takes an
"authToken" to authenticate the user for that call.  You might do something
similar, or different, but ultimately that's up to you.

Does this make sense?  Thanks for asking!

-david



On Fri, Jan 12, 2018 at 6:20 AM, Julian Brown <jlbprof at gmail.com> wrote:

> Apparently Sqlite does not support stored procedures, so I am not sure
> what you are referring to.  Or are you using some plugin to sqlite that
> supports it?
>
> --
> You received this message because you are subscribed to the Google Groups
> "Bedrock" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to bedrock+unsubscribe at googlegroups.com.
> To post to this group, send email to bedrock at googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/ms
> gid/bedrock/be4045c6-b4c7-4d2b-8568-e498ee88bd7f%40googlegroups.com
> <https://groups.google.com/d/msgid/bedrock/be4045c6-b4c7-4d2b-8568-e498ee88bd7f%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
> For more options, visit https://groups.google.com/d/optout.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://houston.pm.org/pipermail/houston_houston.pm.org/attachments/20180113/6f19ff56/attachment.html>


More information about the Houston mailing list