Bug #29122 SQLTablePrivileges is totally broken
Submitted: 14 Jun 2007 21:27 Modified: 13 Mar 2014 6:28
Reporter: Jim Winstead Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51 OS:Any
Assigned to: Lawrenty Novitsky CPU Architecture:Any
Tags: SQLTablePrivileges

[14 Jun 2007 21:27] Jim Winstead
Description:
SQLTablePrivileges is just broken. It gets some data by doing a SELECT from mysql.tables_priv and then cobbles together something from that. Unfortunately, that bears little relation to the privileges that the current user actually has on tables.

How to repeat:
This is a rough test, but should be a good start on how this should actually be tested.

DECLARE_TEST(my_tablepriv)
{
  SQLRETURN rc;
  HDBC hdbc1;
  HSTMT hstmt1;
  SQLCHAR   conn[256], conn_out[256];
  SQLSMALLINT conn_out_len;
  bool is51= mysql_min_version(hdbc, "5.1", 3);

  /* If we can't delete from tables_priv, we can't do this test. */
  rc= SQLExecDirect(hstmt, (SQLCHAR *)
                    "DELETE FROM mysql.tables_priv"
                    " WHERE user = 'mysqltest_1'", SQL_NTS);
  if (!SQL_SUCCEEDED(rc))
    return SKIP;

  ok_sql(hstmt, "FLUSH PRIVILEGES");

  ok_sql(hstmt, "DROP TABLE IF EXISTS t_priv1, t_priv2, t_priv3");
  ok_sql(hstmt, "CREATE TABLE t_priv1 (a INT)");
  ok_sql(hstmt, "CREATE TABLE t_priv2 (a INT)");
  ok_sql(hstmt, "CREATE TABLE t_priv3 (a INT)");

  ok_sql(hstmt, "GRANT USAGE ON * TO mysqltest_1 IDENTIFIED BY 'test'");

  ok_sql(hstmt, "GRANT SELECT,INSERT ON t_priv1 TO mysqltest_1");
  ok_sql(hstmt, "GRANT ALL ON t_priv3 TO mysqltest_1");

  /* Connect using our test user. */
  sprintf((char *)conn, "DSN=%s;UID=mysqltest_1;PASSWORD=test;DATABASE=%s",
          mydsn, mydb);
  if (mysock != NULL)
  {
    strcat((char *)conn, ";SOCKET=");
    strcat((char *)conn, (char *)mysock);
  }

  ok_env(henv, SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1));

  ok_con(hdbc1, SQLDriverConnect(hdbc1, NULL, conn, sizeof(conn), conn_out,
                                 sizeof(conn_out), &conn_out_len,
                                 SQL_DRIVER_NOPROMPT));
  ok_con(hdbc1, SQLAllocStmt(hdbc1, &hstmt1));

  ok_stmt(hstmt1, SQLTablePrivileges(hstmt1, (SQLCHAR *)"mysql", SQL_NTS,
                                     NULL, SQL_NTS, NULL, SQL_NTS));

  is(myrowcount(hstmt1) == 0);

  ok_stmt(hstmt1, SQLFreeStmt(hstmt1, SQL_CLOSE));

  ok_stmt(hstmt1, SQLTablePrivileges(hstmt1, NULL, SQL_NTS, NULL, SQL_NTS,
                                     (SQLCHAR *)"test_tabprev1", SQL_NTS));

  is_num(myrowcount(hstmt1), 2);

  ok_stmt(hstmt1, SQLFreeStmt(hstmt1, SQL_CLOSE));

  ok_stmt(hstmt1, SQLTablePrivileges(hstmt1, NULL, SQL_NTS, NULL, SQL_NTS,
                                     (SQLCHAR *)"test_tabprev2", SQL_NTS));

  is(myrowcount(hstmt1) == 0);

  ok_stmt(hstmt1, SQLFreeStmt(hstmt1, SQL_CLOSE));

  ok_stmt(hstmt1, SQLTablePrivileges(hstmt1, NULL, SQL_NTS, NULL, SQL_NTS,
                                     (SQLCHAR *)"test_tabprev3", SQL_NTS));

  /* number of rows to expect depends on the server version */
  is_num(myrowcount(hstmt1), is51 ? 12 : 11);

  ok_stmt(hstmt1, SQLFreeStmt(hstmt1, SQL_CLOSE));

  ok_stmt(hstmt1, SQLTablePrivileges(hstmt1, NULL, SQL_NTS, NULL, SQL_NTS,
                                     (SQLCHAR *)"test_%", SQL_NTS));

  /* number of rows to expect depends on the server version */
  is_num(myrowcount(hstmt1), is51 ? 14 : 13);

  ok_stmt(hstmt1, SQLFreeStmt(hstmt1, SQL_CLOSE));

  ok_stmt(hstmt1, SQLTablePrivileges(hstmt1, NULL, SQL_NTS, NULL, SQL_NTS,
                                     (SQLCHAR *)"test_tabprev%", SQL_NTS));

  /* number of rows to expect depends on the server version */
  is_num(myrowcount(hstmt1), is51 ? 14 : 13);

  ok_stmt(hstmt1, SQLFreeStmt(hstmt1, SQL_CLOSE));

  ok_stmt(hstmt1, SQLTablePrivileges(hstmt1, (SQLCHAR *)"mysql", SQL_NTS,
                                     NULL, SQL_NTS,
                                     (SQLCHAR *)"tables_priv", SQL_NTS));

  is(myrowcount(hstmt1) == 0);

  ok_stmt(hstmt1, SQLFreeStmt(hstmt1, SQL_CLOSE));

  ok_stmt(hstmt1, SQLFreeStmt(hstmt1, SQL_DROP));
  ok_con(hdbc1, SQLDisconnect(hdbc1));
  ok_con(hdbc1, SQLFreeHandle(SQL_HANDLE_DBC, hdbc1));

  return OK;
}

Suggested fix:
There's not actually any way to get the information we need for this from the server.

INFORMATION_SCHEMA.TABLE_PRIVILEGES does not have it.

You can't parse SHOW GRANTS and get it all.
[13 Mar 2014 6:28] Bogdan Degtyariov
This bug has been fixed ages ago and SQLTablePrivileges() gives the correct results.
Closed.