HAS_SCHEMA_PRIVILEGE
Overview
The has_schema_privilege function checks whether the current user has specific privileges on a schema.
Syntax
There are two versions of the has_schema_privilege function:
See the breakdown of both versions:
-
schema: The name of the schema for which you want to check privileges. It can be any string value or string columns from other tables. -
user: The name of the user who has the privileges. It can be any string value. -
privilege: The privilege parameter specifies the specific privilege you want to check for in the schema. Currently, the function supportscreateandusage.
The comparison for the privilege is case-insensitive, so you can use lowercase or uppercase letters for the privilege name (e.g., CREATE or create are both valid).
Output
Both versions of the has_schema_privilege function will always return a TRUE (t) value.
Examples
Case #1: Check for CREATE Privilege
In this example, we will use the has_schema_privilege function to determine if the current user has the create privilege on a schema named “public.”
This query will return TRUE, which means the current user has a create privilege on the “public” schema.
Case #2: Check for USAGE Privilege
You can also use the has_schema_privilege function to check for the usage privilege on a schema. For example, to check if the current user can create objects in the “public” schema:
This query will return TRUE, which means the current user has usage privilege on the “public” schema.