Bug #5976 "Access denied" error during VIEW creation if user has databse level privileges

• Submitted: 8 Oct 2004 11:05
• Modified: 8 Nov 2004 9:56
• Reporter: Victoria Reznichenko
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.2-alpha-debug-log
• OS: Linux (Linux)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[8 Oct 2004 11:05] Victoria Reznichenko

Description:
When I try to create VIEW I get "Access denied" error, though user has all necessary privileges.

mysql> create table t(col int);
Query OK, 0 rows affected (0.07 sec)

mysql> create view view_t as select col from t;
ERROR 1143 (42000): ANY command denied to user 'vita'@'localhost' for column 'col' in table 't'
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| vita@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> show grants for 'vita'@'localhost';
+--------------------------------------------------------+
| Grants for vita@localhost                              |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'vita'@'localhost'               |
| GRANT ALL PRIVILEGES ON `test`.* TO 'vita'@'localhost' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

How to repeat:
1) create user with all privileges on the specific database.
2) connect as this user
3)
create table t(col int);
create view view_t as select col from t;

[14 Oct 2004 15:45] Robert Kruus

Can be solved by adding global level create view privilege --
grant create view on *.* for ....... ;

But then oddly enough, you get show databases and global use prvileges
(Though here I have 5.0.1-alpha).

mysql> show grants for bob@localhost;
+-----------------------------------------------------------------------------------------------------------------------------+
| Grants for bob@localhost                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE VIEW, SHOW *.* TO 'bob'@'localhost' IDENTIFIED BY PASSWORD '------------------------------------------' |
| GRANT ALL PRIVILEGES ON `maycock`.* TO 'bob'@'localhost'                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

and:
mysql> show databases;
+----------+
| Database |
+----------+
| maycock  |
| mysql    |
+----------+
2 rows in set (0.00 sec)

though the mysql database is still unusable.

[22 Oct 2004 12:46] Oleksandr Byelkin

ChangeSet 
  1.1665 04/10/22 15:43:22 [e-mail] +3 -0 
  fixed db/user privileges getting (BUG#5976)

[8 Nov 2004 9:56] Oleksandr Byelkin

Thank you for bugreport. Bugfix is pushed into source repository.