MySQL CREATE VIEW privilege

[Hodders]

I'm trying to give a user "CREATE VIEW" and "DROP VIEW" privileges on their own database:

Logged in as root:

But it is failing - see below for mysql transcript:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mediaequals        |
| mysql              |
| wwwtest            |
+--------------------+
4 rows in set (0.00 sec)

mysql> show grants;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'secretstuffhere' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants FOR wwwtest;
+---------------------------------------------------------------------------------------------------------------+
| Grants for wwwtest@%                                                                                          |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wwwtest'@'%' IDENTIFIED BY PASSWORD 'secretstuffhere'                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES ON `wwwtest`.* TO 'wwwtest'@'%' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> grant CREATE VIEW on wwwtest.* TO 'wwwtest'@'%';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'wwwtest'
mysql>

Is there a permission missing from root in order to do this or what ?

Any help would be fab....

 

[Hodders]

Anyone ?

 

[glenimp617]

I think you need to create a new database user from within cpanel and apply it to the database with the correct permissions

 

[Hodders]

Sorted - had to:

use mysql;
Update users SET Create_view_prov = 'Y' WHERE User = 'test';
flush privileges;