SQL GRANT ALL and WITH GRANT OPTION: Scope and Examples

6
0

Introduction

Someone responsible for a whole table or database may need many privileges at once. A team lead may also need authority to give other people access.

GRANT ALL assigns all applicable privileges at a chosen scope. WITH GRANT OPTION allows the recipient to pass privileges to others. Choosing the scope and deciding who can share access are separate decisions.

GRANT ALL and Its Scope

GRANT ALL and GRANT ALL PRIVILEGES have the same meaning. The word ALL applies to the specified scope, so all privileges on one table give much less access than all privileges across a server. ALL does not itself include the grant option.

The examples use an existing students table in a practice database named training. The named accounts already exist. Run the administrator statements through an account authorized to grant the requested access. Each scope example uses a separate recipient.

All Privileges on One Table

Give table_manager all applicable privileges on students:

-- Includes ALTER and DROP; DROP also authorizes TRUNCATE.
GRANT ALL PRIVILEGES
ON training.students
TO 'table_manager'@'localhost';

The account receives permission to read, insert, update, and delete records, along with other available table privileges. This is broad access and can authorize destructive operations such as emptying the table.

This grant covers students; it does not grant access to other tables. Use it when someone needs broad responsibility for this particular table.

All Privileges on the Tables of One Database

A person responsible for an application's tables may need access throughout its database. Cover both existing tables and tables created later. A grant on the current set of objects does not always cover future objects automatically.

-- Database scope covers existing and future tables in training.
-- It also includes database-level privileges such as CREATE and DROP.
GRANT ALL PRIVILEGES
ON training.*
TO 'striver'@'localhost';

The access is arranged within training. These grants do not grant privileges on unrelated databases. Broader scope is useful when the account's work spans the application's tables.

All Privileges Across Databases

At global scope, access can extend across databases and include powerful administrative operations. A mistake can affect far more than one application's data, so reserve this level of access for trusted administrators.

Global privileges and superuser status are distinct. GRANT ALL does not universally turn an account into a superuser.

-- Very broad privileges across the server.
-- ALL excludes GRANT OPTION and PROXY.
-- It includes currently registered dynamic privileges, not future ones.
GRANT ALL PRIVILEGES
ON *.*
TO 'admin_user'@'localhost';

Keep routine application accounts limited to the objects and operations they need.

WITH GRANT OPTION

WITH GRANT OPTION gives a recipient authority to grant privileges to other users or roles. It can accompany a selected privilege list or ALL PRIVILEGES.

Suppose Raj needs SELECT and INSERT on students and must be able to give those permissions to Neha. For this example, Raj and Neha have no other relevant privileges or roles and do not own the table.

-- Administrator session.
GRANT SELECT, INSERT
ON training.students
TO 'raj'@'localhost'
WITH GRANT OPTION;
-- Grant option applies at this scope: other table privileges
-- Raj already holds or receives later may also be delegated.

Raj can now use SELECT and INSERT and grant them to Neha. He cannot grant DELETE in this example because he has not received it.

Pass Access to Another User

Connect as Raj and grant the two privileges to Neha:

-- Raj's session.
GRANT SELECT, INSERT
ON training.students
TO 'neha'@'localhost';

Neha can read and insert records. Because Raj omitted WITH GRANT OPTION, and she has no other grant authority, she cannot pass these permissions to another account.

The Risk of Privilege Explosion

Delegation can spread access beyond the people who received it directly from the administrator. If Raj also gives Neha the grant option, she can grant the same privileges to an intern.

-- Raj's session: add delegation authority to Neha's access.
GRANT SELECT, INSERT
ON training.students
TO 'neha'@'localhost'
WITH GRANT OPTION;

The chain continues only when each person passing access onward has the required grant authority. The diagram compares Neha's access before and after she receives the grant option.

How Permissions Spread

How Permissions Spread

As more accounts gain access through others, permissions become harder to track. This growth is often called privilege explosion.

Omit WITH GRANT OPTION when a recipient only needs to use the data. If delegation is required, keep its scope narrow and check the grant authority the account already holds. Omitting the option from a new statement does not remove authority granted earlier.

Summary

GRANT ALL assigns all applicable privileges at the chosen scope, which may cover one table, a database, or broader server access. Existing objects and future objects must both be considered. WITH GRANT OPTION adds the authority to pass privileges to others, and further delegation depends on each recipient's grant authority. Keep broad access and delegation limited to the responsibilities an account actually needs.

CS Core

Read Similar Blogs

Comments0