This page enumerate all privileges held by users.
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 user, and it shows where that privilege came from, which is either from membership of a group, from another user, or from the group-like object public, and when the privilege is from a group, or user, the page shows which group or 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 |
---|---|
user_id | int4 |
user | varchar |
user_owner | bool |
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 |
origin:type | varchar |
origin:id | int4 |
origin:name | varchar |
privs:schema | varchar |
privs:object | varchar |
privs:cumulative_schema_create | bool |
privs:cumulative_schema_grant_create | bool |
privs:cumulative_schema_usage | bool |
privs:cumulative_schema_grant_usage | bool |
privs:database_create | bool |
privs:database_grant_create | bool |
privs:database_temp | bool |
privs:database_grant_temp | bool |
privs:function_execute | bool |
privs:function_grant_execute | bool |
privs:language_usage | bool |
privs:language_grant_usage | bool |
privs:procedure_execute | bool |
privs:procedure_grant_execute | bool |
privs:schema_create | bool |
privs:schema_grant_create | bool |
privs:schema_usage | bool |
privs:schema_grant_usage | bool |
privs:table_delete | bool |
privs:table_grant_delete | bool |
privs:table_drop | bool |
privs:table_grant_drop | bool |
privs:table_insert | bool |
privs:table_grant_insert | bool |
privs:table_reference | bool |
privs:table_grant_reference | bool |
privs:table_select | bool |
privs:table_grant_select | bool |
privs:table_update | bool |
privs:table_grant_update | bool |
privs:view_drop | bool |
privs:view_grant_drop | bool |
privs:view_select | bool |
privs:view_grant_select | bool |
The user ID. This column is emitted in CSV exports only.
The user name.
True if the user is the owner of the object, false otherwise.
The schema ID. This column is emitted in CSV exports only.
The schema name.
This is the type of the object to which the group has one or more 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.
This column specifies if the privilege is given directly to the user, or comes via group membership, or via the privilege being made public (and so given to all users).
object type | description |
---|---|
group | a group |
public | the group-like object ‘public’ |
user | a user |
The value in this column defines the meaning of the values in the priv_origin_id and priv_origin_name columns.
If the value in priv_origin_type
is group
,
this is the ID of the group the privileges specified in this row come
from.
If the value in priv_origin_type
is public
,
this field is set to NULL
.
If the value in priv_origin_type
is user
,
this field is set to the ID of current owner of the object (who may be
different to the user who actually issued the privilege;
Redshift only stores the current owner, not who issued the
privilege.)
This column is emitted in CSV exports only.
If the value in priv_origin_type
is group
,
this is the name of the group the privileges specified in this row come
from.
If the value in priv_origin_type
is public
,
this field is set to public
.
If the value in priv_origin_type
is user
,
this field is set to the name of current owner of the object (who may be
different to the user who actually issued the privilege;
Redshift only stores the current owner, not who issued the
privilege.)
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”.
Finally, direct grants can also include the privilege to grant the
given privilege to other users (with grant option
).
When this happens, the letter for the privilege is capitalized.
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 create
on
schema privilege, except it is for the grant create
privilege.
This is the matching column for the cumulative usage
on
schema privilege, except it is for the grant usage
privilege.
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 use
objects in a schema, the user must hold the usage
privilege
on the schema. It is often the case that we immediately care whether or
not the user holds usage
on a schema, but do not
immediately care exactly how. This column then takes all the
usage
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.
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 to grant create
on database is
granted, otherwise false.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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 to grant temp
on database is
granted, otherwise false.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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 to grant execute
on function is
granted, otherwise false.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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 to grant usage
on language is
granted, otherwise false.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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 to grant execute
on procedure is
granted, otherwise false.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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 to grant create
on schema is
granted, otherwise false.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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 to grant usage
on schema is
granted, otherwise false.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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 to grant delete
on table is
granted, otherwise false.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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 to grant drop
on table is granted,
otherwise false.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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 to grant insert
on table is
granted, otherwise false.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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 to grant reference
on table is
granted, otherwise false.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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 to grant select
on table is
granted, otherwise false.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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 to grant update
on table is
granted, otherwise false.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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 to grant drop
on view is granted,
otherwise false. This column is emitted in CSV exports only.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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.
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.
True if the privilege to grant select
on view is
granted, otherwise false. This column is emitted in CSV exports
only.
In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.
(Which makes sense, as you could just grant that privilege to yourself.)
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.