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.