SHOW GRANTS
Muestra las concesiones para un usuario, rol u objeto. El objeto puede ser una base de datos, un esquema, una tabla o una función. Al especificar un objeto, como una tabla o una función, debe calificarlo con una notación de dos o tres partes. Por ejemplo, schema_name.table_name o database_name.schema_name.table_name.
Sintaxis
A continuación, se muestra la sintaxis para mostrar los permisos de un objeto. Tenga en cuenta que la segunda forma de especificar una función solo es válida para esquemas y bases de datos externos creados a partir de un recurso compartido de datos.
SHOW GRANTS ON { DATABASE database_name | FUNCTION {database_name.schema_name.function_name | schema_name.function_name } ( [ [ argname ] argtype [, ...] ] ) | FUNCTION {database_name.schema_name.function_name | schema_name.function_name } | SCHEMA {database_name.schema_name | schema_name} | { TABLE {database_name.schema_name.table_name | schema_name.table_name} | table_name } } [FOR {username | ROLE role_name | PUBLIC}] [LIMIT row_limit]
A continuación, se muestra la sintaxis para mostrar los permisos de un usuario o rol.
SHOW GRANTS FOR {username | ROLE role_name} [LIMIT row_limit]
Parámetros
- database_name
-
Nombre de la base de datos en la que se muestran las concesiones.
- function_name
-
Nombre de la función en la que se muestran las concesiones.
- schema_name
-
Nombre del esquema en el que se muestran las concesiones.
- table_name
-
Nombre de la tabla en la que se muestran las concesiones.
- FOR username
-
Indica que se muestran concesiones para un usuario.
- FOR ROLE role_name
-
Indica que se muestran concesiones para un rol.
- FOR PUBLIC
-
Indica que se muestran concesiones para PUBLIC.
- row_limit
-
Número máximo de filas que se devolverán. El valor de row_limit está entre 0 y 10 000.
Ejemplos
El siguiente ejemplo muestra todas las concesiones en una base de datos denominada dev.
SHOW GRANTS ON DATABASE dev; database_name | privilege_type | identity_id | identity_name | identity_type | admin_option | privilege_scope ---------------+----------------+-------------+---------------+---------------+--------------+----------------- dev | TRUNCATE | 101 | alice | user | f | TABLES dev | DROP | 101 | alice | user | f | TABLES dev | INSERT | 101 | alice | user | f | TABLES dev | ALTER | 101 | alice | user | f | TABLES dev | TEMP | 0 | public | public | f | DATABASE dev | DELETE | 101 | alice | user | f | TABLES dev | SELECT | 101 | alice | user | f | TABLES dev | UPDATE | 101 | alice | user | f | TABLES dev | REFERENCES | 101 | alice | user | f | TABLES (9 rows)
El siguiente comando muestra todas las concesiones en un esquema denominado demo.
SHOW GRANTS ON SCHEMA demo; schema_name | object_name | object_type | privilege_type | identity_id | identity_name | identity_type | admin_option | privilege_scope -------------+-------------+-------------+----------------+-------------+---------------+---------------+--------------+----------------- demo | demo | SCHEMA | ALTER | 101 | alice | user | f | SCHEMA demo | demo | SCHEMA | DROP | 101 | alice | user | f | SCHEMA demo | demo | SCHEMA | USAGE | 101 | alice | user | f | SCHEMA demo | demo | SCHEMA | CREATE | 101 | alice | user | f | SCHEMA (4 rows)
El siguiente comando muestra todas las concesiones para un usuario denominado alice.
SHOW GRANTS FOR alice; database_name | schema_name | object_name | object_type | privilege_type | identity_id | identity_name | identity_type | privilege_scope ---------------+-------------+-------------+-------------+----------------+-------------+---------------+---------------+----------------- dev | | | DATABASE | INSERT | 101 | alice | user | TABLES dev | | | DATABASE | SELECT | 101 | alice | user | TABLES dev | | | DATABASE | UPDATE | 101 | alice | user | TABLES dev | | | DATABASE | DELETE | 101 | alice | user | TABLES dev | | | DATABASE | REFERENCES | 101 | alice | user | TABLES dev | | | DATABASE | DROP | 101 | alice | user | TABLES dev | | | DATABASE | TRUNCATE | 101 | alice | user | TABLES dev | | | DATABASE | ALTER | 101 | alice | user | TABLES dev | public | t1 | TABLE | INSERT | 101 | alice | user | TABLE dev | public | t1 | TABLE | SELECT | 101 | alice | user | TABLE dev | public | t1 | TABLE | UPDATE | 101 | alice | user | TABLE dev | public | t1 | TABLE | DELETE | 101 | alice | user | TABLE dev | public | t1 | TABLE | REFERENCES | 101 | alice | user | TABLE dev | public | t1 | TABLE | DROP | 101 | alice | user | TABLE dev | public | t1 | TABLE | TRUNCATE | 101 | alice | user | TABLE dev | public | t1 | TABLE | ALTER | 101 | alice | user | TABLE dev | demo | | SCHEMA | USAGE | 101 | alice | user | SCHEMA dev | demo | | SCHEMA | CREATE | 101 | alice | user | SCHEMA dev | demo | | SCHEMA | DROP | 101 | alice | user | SCHEMA dev | demo | | SCHEMA | ALTER | 101 | alice | user | SCHEMA (20 rows)
El siguiente comando muestra todas las concesiones en una tabla denominada t3 para un usuario denominado alice. Tenga en cuenta que puede utilizar una notación de dos o tres partes para especificar el nombre de la tabla.
SHOW GRANTS ON TABLE demo_db.demo_schema.t3 FOR alice; schema_name | object_name | object_type | privilege_type | identity_id | identity_name | identity_type | admin_option | privilege_scope -------------+-------------+-------------+----------------+-------------+---------------+---------------+--------------+----------------- demo_schema | t3 | TABLE | ALTER | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | TRUNCATE | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | DROP | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | SELECT | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | INSERT | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | UPDATE | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | DELETE | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | REFERENCES | 100 | alice | user | f | TABLE (8 rows) SHOW GRANTS ON TABLE demo_schema.t3 FOR alice; schema_name | object_name | object_type | privilege_type | identity_id | identity_name | identity_type | admin_option | privilege_scope -------------+-------------+-------------+----------------+-------------+---------------+---------------+--------------+----------------- demo_schema | t3 | TABLE | ALTER | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | TRUNCATE | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | DROP | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | SELECT | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | INSERT | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | UPDATE | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | DELETE | 100 | alice | user | f | TABLE demo_schema | t3 | TABLE | REFERENCES | 100 | alice | user | f | TABLE (8 rows)
El siguiente ejemplo muestra todas las concesiones en una tabla denominada t4. Tenga en cuenta las diferentes formas en que puede especificar el nombre de la tabla.
SHOW GRANTS ON t4; schema_name | object_name | object_type | privilege_type | identity_id | identity_name | identity_type | admin_option | privilege_scope -------------+-------------+-------------+----------------+-------------+---------------+---------------+--------------+----------------- public | t4 | TABLE | ALTER | 100 | alice | user | f | TABLE public | t4 | TABLE | TRUNCATE | 100 | alice | user | f | TABLE public | t4 | TABLE | DROP | 100 | alice | user | f | TABLE public | t4 | TABLE | SELECT | 100 | alice | user | f | TABLE public | t4 | TABLE | INSERT | 100 | alice | user | f | TABLE public | t4 | TABLE | UPDATE | 100 | alice | user | f | TABLE public | t4 | TABLE | DELETE | 100 | alice | user | f | TABLE public | t4 | TABLE | REFERENCES | 100 | alice | user | f | TABLE (8 rows) SHOW GRANTS ON TABLE public.t4; schema_name | object_name | object_type | privilege_type | identity_id | identity_name | identity_type | admin_option | privilege_scope -------------+-------------+-------------+----------------+-------------+---------------+---------------+--------------+----------------- public | t4 | TABLE | ALTER | 100 | alice | user | f | TABLE public | t4 | TABLE | TRUNCATE | 100 | alice | user | f | TABLE public | t4 | TABLE | DROP | 100 | alice | user | f | TABLE public | t4 | TABLE | SELECT | 100 | alice | user | f | TABLE public | t4 | TABLE | INSERT | 100 | alice | user | f | TABLE public | t4 | TABLE | UPDATE | 100 | alice | user | f | TABLE public | t4 | TABLE | DELETE | 100 | alice | user | f | TABLE public | t4 | TABLE | REFERENCES | 100 | alice | user | f | TABLE (8 rows)
