Prácticas recomendadas para la gestión de usuarios

Administrador del sistema

El usuario dbadmin creado al iniciar un clúster de DWS es un administrador del sistema. Tiene el permiso más alto del sistema y puede realizar todas las operaciones, incluidas las operaciones en tablespaces, tablas, índices, esquemas, funciones y vistas personalizadas, así como consultar catálogos y vistas del sistema.

Para crear un administrador de bases de datos, conéctese a la base de datos como administrador y ejecute la sentencia CREATE USER o ALTER USER con SYSADMIN especificado.

Ejemplos:

Crear el usuario Jim como administrador del sistema.

1

CREATE USER Jim WITH SYSADMIN password '{Password}';

Cambiar el usuario Tom a un administrador del sistema. ALTER USER solo se puede utilizar para usuarios existentes.

1

ALTER USER Tom SYSADMIN;

Usuario común

Puede ejecutar la sentencia SQL CREATE USER para crear un usuario común. Un usuario común no puede crear, modificar, suprimir ni asignar tablespaces y necesita que se le asigne el permiso para acceder a los tablespaces. Un usuario común tiene todos los permisos para sus propias tablas, esquemas, funciones y vistas personalizadas, crea índices en sus propias tablas y consulta solo algunos catálogos y vistas del sistema.

El clúster de bases de datos tiene una o más bases de datos con nombre. Los usuarios se comparten dentro de todo el clúster, pero sus datos no se comparten.

Las operaciones comunes de los usuarios son las siguientes. Reemplace password por la contraseña real.

1. Creación de un usuario

   1	CREATE USER Tom PASSWORD '{Password}';

2. Modificación de contraseña de un usuario
   Modifique la contraseña de inicio de sesión del usuario Tom de password a newpassword.

   1	ALTER USER Tom IDENTIFIED BY 'newpassword' REPLACE '{Password}';

3. Asignación de permisos a un usuario
   • Agregue CREATEDB cuando cree un usuario que tenga el permiso para crear una base de datos.

   1	CREATE USER Tom CREATEDB PASSWORD '{Password}';

   • Agregue el permiso CREATEROLE para un usuario.

   1	ALTER USER Tom CREATEROLE;

4. Revocación de permisos de usuario

   1	REVOKE ALL PRIVILEGES FROM Tom;

5. Bloqueo o desbloqueo de un usuario
   • Bloquear el usuario Tom.

   1	ALTER USER Tom ACCOUNT LOCK;

   • Desbloquear el usuario Tom.

   1	ALTER USER Tom ACCOUNT UNLOCK;

6. Eliminación de un usuario

   1	DROP USER Tom CASCADE;

Consulta de información de usuario

Las vistas del sistema relacionadas con usuarios, roles y permisos incluyen ALL_USERS, PG_USER y PG_ROLES, y los catálogos del sistema incluyen PG_AUTHID y PG_AUTH_MEMBERS.

• ALL_USERS muestra todos los usuarios de la base de datos, pero no muestra los detalles de ellos.
• PG_USER muestra la información del usuario, incluidos los ID de usuario, el permiso para crear bases de datos y los grupos de recursos.
• PG_ROLES muestra información sobre los roles de la base de datos.
• PG_AUTHID registra información sobre los identificadores de autenticación de la base de datos (roles), incluidos los permisos de roles para iniciar sesión o crear bases de datos.
• PG_AUTH_MEMBERS almacena información de los roles contenidos en un grupo de roles.

1. Puede ejecutar PG_USER para consultar a todos los usuarios de la base de datos. También se pueden consultar el ID de usuario (USESYSID) y los permisos.

   1 2 3 4 5 6 7 8 9 10 11 12

   SELECT * FROM pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valbegin | valuntil | respool | parent | spacelimit | useconfig | nodegroup | tempspacelimit | spillspacelim it ---------+----------+-------------+----------+-----------+---------+----------+----------+----------+--------------+--------+------------+-----------+-----------+----------------+-------------- --- Ruby | 10 | t | t | t | t | ******** | | | default_pool | 0 | | | | | kim | 21661 | f | f | f | f | ******** | | | default_pool | 0 | | | | | u3 | 22662 | f | f | f | f | ******** | | | default_pool | 0 | | | | | u1 | 22666 | f | f | f | f | ******** | | | default_pool | 0 | | | | | dbadmin | 16396 | f | f | f | f | ******** | | | default_pool | 0 | | | | | u5 | 58421 | f | f | f | f | ******** | | | default_pool | 0 | | | | | (6 rows)

2. ALL_USERS muestra todos los usuarios de la base de datos, pero no muestra los detalles de ellos.

   1 2 3 4 5 6 7 8 9 10 11 12

   SELECT * FROM all_users; username | user_id ----------+--------- Ruby | 10 manager | 21649 kim | 21661 u3 | 22662 u1 | 22666 u2 | 22802 dbadmin | 16396 u5 | 58421 (8 rows)

3. PG_ROLES almacena información sobre los roles que han accedido a la base de datos.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

   SELECT * FROM pg_roles; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | rolpassword | rolvalidbegin | rolv aliduntil | rolrespool | rolparentid | roltabspace | rolconfig | oid | roluseft | rolkind | nodegroup | roltempspace | rolspillspace ---------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+-------------+---------------+----- ----------+--------------+-------------+-------------+-----------+-------+----------+---------+-----------+--------------+--------------- Ruby | t | t | t | t | t | t | t | t | t | -1 | ******** | | | default_pool | 0 | | | 10 | t | n | | | manager | f | t | f | f | f | f | f | f | f | -1 | ******** | | | default_pool | 0 | | | 21649 | f | n | | | kim | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool | 0 | | | 21661 | f | n | | | u3 | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool | 0 | | | 22662 | f | n | | | u1 | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool | 0 | | | 22666 | f | n | | | u2 | f | t | f | f | f | f | f | f | f | -1 | ******** | | | default_pool | 0 | | | 22802 | f | n | | | dbadmin | f | t | f | f | f | t | f | f | t | -1 | ******** | | | default_pool | 0 | | | 16396 | f | n | | | u5 | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool | 0 | | | 58421 | f | n | | | (8 rows)

4. Para ver las propiedades del usuario, consulte el catálogo del sistema PG_AUTHID, que almacena información sobre los identificadores de autorización de bases de datos (roles). Cada clúster, no cada base de datos, tiene solo un catálogo del sistema PG_AUTHID. Solo los usuarios con permisos de administrador del sistema pueden acceder al catálogo.

   1 2 3 4 5 6 7

   SELECT * FROM pg_authid; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | rolpassword | rolvalidbegin | rolvaliduntil | rolrespool | roluseft | rolparentid | roltabspace | rolkind | rolnodegroup | roltempspace | rolspillspace | rolexcpdata | rolauthinfo ----------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+---------------+--------------+----------+-------------+-------------+---------+--------------+--------------+---------------+-------------+------------- Ruby | t | t | t | t | t | t | t | t | t | -1 | sha256366f1e665be208e6015bc3c5795d13e4dc297a148dca6c60346018c80e5c04c9ba170384ce44609b31baa741f09a3ea5bedc7dadb906286ca994067c3fbf672dc08c981929e326ca08c005d8df942994e146ed3302af47000b36e9852b50e39dmd585de11aafebd90ec620b201fc36f07a5ecdficefade3a1456ec0aca9a0ee01e3bf2971d1dbafd604e596149e2e2928be4060dec2bd8688776588b4cd8c64fd38f1b0beab1603129fa396556ba8aa4c7d6e137a04623 | | | default_pool | t | 0 | | n | 0 | | | | sysadmin | f | t | f | f | f | t | f | f | t | -1 | sha256ecaa7f0ca4436143af43074f16cdd825783ad1a5d659fd94f5e2fa5124e7da44045ecf40bda1a97975fcf5920dca0c8be375be5c71b51cb1eeeba0851fb3648cfa49f55989f83fd9baf1a9d5853ce19125f4fc29a7c709c095ed02d00638410dmd556d6e2dcc41594dc7ad8ee909ef81637ecdficefadefd7d9704ee06affef9581cd6a50a546607f88891198e96a5e84e7e83dccf56c5cd20a500bbc5248e8ea51f0bca70c5a8dcf00953f8b62c7a181368153abce760 | | | default_pool | f | 0 | | n | | | | | Tom | f | t | f | t | f | t | f | f | f | -1 | sha256f43c4f52ac51e297bc4dbdbc751fcf05319c15681dbf5a9c5777d2edce45cb592a948b25457a728e99a3e0608592f33b0a4312eba6124936522304ba298caa2002a04578860fecb0286d7c7baec09365eafd049b2b99f74f21a08864dd7d3f2amd515ee49f0b18ef8e7d0cd27d91ce2fa9decdficefade16bab5f05b6d7c86a19ae6406cc59c437506c3f6187bfdf3eefc7a7c7033afa076361b255cc8b6ccb6e19d4767effaec654b3308cc72cebb891d00a4a10362da | | | default_pool | f | 0 | | n | | | | | (3 rows)

Consulta de recursos de usuario

1. Consulta de la cuota de recursos y el uso de todos los usuarios

   1	SELECT * FROM PG_TOTAL_USER_RESOURCE_INFO;

   Ejemplo de uso de recursos de todos los usuarios:

   1 2 3 4 5 6 7

   username | used_memory | total_memory | used_cpu | total_cpu | used_space | total_space | used_temp_space | total_temp_space | used_spill_space | total_spill_space | read_kbytes | write_kbytes | read_counts | write_counts | read_speed | write_speed ----------+-------------+--------------+----------+-----------+------------+-------------+-----------------+------------------+------------------+-------------------+-------------+--------------+-------------+--------------+------------+------------- perfadm | 0 | 17250 | 0 | 0 | 0 | -1 | 0 | -1 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 usern | 0 | 17250 | 0 | 48 | 0 | -1 | 0 | -1 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 userg | 34 | 15525 | 23.53 | 48 | 0 | -1 | 0 | -1 | 814955731 | -1 | 6111952 | 1145864 | 763994 | 143233 | 42678 | 8001 userg1 | 34 | 13972 | 23.53 | 48 | 0 | -1 | 0 | -1 | 814972419 | -1 | 6111952 | 1145864 | 763994 | 143233 | 42710 | 8007 (4 rows)

2. Consulta de la cuota de recursos y el uso de un usuario específico

   1	SELECT * FROM GS_WLM_USER_RESOURCE_INFO('username');

   Ejemplo de uso de recursos del usuario Tom:

   1 2 3 4 5

   SELECT * FROM GS_WLM_USER_RESOURCE_INFO('Tom'); userid | used_memory | total_memory | used_cpu | total_cpu | used_space | total_space | used_temp_space | total_temp_space | used_spill_space | total_spill_space | read_kbytes | write_kbytes | read_counts | write_counts | read_speed | write_speed -------+-------------+--------------+----------+-----------+------------+-------------+-----------------+------------------+------------------+-------------------+-------------+--------------+-------------+--------------+------------+------------- 16523 | 18 | 2831 | 0 | 19 | 0 | -1 | 0 | -1 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 (1 row)

3. Consulta del uso de E/S de un usuario específico

   1	SELECT * FROM pg_user_iostat('username');

   Ejemplo de uso de E/S del usuario Tom:

   1 2 3 4 5

   SELECT * FROM pg_user_iostat('Tom'); userid | min_curr_iops | max_curr_iops | min_peak_iops | max_peak_iops | io_limits | io_priority -------+---------------+---------------+---------------+---------------+-----------+------------- 16523 | 0 | 0 | 0 | 0 | 0 | None (1 row)