Bug #16751 View DEFINER breaks when using the mysql.host table for privileges

• Submitted: 24 Jan 2006 15:54
• Modified: 8 Sep 2006 13:26
• Reporter: Mark Leith
• Status: Can't repeat
• Category: MySQL Server: Views
• Severity: S3 (Non-critical)
• Version: 5.0-BK
• OS: Any (All)
• Assigned to: Alexander Nozdrin
• CPU Architecture: Any

[24 Jan 2006 15:54] Mark Leith

Description:
Using the mysql.host table for privileges breaks the DEFINER clause for views, with CURRENT user - resulting in:

mysql> desc v1;
ERROR 1356 (HY000): View 'bobs_testing.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

How to repeat:

GRANT USAGE ON *.* TO 'bobs'@'' IDENTIFIED BY 'bobs';
GRANT ALL PRIVILEGES ON `bobs_testing`.* TO 'bobs'@'';

INSERT INTO mysql.host (Host, Db) VALUES ('/*insert remote host*/','bobs_testing');

CREATE database bobs_testing;

FLUSH PRIVILEGES;

/* From remote host */

mysql -u bobs -pbobs -h dbhost
USE bobs_testing;

CREATE TABLE t1 (i int, j varchar(100));

CREATE DEFINER = CURRENT_USER SQL SECURITY DEFINER VIEW v1 
AS SELECT * FROM t1;

DESC v1;

SELECT * FROM v1;

[8 Sep 2006 13:26] Alexander Nozdrin

I can not repeat this bug in 5.0.25BK.

-------------------------------------------------------------------
On host 'alik':
-------------------------------------------------------------------
(alik)[~/MySQL/devel/5.0-rt-bug19718]> ./client/mysql -u root mysql

# GRANT USAGE is not supported any more.

mysql> grant all privileges on db1.* to u1@'';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into mysql.host(Host, Db) values('booka', 'db1');
Query OK, 1 row affected (0.00 sec)

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
-------------------------------------------------------------------

-------------------------------------------------------------------
On host 'booka':
-------------------------------------------------------------------
(booka)[~/MySQL/devel/5.0-rt-bug16751]> ./client/mysql --host=alik --user=u1
mysql> use db1;
ERROR 1044 (42000): Access denied for user 'u1'@'' to database 'db1'

(booka)[~/MySQL/devel/5.0-rt-bug16751]> ./client/mysql --host=alik --user=u1 db1
ERROR 1044 (42000): Access denied for user 'u1'@'' to database 'db1'
-------------------------------------------------------------------