Executing Stored Procedures without Permission

<< MySQL - JBDC CallableStatement

<< JDBC Tutorials - Herong's Tutorial Notes

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:

C:\>\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) 2007 by Dr. Herong Yang. All rights reserved.
 */
import java.sql.*;
public class MySqlCallPermissionError {
  public static void main(String [] args) {
    Connection con = null;
    try {
      com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds 
        = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
      ds.setServerName("localhost");
      ds.setPortNumber(3306);
      ds.setDatabaseName("HerongDB");
      ds.setUser("Herong");
      ds.setPassword("TopSecret");
      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();
    }
  }
}

Since "Herong" does not have permission to run stored procedures created by "root" by default, I got the following error message:

C:\>javac -cp .;\local\lib\mysql-connector-java-5.0.7-bin.jar
  MySqlCallPermissionError.java

C:\>java -cp .;\local\lib\mysql-connector-java-5.0.7-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:910)
  at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(...)
  at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(...)
  at com.mysql.jdbc.CallableStatement.determineParameterTypes(...)
  at com.mysql.jdbc.CallableStatement.<init>(...)
  at com.mysql.jdbc.Connection.parseCallableStatement(...)
  at com.mysql.jdbc.Connection.prepareCall(Connection.java:4610)
  at com.mysql.jdbc.Connection.prepareCall(Connection.java:4584)
  at MySqlCallPermissionError.main(MySqlCallPermissionError.java:20)

Sections in This Chapter

Overview of CallableStatement Objects

"CREATE PROCEDURE" - Creating a Simple Procedure

Creating Procedures with IN and OUT Parameters

Creating Procedures with INOUT Parameters

Creating Procedures with Multiple Queries

Creating CallableStatement Objects with prepareCall()

Capturing ResultSet with executeQuery()

Creating CallableStatement Objects with Parameters

Common Errors with CallableStatement Parameters

Creating CallableStatement Objects with INOUT Parameters

Retrieving Multiple ResultSet Objects

Executing Stored Procedures without Permission

getProcedures() - Listing Stored Procedures