When attempting to perform a mysqldump operation that includes views, you might encounter this common error:

mysqldump: Couldn't execute 'show create table view_name': 
SHOW VIEW command denied to user 'username'@'host' for table 'view_name' (1142)

This occurs because the MySQL user account lacks the necessary SHOW VIEW privilege, which is distinct from regular table privileges.

For successful view-related operations, a MySQL user needs these specific privileges:

• SHOW VIEW - Required to see the view definition
• CREATE VIEW - Needed if creating new views
• DROP VIEW - Required for view removal

To grant only view-related privileges without giving full database access:

-- Grant SHOW VIEW privilege for a specific view
GRANT SHOW VIEW ON database_name.view_name TO 'username'@'host';

-- Grant SHOW VIEW privilege for all views in a database
GRANT SHOW VIEW ON database_name.* TO 'username'@'host';

-- Grant all view-related privileges
GRANT SHOW VIEW, CREATE VIEW, DROP VIEW ON database_name.* TO 'username'@'host';

For a backup user that needs to dump all database views:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON your_database.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Check the granted privileges with:

SHOW GRANTS FOR 'username'@'host';

Look for entries containing SHOW VIEW in the output to confirm the privilege assignment.

• Always FLUSH PRIVILEGES after granting permissions
• MySQL 5.0.1+ requires SHOW VIEW specifically for view operations
• The error may persist until the user reconnects to MySQL

When working with MySQL views in production environments, administrators often encounter permission issues during backup operations. The specific error SHOW VIEW command denied typically occurs when:

• Performing mysqldump operations
• Using backup scripts that rely on view definitions
• Migrating databases between environments

To properly dump view definitions, MySQL requires these specific privileges:

SHOW VIEW
SELECT (on the view itself)

Here's the precise syntax to grant only the necessary view-related privileges:

GRANT SELECT, SHOW VIEW ON database_name.view_name TO 'username'@'host';

For example, to grant access to a specific view:

GRANT SELECT, SHOW VIEW ON inventory.view_household TO 'backup'@'localhost';

For backup purposes, you might want to grant these privileges on all views in a database:

GRANT SELECT, SHOW VIEW ON database_name.* TO 'backup'@'localhost';

After granting privileges, verify them with:

SHOW GRANTS FOR 'backup'@'localhost';

Sample output should include:

GRANT SELECT, SHOW VIEW ON inventory.view_household TO 'backup'@'localhost'

• Always specify the minimum required privileges
• Avoid using wildcard grants (*.*) for production backup accounts
• Regularly audit privileges with SHOW GRANTS
• Consider creating a dedicated backup user with only view-related privileges