GRANT - MySQL

Overview

GRANT authorizes privileges to specific users or roles on MySQL database objects like tables, views, or procedures. It controls access to data and database operations, enabling administrators to define user permissions and enforce data security.

Syntax

GRANT <privileges>
ON <object_type> <object_name>
TO <user>|<role>
[IDENTIFIED BY <password>]
[WITH GRANT OPTION]

Options/Flags

  • privileges: Specify the privileges to grant, including CRUD (SELECT, INSERT, UPDATE, DELETE) operations, GRANT, or administrative rights (ALL).
  • object_type: The type of the database object, such as TABLE, VIEW, or PROCEDURE.
  • object_name: The name of the object on which privileges are being granted.
  • user/role: The username or role name to grant privileges to.
  • IDENTIFIED BY: If granting privileges to a new user, specify their password.
  • WITH GRANT OPTION: Allows the grantee to pass on the granted privileges to other users.

Examples

Granting SELECT privilege on a table:

GRANT SELECT ON users TO john;

Granting all privileges on a database:

GRANT ALL ON my_database TO jane WITH GRANT OPTION;

Common Issues

  • Incorrect object ownership: Ensure that the user or role granting privileges has sufficient permissions on the object.
  • Invalid privileges: Verify that the specified privileges are valid for the object type.
  • Syntax errors: Double-check the command syntax for any errors, such as missing commas or incorrect object names.

Integration

Revoking privileges:

REVOKE <privileges>
ON <object_type> <object_name>
FROM <user>|<role>

Checking granted privileges:

SHOW GRANTS FOR <user>|<role>;
  • REVOKE: Removes privileges granted by GRANT.
  • SHOW GRANTS: Displays the privileges granted to a user or role.
  • CREATE USER: Creates a new user with specified privileges.
  • ALTER USER: Modifies an existing user’s privileges or password.