MySQL provides granular access control when working with Views. Permissions behave differently depending on whether queries run under the definer's privileges or the invoker's privileges.

Key Permission Models for Views

1
Views in MySQL operate under two security modes: SQL SECURITY DEFINER (default) and SQL SECURITY INVOKER.
2
Under DEFINER mode, the user's permissions are not directly checked against the base tables — rather, the view runs with the privileges of the view creator.
3
Under INVOKER mode, the querying user must have individual privileges on both the view and the underlying base tables.

How Permissions Are Evaluated

1
To access a view, a user must be granted at least SELECT (or INSERT/UPDATE/DELETE if applicable) on the view itself.
2
If the view is defined with SQL SECURITY DEFINER, the user does NOT need permissions on the base tables — only the view owner does.
3
If SQL SECURITY INVOKER is used, the user must have permissions on both the view and its underlying tables.

Using Views for Security and Privilege Restriction

1
Views can hide sensitive columns by exposing only selected fields.
2
A user may be granted SELECT on a view without gaining SELECT on the base table.
3
This enables column-level and row-level security.
4
Example: A view that hides salary information in an employees table.

Important Implications

1
If a user attempts INSERT/UPDATE/DELETE through a view, MySQL checks whether the view is updatable and whether privileges exist.
2
Under DEFINER mode, the DEFINER must have table privileges for the operations to succeed.
3
Views cannot be used to bypass base table restrictions if the DEFINER lacks permissions.

In summary, granting privileges on a view does not automatically grant access to its underlying tables. MySQL’s SQL SECURITY modes define whose permissions apply, making views a powerful mechanism for secure, controlled data access.

Difficulty: 6/10

Topics: View permissions, Privilege inheritance, Security context

Scenario Questions

0-2 years experience

1
If a user can SELECT from a view but gets 'access denied' when querying the underlying table directly, what’s the most likely reason?
2
You create a view with SQL SECURITY DEFINER and grant SELECT to a junior analyst — they can query the view but can't SELECT from the base table. Is this expected? Why?

2-5 years experience

1
A reporting feature broke after a DBA changed the view’s definer account — users now get permission errors even though their roles haven’t changed. What would you check first?
2
Your team built a view to expose customer data to a third-party app, but security flagged it as a risk. Why might granting access to the view instead of the table be dangerous if not configured properly?

5-8 years experience

1
You’re designing a multi-tenant analytics layer using views — how do you ensure tenants can’t access each other’s data via view definer privileges, and what edge cases might slip through?
2
A legacy view uses DEFINER with a service account that has broad privileges. You want to migrate to least-privilege access without breaking existing apps. What’s your migration strategy?

8+ years experience

1
Your company is consolidating 20+ microservices that all query the same database via views — how do you design a permission model that scales securely across teams without creating a maintenance nightmare?
2
You’re migrating from a monolith to a service-oriented architecture, and legacy views with DEFINER privileges are tightly coupled to service accounts. How do you decouple this without introducing security gaps or downtime?

Follow-up Questions

❓What happens if the view definer's account gets revoked?
❓How would you audit which users can access data through views without direct table access?
❓Can a view bypass row-level security on the base table?