SQL USAGE

What is the SQL USAGE privilege?

USAGE is a privilege keyword that grants the right to reference or use an object without giving additional rights such as SELECT or INSERT.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL USAGE:

SQL USAGE Full Explanation

USAGE appears in GRANT and REVOKE statements to give a role the minimum permission required to reference certain objects. In PostgreSQL it applies to schemas, sequences, types, domains, languages, foreign data wrappers, servers and large objects. It lets the grantee access or call the object but not modify it. For example, USAGE on a schema lets a user access objects inside the schema that they already have rights to, while USAGE on a sequence lets them call nextval and currval. In MySQL and MariaDB USAGE is a global privilege that effectively grants no privileges; its main purpose is to create an account or change attributes without assigning object permissions. In Snowflake or Redshift USAGE is required on higher level containers such as databases and schemas before lower level privileges can be used. USAGE does not imply rights like SELECT, INSERT, UPDATE or EXECUTE, so it is often paired with those when needed. Revoking USAGE blocks all interaction with the object even if other privileges remain.

SQL USAGE Syntax

-- PostgreSQL / Redshift / Snowflake
GRANT USAGE ON SCHEMA schema_name TO role_name;
GRANT USAGE ON SEQUENCE sequence_name TO role_name;
REVOKE USAGE ON SCHEMA schema_name FROM role_name;

-- MySQL / MariaDB
GRANT USAGE ON *.* TO 'user'@'host' [IDENTIFIED BY 'pwd'];

SQL USAGE Parameters

object_type (string) - The category of object (SCHEMA, SEQUENCE, DATABASE, etc.)
object_name (identifier) - Name of the object to which the privilege applies
grantee (identifier) - User or role receiving the privilege
WITH GRANT OPTION (keyword, optional) - Allows the grantee to grant the same privilege to others

Example Queries Using SQL USAGE

-- Allow analysts to reference objects inside the reporting schema
GRANT USAGE ON SCHEMA reporting TO analyst_role;

-- Let application read values from an auto increment sequence
GRANT USAGE ON SEQUENCE orders_order_id_seq TO app_user;

-- Create a MySQL account without privileges but with a password
GRANT USAGE ON *.* TO 'guest'@'%' IDENTIFIED BY 'readonly';

-- Remove USAGE so a role can no longer access the schema
REVOKE USAGE ON SCHEMA reporting FROM analyst_role;

Expected Output Using SQL USAGE

Each GRANT or REVOKE returns "GRANT" or "REVOKE" (or "Query OK" in MySQL) and updates the system catalog so the specified user or role has or loses the USAGE privilege

Use Cases with SQL USAGE

Let users access objects in a schema without letting them create new ones - Allow a role to call nextval on a sequence that drives surrogate keys
Give read only tools the ability to connect to a Snowflake database hierarchy
Create or alter a MySQL account without handing out object privileges

Common Mistakes with SQL USAGE

Assuming USAGE lets you SELECT from tables; it does not
Forgetting to grant USAGE on a schema in PostgreSQL, causing name resolution errors even when table SELECT is granted
Mixing up USAGE with CONNECT; CONNECT governs database logins in PostgreSQL
Revoking USAGE but forgetting dependent privileges, leading to orphaned grants

First Introduced In

PostgreSQL 7.3, MySQL 5.0

Frequently Asked Questions

What objects can have USAGE in PostgreSQL?

Schemas, sequences, data types, domains, languages, foreign data wrappers, servers and large objects can all accept USAGE.

Does USAGE include SELECT rights on a sequence?

Yes. In PostgreSQL USAGE on a sequence implicitly allows nextval, currval and setval, which read or advance the sequence.

How do I completely block a role from a schema?

REVOKE USAGE ON SCHEMA schema_name FROM role_name; Any attempt to access objects inside that schema will fail unless USAGE is re-granted.

Can I combine USAGE with WITH GRANT OPTION?

Yes. GRANT USAGE ... WITH GRANT OPTION lets the grantee pass the same USAGE privilege to other roles.