BUG: GRANT/REVOKE don"t play nice with query cache

If the query cache is on, the GRANT and REVOKE statements don't
cause queries to be invalidated properly that access the grant tables.
It's necessary to FLUSH TABLES or RESET QUERY CACHE to get changes to
show up.

How-to-repeat:

SELECT host,user,db FROM db;
GRANT some database-level privileges, which adds a row to the db table.

Run the SELECT statement again. The result will be the same, though
it shouldn't be.

Then issue FLUSH TABLES or RESET QUERY CACHE and run the SELECT statement
again. The result will now differ.

Then REVOKE the privileges. It'll be necessary to reset the cache again
to get the change to show up.

I suppose maybe the same effect will occur for SET PASSWORD...

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12647@lists.mysql.com
To unsubscribe, e-mail

Re: BUG: GRANT/REVOKE don"t play nice with query cache

Hi!

On Sun, Oct 06, 2002 at 08:18:49PM -0500, Paul DuBois wrote:
> If the query cache is on, the GRANT and REVOKE statements don't
> cause queries to be invalidated properly that access the grant tables.
> It's necessary to FLUSH TABLES or RESET QUERY CACHE to get changes to
> show up.
>
> How-to-repeat:
>
> SELECT host,user,db FROM db;
> GRANT some database-level privileges, which adds a row to the db table.
>
> Run the SELECT statement again. The result will be the same, though
> it shouldn't be.
>
> Then issue FLUSH TABLES or RESET QUERY CACHE and run the SELECT statement
> again. The result will now differ.
>
> Then REVOKE the privileges. It'll be necessary to reset the cache again
> to get the change to show up.
>
> I suppose maybe the same effect will occur for SET PASSWORD...

Thank you for good bug report!

Now problem is solved in 4.0 bk repository. Here is patch for it fixing:

diff -Nrc a/sql/sql_cache.cc b/sql/sql_cache.cc
*** a/sql/sql_cache.cc Tue Oct 8 00:44:56 2002
--- b/sql/sql_cache.cc Tue Oct 8 00:44:56 2002
***************
*** 342,347 ****
--- 342,353 ----
#define DUMP(C)
#endif

+ #ifdef FN_NO_CASE_SENCE
+ #define DB_NAME_PREPROCESS(C) tolower(C)
+ #else
+ #define DB_NAME_PREPROCESS(C) (C)
+ #endif
+
const char *query_cache_type_names[]= { "OFF", "ON", "DEMAND",NullS };
TYPELIB query_cache_type_typelib=
{
***************
*** 1378,1388 ****

VOID(hash_init(&queries,def_query_hash_size, 0, 0,
query_cache_query_get_key, 0, 0));
! #ifndef __WIN__
VOID(hash_init(&tables,def_table_hash_size, 0, 0,
query_cache_table_get_key, 0, 0));
#else
! // windows case insensitive file names work around
VOID(hash_init(&tables,def_table_hash_size, 0, 0,
query_cache_table_get_key, 0,
(lower_case_table_names?0:HASH_CASE_INSENSITIVE)));
--- 1384,1394 ----

VOID(hash_init(&queries,def_query_hash_size, 0, 0,
query_cache_query_get_key, 0, 0));
! #ifndef FN_NO_CASE_SENCE
VOID(hash_init(&tables,def_table_hash_size, 0, 0,
query_cache_table_get_key, 0, 0));
#else
! // windows, OS/2 or other case insensitive file names work around
VOID(hash_init(&tables,def_table_hash_size, 0, 0,
query_cache_table_get_key, 0,
(lower_case_table_names?0:HASH_CASE_INSENSITIVE)));
***************
*** 2442,2451 ****
tables_used->table->file->has_transactions());

if (tables_used->table->db_type == DB_TYPE_MRG_ISAM ||
! tables_used->table->tmp_table != NO_TMP_TABLE)
{
DBUG_PRINT("qcache",
! ("select not cacheable: used MRG_ISAM or temporary table(s)"));
DBUG_RETURN(0);
}
if (tables_used->table->db_type == DB_TYPE_MRG_MYISAM)
--- 2448,2463 ----
tables_used->table->file->has_transactions());

if (tables_used->table->db_type == DB_TYPE_MRG_ISAM ||
! tables_used->table->tmp_table != NO_TMP_TABLE ||
! (tables_used->db_length == 5 &&
! DB_NAME_PREPROCESS(tables_used->db[0])=='m' &&
! DB_NAME_PREPROCESS(tables_used->db[1])=='y' &&
! DB_NAME_PREPROCESS(tables_used->db[2])=='s' &&
! DB_NAME_PREPROCESS(tables_used->db[3])=='q' &&
! DB_NAME_PREPROCESS(tables_used->db[4])=='l'))
{
DBUG_PRINT("qcache",
! ("select not cacheable: used MRG_ISAM, temporary or system table(s)"));
DBUG_RETURN(0);
}
if (tables_used->table->db_type == DB_TYPE_MRG_MYISAM)


--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12663@lists.mysql.com
To unsubscribe, e-mail