Executing Stored Procedures without Permission

JDBC for MySQL - Herong's Tutorial Examples

∟Executing Stored Procedures without Permission

This section provides a test on executing stored procedures without enough permission.

To test what will happen if a Java application is trying to execute a stored procedure without enough permission, first I logged in to MySQL server with as "root", and created the following stored procedure:

herong> local\mysql\bin\mysql -u root

mysql> USE HerongDB;
Database changed

mysql> DELIMITER '/';

mysql> -- Creating the stored procedure
mysql> CREATE PROCEDURE Info(OUT User VARCHAR(80),
    ->   OUT Catalog VARCHAR(80))
    -> BEGIN
    ->   SET User = USER();
    ->   SET Catalog = DATABASE();
    -> END/
Query OK, 0 rows affected (0.00 sec)

mysql> -- Testing the stored procedure
mysql> CALL Info(@User, @Database)/
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @User, @Database/
+----------------+-----------+
| @User          | @Database |
+----------------+-----------+
| root@localhost | herongdb  |
+----------------+-----------+
1 row in set (0.00 sec)

Then I wrote the following program to run this stored procedure as user "Herong":

/* MySqlCallPermissionError.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class MySqlCallPermissionError {
  public static void main(String [] args) {
    Connection con = null;
    try {
      com.mysql.cj.jdbc.MysqlDataSource ds
        = new com.mysql.cj.jdbc.MysqlDataSource();
      ds.setServerName("localhost");
      ds.setPortNumber(3306);
      ds.setDatabaseName("HerongDB");
      ds.setUser("Herong");
      ds.setPassword("TopSecret");
      ds.setServerTimezone(java.util.TimeZone.getDefault().getID());
      con = ds.getConnection();

// Create CallableStatement
      CallableStatement cs = con.prepareCall("CALL Info(?,?)");

// Registering output parameters
      cs.registerOutParameter(1,java.sql.Types.VARCHAR);
      cs.registerOutParameter(2,java.sql.Types.VARCHAR);

// Execute the call statement
      cs.executeUpdate();

// Retrieve output parameters
      System.out.println("User: "+cs.getString(1));
      System.out.println("Database: "+cs.getString(2));

// Close resource
      cs.close();

      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
      e.printStackTrace();
    }
  }
}

To my surprise, the above program executed correctly with JDBC driver 8.0 and MySQL Server 8.0:

herong> java -cp .:mysql-connector-java.jar \
   MySqlCallPermissionError.java

User: Herong@localhost
Database: herongdb

I guess MySQL Server 8.0 has changed its default setting. On MySQL Server 5.5, since "Herong" does not have permission to run stored procedures created by "root" by default, I got the following error message:

herong> java -cp .:mysql-connector-java-5.1.36-bin.jar
   MySqlCallPermissionError

Exception: User does not have access to metadata required to
determine stored procedure parameter types. If rights can not be
granted, configure connection with "noAccessToProcedureBodies=true"
to have driver generate parameters that represent INOUT strings
irregardless of actual parameter types.
...
  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
  ...
  at MySqlCallPermissionError.main(MySqlCallPermissionError.java:19)