This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Little Book of Impediments (e-book only) and have Tom Perry on-line!
See this thread for details.
Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Securing the db from data changing commands

 
Akshay Kumbhar
Greenhorn
Posts: 13
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 13
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And what about checking the query for all DML and DDL commands like CREATE, UPDATE, ALTER, DELETE, DROP, and so on?
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 13
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
    Greenhorn
    Posts: 13
    Eclipse IDE Java Oracle
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    completed the full processing of it. Its working for now. lets see if anyone can break it now.
     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic