How to create a read-only MySQL user?

MySQL Logo

Introduction

There are times when you need to create a user only to have a read-only access to a database. The user can view or read the data in the database but they cannot make any changes to the data or the database structure.

Creating a New User Account

To create a read-only database user account for MySQL do the following steps:

  • First, login as a MySQL administrator from your terminal / command prompt using the following command:
mysql -u root -p
  • You’ll prompted to enter the password. Type the password for the root account.
  • Create a new MySQL user account.
CREATE USER 'report'@'%' IDENTIFIED BY 'secret';

The % in the command above means that user report can be used to connect from any host. You can limit the access by defining the host from where the user can connect. Omitting this information will only allow the user to connect from the same machine.

  • Grant the SELECT privilege to user.
GRANT SELECT ON kodejava.* TO 'report'@'%';
  • Execute the following command to make the privilege changes saved and take effect.
FLUSH PRIVILEGES;
  • Type quit to exit from the MySQL shell.

Test the New User Account

  • Now we can try the newly created user account. Start by login with the new user account and provide the corresponding password.
mysql -u report -p
  • Try executing the DELETE command:
mysql> USE kodejava;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DELETE FROM authors;
ERROR 1142 (42000): DELETE command denied to user 'report'@'localhost' for table 'authors'
mysql> UPDATE authors SET name = 'Wayan Saryada' WHERE id = 1;
ERROR 1142 (42000): UPDATE command denied to user 'report'@'localhost' for table 'authors'
mysql>

Creating MySQL database programmatically in Java

There are times that you might need to create database or tables right after you run your program instead of manually creating it. In this example I will show you how you can do this using JDBC and MySQL database. The first thing we need to do as usual when creating a JDBC program is to define a JDBC URL. One thing that you’ll notice here is that we don’t define the database name in the URL. So the URL will be like jdbc:mysql://localhost.

After defining the URL we need to create a connection to the database. We issued the DriverManager.getConnection() method and pass the URL, username and password as the arguments. The next step is to create a PreparedStatement. When we call the preparedStatement() method we pass an SQL command to create the database, which is CREATE DATABASE IF NOT EXISTS DEMODB. This will create the database when there is database with DEMODB exists in the database. Finally call the PreparedStatement‘s execute() method.

Now you can try for your self, start typing the following code snippet in your text editor or IDE and execute it to create the database.

package org.kodejava.example.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class CreateMySQLDatabaseExample {
    public static void main(String[] args) {
        // Defines the JDBC URL. As you can see, we are not specifying
        // the database name in the URL.
        String url = "jdbc:mysql://localhost";

        // Defines username and password to connect to database server.
        String username = "root";
        String password = "";

        // SQL command to create a database in MySQL.
        String sql = "CREATE DATABASE IF NOT EXISTS DEMODB";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

After you are executing the code snippet above you will find a new database named DEMODB created in your MySQL database server.

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How do I register out parameter in CallableStatement?

This example show you how to register out parameter for executing a stored procedure using the CallableStatement.registerOutParameter() method call. We must register the out parameters before the query execution. The registerOutParameter() method takes two parameters, the index of the parameter and the sql data type of the out parameter.

package org.kodejava.example.jdbc;

import java.sql.*;

public class RegisterOutParameter {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Creates a CallableStatement for executing the stored
            // procedure
            String query = "call GetDetailByName(?, ?, ?)";
            CallableStatement cb = connection.prepareCall(query);

            // Sets the input parameter
            cb.setString(1, "Ruby on Rails");

            // Registers the out parameters
            cb.registerOutParameter(2, Types.VARCHAR);
            cb.registerOutParameter(3, Types.DECIMAL);

            // Executes the query
            cb.executeQuery();

            // Gets the query result output
            System.out.println("Name  : " + cb.getString(1));
            System.out.println("Code  : " + cb.getString(2));
            System.out.println("Price : " + cb.getBigDecimal(3));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is the MySQL stored procedure that we call in the code above.

DELIMITER ;;
DROP PROCEDURE IF EXISTS `GetDetailByName`;;
CREATE PROCEDURE `GetDetailByName`(INOUT  product_name  VARCHAR(50),
                                   OUT product_code  VARCHAR(10),
                                   OUT product_price DECIMAL(10, 2))
  BEGIN
    SELECT code INTO product_code FROM products WHERE name = product_name;

    SELECT price INTO product_price FROM products WHERE name = product_name;
  END;;
DELIMITER ;

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How do I limit MySQL query result?

package org.kodejava.example.jdbc;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SqlLimitExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Create PreparedStatement to get all data from database.
            String query = "select count(*) from products";
            PreparedStatement ps = connection.prepareStatement(query);
            ResultSet result = ps.executeQuery();

            int total = 0;
            while (result.next()) {
                total = result.getInt(1);
            }

            System.out.println("Total number of data in database: " +
                total + "\n");

            // Create PreparedStatement to the first 5 records only.
            query = "select * from products limit 5";
            ps = connection.prepareStatement(query);
            result = ps.executeQuery();

            System.out.println("Result fetched with specified limit 5");
            System.out.println("====================================");
            while (result.next()) {
                System.out.println("id:" + result.getInt("id") +
                    ", code:" + result.getString("code") +
                    ", name:" + result.getString("name") +
                    ", price:" + result.getString("price"));
            }

            // Create PreparedStatement to get data from the 4th
            // record (remember the first record is 0) and limited
            // to 3 records only.
            query = "select * from products limit 3, 3";
            ps = connection.prepareStatement(query);
            result = ps.executeQuery();

            System.out.println("\nResult fetched with specified limit 3, 3");
            System.out.println("====================================");
            while (result.next()) {
                System.out.println("id:" + result.getInt("id") +
                    ", code:" + result.getString("code") +
                    ", name:" + result.getString("name") +
                    ", price:" + result.getString("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

An example result of our program is:

Total number of data in database: 9

Result fetched with specified limit 5
====================================
id:1, code:P0000001, name:UML Distilled 3rd Edition, price:25.00
id:3, code:P0000003, name:PHP Programming, price:20.00
id:4, code:P0000004, name:Longman Active Study Dictionary, price:40.00
id:5, code:P0000005, name:Ruby on Rails, price:24.00
id:6, code:P0000006, name:Championship Manager, price:0.00

Result fetched with specified limit 3, 3
====================================
id:5, code:P0000005, name:Ruby on Rails, price:24.00
id:6, code:P0000006, name:Championship Manager, price:0.00
id:7, code:P0000007, name:Transport Tycoon Deluxe, price:0.00

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How do I commit or rollback transaction in JDBC?

Executing a database manipulation command such as insert, update or delete can sometime throws exception due to invalid data. To protect the integrity of our application data we must make sure when we a transaction was failed we must rollback all the executed command so that it affect the state of our data.

In this example we are using MySQL database. To enable transaction capability in MySQL make sure that you are using InnoDB storage engine to create the your tables.

package org.kodejava.example.jdbc;

import java.sql.*;

public class TransactionRollbackExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) throws Exception {
        try (Connection conn =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            conn.setAutoCommit(false);

            String query = "INSERT INTO orders (username, order_date) " +
                "VALUES (?, ?)";
            try (PreparedStatement stmt = conn.prepareStatement(query,
                PreparedStatement.RETURN_GENERATED_KEYS)) {
                stmt.setString(1, "javaduke");
                stmt.setDate(2, new Date(System.currentTimeMillis()));
                stmt.execute();

                ResultSet keys = stmt.getGeneratedKeys();
                int orderId = 1;
                if (keys.next()) {
                    orderId = keys.getInt(1);
                }

                // This is an invalid statement that will cause exception to
                // demonstrate a rollback.
                query = "INSERT INTO order_details (order_id, product_id, " +
                    "quantity, price) VALUES (?, ?, ?, ?, ?)";
                PreparedStatement detailStmt = conn.prepareStatement(query);
                detailStmt.setInt(1, orderId);
                detailStmt.setInt(2, 1);
                detailStmt.setInt(3, 10);
                detailStmt.setDouble(4, 29.99);
                detailStmt.execute();

                // Commit transaction to mark it as a success database operation
                conn.commit();
                System.out.println("Transaction commit...");
            } catch (SQLException e) {
                // Rollback any database transaction due to exception occurred
                conn.rollback();
                System.out.println("Transaction rollback...");
                e.printStackTrace();
            }
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central