This page enumerate all privileges held by groups.
The underlying SQL examines the ACL columns in the system tables and so directly enumerates privileges; it does not rely upon the system functions which indicate if a given user or group has a given privilege on a given object.
As such, the page shows every privilege for every group, and it shows where that privilege came from - which for a group, is always a user - and it shows which user.
(Note that in Redshift/Postgres, the current owner of an object is always consider the granter of a privilege on the object. So if a given object is owned by a user, who grants a privilege, and then ownership of the object changes, the new owner will be considered the granter of the privilege.)
There is currently (2023-01-09) no support for roles. I need to integrate roles into this page.
| Name | Type |
|---|---|
| group_id | int8 |
| group | varchar |
| object:schema_id | int8 |
| object:schema | varchar |
| object:type | varchar |
| object:subtype | varchar |
| object:return_id | int8 |
| object:return | varchar |
| object:id | int8 |
| object:name | varchar |
| object:arguments_ids | varchar |
| object:arguments | varchar |
| privs:granter_id | int8 |
| privs:granter | varchar |
| privs:schema | varchar |
| privs:object | varchar |
| privs:cumulative_schema_create | bool |
| privs:cumulative_schema_usage | bool |
| privs:database_create | bool |
| privs:database_temp | bool |
| privs:function_execute | bool |
| privs:language_usage | bool |
| privs:procedure_execute | bool |
| privs:schema_create | bool |
| privs:schema_usage | bool |
| privs:table_delete | bool |
| privs:table_drop | bool |
| privs:table_insert | bool |
| privs:table_reference | bool |
| privs:table_select | bool |
| privs:table_update | bool |
| privs:view_drop | bool |
| privs:view_select | bool |
The group ID. This column is emitted in CSV exports only.
Group IDs are unique across all databases.
The group name.
The schema ID. This column is emitted in CSV exports only.
Schema name. This is the schema of the object this row describes to which the group has one or more privileges.
The following objects cannot belong to a schema and for them this
column is NULL;
| object type |
|---|
| database |
| language |
Strictly, schemas also do not belong to schemas, but this view
will show the schema name of a schema for that schema, rather
than NULL.
This is the type of the object to which the group has a privilege or privileges. These are;
| Object Type |
|---|
| database |
| function |
| language |
| procedure |
| schema |
| table |
| view |
Unfortunately, AWS has overloaded some of these terms with what can
be radically different meanings (schema for example is both
a conventional SQL schema, but also a pointer to an external metadata
store for Redshift Spectrum tables).
If for this type of object (see object:type) there are a
number of different subtypes (such as with functions), this contains the
keyword Redshift uses for that subtype.
| Object Type | Object Subtype | Description | Brief Description |
|---|---|---|---|
| database | athena | Athena Data Catalog | adc |
| database | hive | Hive Data Catalog | hdc |
| database | internal | Normal Redshift database | db |
| database | postgres | Postgres Federated database | pfd |
| database | mysql | mySQL Federated database | mfd |
| function | c | C function | c |
| function | exfunc | Lambda function | ld |
| function | internal | Postgres built-in function | p |
| function | mlfunc | Machine learning model function | ml |
| function | plpythonu | Python function | py |
| function | sql | SQL function | s |
| language | c | C language | c |
| language | exfunc | Lambda language | ld |
| language | internal | Postgres built-in | p |
| language | mlfunc | Machine learning language | ml |
| language | plpgsql | PL/pgSQL language | pl |
| language | plpythonu | Python language | py |
| language | sql | SQL language | s |
| procedure | plpgsql | PL/gpSQL procedure | pr |
| schema | external | External schema | es |
| schema | internal | Normal Redshift schema | s |
| table | external | External table | et |
| table | internal | Normal Redshift table | t |
| view | internal | Normal Redshift view | v |
| view | materialized | Materialized view | v |
If the object is a function, this is the data type ID of the return
value, otherwise NULL. This column is emitted in CSV
exports only.
If the object is a function, this is the base data type name (so no
length, precision or scale) of the return value, otherwise
NULL. This column is emitted in CSV exports only.
The ID of the object this row describes, to which one or more privileges are granted. The object can be of any type which allows grants, and so the name can be for a table, a view, a programming language, etc.
This column is emitted in CSV exports only.
The name of the object this row describes, to which one or more privileges are granted. The object can be of any type which allows grants, and so the name can be for a table, a view, a programming language, etc.
If the object is a function or procedure, this is a comma separated
string of the data type IDs for the arguments, otherwise
NULL. This column is emitted in CSV exports only.
If the object is a function or procedure, this is a comma separated
string of the base (so not adorned with lengths, precision, scale, etc)
data type names for the arguments, otherwise NULL. This
column is emitted in CSV exports only.
User ID of the owner of the object. Postgres/Redshift do not keep track of which user actually issued privileges. Rather, they keep track only of the current owner of the object in question, and regard the current owner as the granter of the privileges on that object. This column is emitted in CSV exports only.
User name of the owner of the object. Postgres/Redshift do not keep track of which user actually issued privileges. Rather, they keep track only of the current owner of the object in question, and regard the current owner as the granter of the privileges on that object.
The privs held by this user on the schema of the object (given for here for convenience).
This column shows the privileges granted for the given object to the
given group or user. Where the privileges available to Redshift are
reduced from those in Postgres, it is in fact very nearly (there is one
exception, covered below) possible to use the first letter of the actual
name of the privileges being granted, so s means
select, i means insert, etc.
I could be wrong, but I think this more natural and intuitive than the slightly obscure mapping Postgres uses (particularly so where Postgres also uses capitalization to convey meaning) and as such, I have adopted this approach.
Each row shows always the privileges made to the schema the object is in, so there is no need to hunt around to find the schema grants; the owner flag comes first, then a period, then the schema privileges come first (create, usage), then another period, then the object grants.
In all cases, when a privilege is not given, a blank space is printed, to keep the layout constant and so easier to read.
Database and languages do not belong in a schema. In these cases, the schema privileges are both spaces and the period which lies between the schema privileges and the object privileges is also replaced by a space.
The full set of privileges then is this;
o.ctu.cu.dfipsux
Which corresponds to;
| Letter | Grant |
|---|---|
| o | this user is the owner of the object |
| . | separator between owner and following privileges |
| c | create schemas in database |
| t | create temporary tables in database |
| u | usage on a language |
| . | separator between global object privileges and schema-based privileges |
| c | create objects in schema |
| u | use objects in schema |
| . | separator between schema privileges and object privileges |
| d | delete on table |
| f | create references on columns in table |
| i | insert on table |
| p | drop on table or view |
| s | select on table or view |
| u | update on table |
| x | execute on function or procedure |
I chose f for references (for “foreign key”), rather
than r, because r makes me think of
“read”.
The privs held by this user on the schema of the object. A given user
can receive the same privs on the same object from many sources. To
create objects in a schema, the user must hold the create
privilege on the schema. It is often the case that we immediately care
whether or not the user holds create on a schema, but do
not immediately care exactly how. This column then takes all the
create privileges on the schema, from all sources, and
reduces them to a “yes or no”, which is shown in this column.
This column is emitted in CSV exports only.
This is the matching column for the cumulative usage on
schema privilege, except it is for the grant usage
privilege.
True if the privilege create on database is granted,
otherwise false.
This privilege allows a user to create schemas in a database.
This column is emitted in CSV exports only.
True if the privilege temp on database is granted,
otherwise false. This column is emitted in CSV exports only.
True if the privilege execute on function is granted,
otherwise false. This column is emitted in CSV exports only.
True if the privilege usage on language is granted,
otherwise false. This column is emitted in CSV exports only.
True if the privilege execute on procedure is granted,
otherwise false. This column is emitted in CSV exports only.
True if the privilege create on schema is granted,
otherwise false.
This privilege allows a user to create tables in a schema.
This column is emitted in CSV exports only.
True if the privilege usage on schema is granted,
otherwise false. This column is emitted in CSV exports only.
True if the privilege delete on table is granted,
otherwise false. This column is emitted in CSV exports only.
True if the privilege drop on table is granted,
otherwise false. This column is emitted in CSV exports only.
True if the privilege insert on table is granted,
otherwise false. This column is emitted in CSV exports only.
True if the privilege reference on table is granted,
otherwise false.
This privilege allows a user to create references (foreign keys) on a table, but to do so, this privilege must be held on both the table itself and the foreign key table.
This column is emitted in CSV exports only.
True if the privilege select on table is granted,
otherwise false. This column is emitted in CSV exports only.
True if the privilege update on table is granted,
otherwise false. This column is emitted in CSV exports only.
True if the privilege drop on view is granted, otherwise
false.
In Redshift/Postgres, with regard to privileges, tables and views are treated identically; whenever the docs or SQL commands say “table”, they mean “table or view”.
I have separated tables and views, because views support only a subset of the privileges of tables. You can on a view grant privileges which only make sense with a table, such as delete, but they have no meaning.
As such, I enumerate the select and drop
privileges (and the privileges to grant them) for views.
This column is emitted in CSV exports only.
True if the privilege select on view is granted,
otherwise false.
In Redshift/Postgres, with regard to privileges, tables and views are treated identically; whenever the docs or SQL commands say “table”, they mean “table or view”.
I have separated tables and views, because views support only a subset of the privileges of tables. You can on a view grant privileges which only make sense with a table, such as delete, but they have no meaning.
As such, I enumerate the select and drop
privileges (and the privileges to grant them) for views.
This column is emitted in CSV exports only.