MySQL users cannot create views (routines)

Created:

2016-11-16 12:54:09 UTC

Modified:

2017-08-16 16:03:18 UTC

0

Was this article helpful?


Have more questions?

Submit a request

MySQL users cannot create views (routines)

Applicable to:

  • Plesk 10.x for Windows
  • Plesk 12.5 for Linux
  • Plesk 11.x for Windows
  • Plesk 12.0 for Windows

Fixed since

The problem described in this KB article has been completely fixed since Plesk 11.5 (see release notes ).

Symptoms

  1. Database users cannot create views. This error appears in the DB WebAdmin:

    #1142 - CREATE VIEW command denied to user 'db1user'@'localhost' for table 'users_active'
  2. Unable to create routine. The following message is displayed

    You do not have the necessary privileges to create a routine

Cause

MySQL database users on Parallels Plesk for Windows are not granted the privilege to create and manage views (routines) by default.

Resolution

To grant permissions to create and manage views (routines) to a database user, follow these steps:

  1. Log in to Parallels Plesk. Go to Tools & Settings → Database Servers → MySQL Server and open Webadmin .
  2. Select the mysql database, open the db table, and click on Edit in the row of corresponding database users:

  1. In case of required view privileges switch permissions Create_view_priv and Show_view_priv to Y and click Go . In case of required routine privileges switch permission Create_routine_priv and Alter_routine_priv to Y and click Go .
  2. Switch to the SQL tab of Webadmin, type
    FLUSH PRIVILEGES;

    and click Go .

After that, the user will be able to create views inside the database.

Have more questions? Submit a request
Please sign in to leave a comment.