Options
Set All User’s Default Roles to All of their Granted Roles
Jim_Knicely
- Select Field - Administrator
By default, no roles (other than the default PUBLIC Role) are enabled at the start of a user session. You have to assign one or more of a user’s granted roles as default roles so that they are enabled when a user logs on. This is done with the ALTER USER … DEFAULT ROLE command.
I’d like to assign all of my database user’s granted roles to also be their default roles. Instead of manually running an ALTER USER … DEFAULT ROLE command for each of the 357 users in my Vertica DB, I can have Vertica generate and run the commands for me!
Example:
dbadmin=> SELECT user_name, all_roles, default_roles dbadmin-> FROM users dbadmin-> WHERE user_name ILIKE 'role_user%' dbadmin-> ORDER BY 1; user_name | all_roles | default_roles ------------+-----------+--------------- role_user1 | r1 | role_user2 | r1, r2 | role_user3 | r1, r3 | role_user4 | r2 | role_user5 | r2, r3 | (5 rows) dbadmin=> \! /opt/vertica/bin/vsql -Atc "SELECT 'ALTER USER ' || user_name || ' DEFAULT ROLE ' || REPLACE(all_roles, '*', '') || ';' FROM users WHERE NOT is_super_user AND all_roles <> '';" | /opt/vertica/bin/vsql –q dbadmin=> SELECT user_name, all_roles, default_roles dbadmin-> FROM users dbadmin-> WHERE user_name ILIKE 'role_user%' dbadmin-> ORDER BY 1; user_name | all_roles | default_roles ------------+-----------+--------------- role_user1 | r1 | r1 role_user2 | r1, r2 | r1, r2 role_user3 | r1, r3 | r1, r3 role_user4 | r2 | r2 role_user5 | r2, r3 | r2, r3 (5 rows)
Have fun!
1
Comments
I also write some backup SQL ：
---backup resource_pools SELECT 'CREATE RESOURCE POOL ' || name || CASE WHEN memorysize IS NULL THEN ' ' ELSE ' MEMORYSIZE ' || '''' || memorysize || '''' END || CASE WHEN maxmemorysize = '' THEN ' ' ELSE ' MAXMEMORYSIZE ' || '''' || maxmemorysize || '''' END || CASE WHEN executionparallelism = 'AUTO' THEN ' ' ELSE ' EXECUTIONPARALLELISM ' || '''' || executionparallelism || '''' END || CASE WHEN NULLIFZERO(priority) IS NULL THEN ' ' ELSE ' PRIORITY ' || '''' || priority || '''' END || CASE WHEN runtimepriority IS NULL THEN ' ' ELSE ' RUNTIMEPRIORITY ' || runtimepriority END || CASE WHEN runtimeprioritythreshold IS NULL THEN ' ' ELSE ' RUNTIMEPRIORITYTHRESHOLD ' || runtimeprioritythreshold END || CASE WHEN queuetimeout IS NULL THEN ' ' ELSE ' QUEUETIMEOUT ' || queuetimeout END || CASE WHEN maxconcurrency IS NULL THEN ' ' ELSE ' MAXCONCURRENCY ' || maxconcurrency END || CASE WHEN runtimecap IS NULL THEN ' ' ELSE ' RUNTIMECAP ' || '''' || runtimecap || '''' END || ' ; ' FROM v_catalog.resource_pools WHERE NOT is_internal ORDER BY name; ---backup roles -- Roles ------------------------------------------------------------ SELECT '-- Create Roles'; SELECT 'CREATE ROLE ' || name || ' ;' AS TXT_CR FROM v_catalog.roles WHERE name NOT IN ('public','dbadmin','pseudosuperuser','dbduser') ORDER BY 1; ------------------------------------------------------------ -- Add users to Roles ------------------------------------------------------------ SELECT '-- Add users to roles'; SELECT 'GRANT ' || all_roles || ' TO ' || user_name || ';' FROM v_catalog.users WHERE user_name NOT IN ('dbadmin') ORDER BY 1; --backup schema SELECT '-- Create Schema'; SELECT 'CREATE SCHEMA ' || schema_name || ';' FROM schemata WHERE schema_name NOT IN ('v_internal','v_catalog','v_monitor','TxtIndex') ORDER BY 1; --backup users SELECT '-- Create Users'; SELECT 'CREATE USER ' || user_name || ' RESOURCE POOL ' || resource_pool || ' ;' FROM v_catalog.users WHERE user_name NOT IN ('dbadmin') ORDER BY 1; --backup grants select 'grant '|| privileges_description || ' on '|| object_name || ' to '|| grantee||';' from grants where grantor<>grantee order by object_name;