Protecting with Stored Procedures

[Julian Brown]

In various articles you mention you protect the database with stored procedures.  Since any unprivileged user on the system can netcat and sql dump or destroy the database, I was wondering can you give me some clue how to defend the data with stored procedures or point me to existing articles on that topic or what I can google?  I demonstrated Bedrock to a local user group and they liked it, it is much easier to setup replication than any other broker I have seen.  The unprivileged user issue is a problem.   So I need to figure out how to deal with that.   I can protect from external systems, but if they can compromise my website and get a low powered user on that system my entire data cluster is at risk.    Can I tell it to use a unix socket?  I believe I can protect a unix socket with user/group permissions.

Thanx

Julian

[Julian Brown]

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?   

[David Barrett]

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 <jlb...@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@googlegroups.com.
To post to this group, send email to bed...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/bedrock/be4045c6-b4c7-4d2b-8568-e498ee88bd7f%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Julian Brown]

OK so I control the access to Bedrock via a Bedrock plugin of my own device.

Thanx

Julian

On Friday, January 12, 2018 at 6:01:47 PM UTC-6, David Barrett wrote:

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 <jlb...@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+u...@googlegroups.com.

To post to this group, send email to bed...@googlegroups.com.