Securing the db from data changing commands

[Akshay Kumbhar]

I have db for which I want to restrict users for using only select commands. The UI is built in spring from which query/queries can be passed from text-area. This can contain queries,comments,random texts. I want to allow only select commands from that text (if possible) or
find that there is any query which is breaking the contract so giving the appropriate error msg. How can I proceed with this issue.

[Roel De Nijs]

Can't you limit the permissions of the user to READ_ONLY? On Microsoft Sql server you can use the db_datareader role for example.

[chris webster]

It depends on your database, but one approach would be to apply this security within the database. Your tables might be owned by a user called APP_OWNER. APP_OWNER would then GRANT SELECT permissions on certain tables to another user (or user group/role) e.g. READ_ONLY_USER. In your application, you would have a DB connection that logs in as READ_ONLY_USER, so they can only do SELECT (read) requests against the tables. The database will raise an error if they try to do anything else, and you can trap this error and handle it appropriately in your application.

Of course, the smart thing to do would also be to prevent your application code from trying to update read-only tables in the first place.

[Akshay Kumbhar]

No it has to be managed from code only. want to restrict team of people who has access to query manager. other use that db through UI. from UI then can do insert, update, delete so cant do READ_ONLY thing. there is no user as such created to run query. People who has access to query manager UI can run query. Therefore it needs to be done through validations only. So I need basic plan as in how we can check the input text from which if there is any data update query then not to run it.

[Roel De Nijs]

And what about checking the query for all DML and DDL commands like CREATE, UPDATE, ALTER, DELETE, DROP, and so on?

[Tapas Chand]

I remember once we tried something similar. I do not know how much this will be helpful to you.
We had oracle and there were 2 schemas in same service, ABC and XYZ.
ABC was the original schema and XYZ had the select access on ABC.
A separate Connection object was created for XYZ schema only for the requests coming from that particular JSP having the textarea for select query.
So it automatically accepted only select queries. DB would throw exception for any DDL/DML.

But soon we realized exposing the database like this is not at all a good choice.
It was a Core banking DB of a bank and small mistakes in query can result FTS and can kill the performance in peak hours.

[Akshay Kumbhar]

right Roel De Nijs. Thats what I'm looking for. checking the query before it is passed to .sh file.
operation gets complex where we have batch of queries which includes comments also. plus queries can be written on multiple lines. as some people have habit of doing so. validations are written in php. which is not detecting the update(enter key) thing
One idea I got is to run any query and according to response rollback. But this is okay with DML but fails for DDL like truncate.

[Roel De Nijs]

Akshay Kumbhar wrote:operation gets complex where we have batch of queries which includes comments also. plus queries can be written on multiple lines. as some people have habit of doing so. validations are written in php. which is not detecting the update(enter key) thing

That the query gets complex and contains many queries could and should not be a problem at all. Then the function just has to operate on a long string. That people write their queries on multiple lines is definitely a problem. So you have to do first a preprocessing of the query before you start validating. In this pre-processing you have to:

remove all single line comments

replace all white space (tabs, new lines, and so on) with just one space

replace all multiple spaces with just one (1) space

Then you can validate this pre-processed query and for example use the stripos function to discover any "UPDATE TABLE" statement or any other DML/DDL statements.

Akshay Kumbhar wrote:One idea I got is to run any query and according to response rollback. But this is okay with DML but fails for DDL like truncate.

That's a pretty bad idea! Don't do that at all! I can add COMMIT statements to my query and then your rollback will be pretty useless...

[Akshay Kumbhar]

completed the full processing of it. Its working for now. lets see if anyone can break it now.

[Roel De Nijs]

Akshay Kumbhar wrote:completed the full processing of it. Its working for now.

Nice job!

Akshay Kumbhar wrote:lets see if anyone can break it now.

Keep us posted if someone breaks it. So we can adjust the pre-processing and validation of the query so other ranchers could benefit from it.