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.
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.