javajdbccratedb

Unable to use Crate JDBC 2.6.0 in read only mode


I want to restrict delete/update statements

This is what I am trying

ResultSet rs = stmt.executeQuery("delete from test where id=243640033")

Ideally executeQuery method of JDBC should not allow update, delete and etc. But Crate Database is simply executing delete queries. Then I tried connection.setReadOnly(true) and it did not work

Is there any way to restrict Crate JDBC doing update/delete/drop operations from stmt.executeQuery(somequery) method ?


Solution

  • You could create a CrateDB user with Data Query Language (DQL) privileges only and subsequently use this user to connect with JDBC. This will prevent all INSERT / UPDATE / DELETE / DROP operations.

    CREATE USER read_only WITH (password = '<secure-password>');
    GRANT DQL TO read_only;
    

    If required you can further restrict access only to specific schemas, tables or views. Compare the documentation on how to achieve this.