Win a copy of Cloud Native PatternsE this week in the Cloud forum
or Natural Language Processing in the AI/ML forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Knute Snortum
  • Rob Spoor
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Piet Souris
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

Securing the db from data changing commands

 
Greenhorn
Posts: 13
Eclipse IDE Oracle Java
  • 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.
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • 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 Oracle Java
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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?
 
Ranch Hand
Posts: 624
9
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 Oracle Java
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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 Oracle Java
    • 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: 11604
    178
    Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
    • 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.
     
    It is sorta covered in the JavaRanch Style Guide.
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!