User-level configuration parameters

ALTER USER lets you set user-level configuration parameters on individual users. These settings override database- or session-level settings on the same parameters. For example, the following ALTER USER statement sets DepotOperationsForQuery for users Yvonne and Ahmed to FETCHES, thus overriding the default setting of ALL:

=> SELECT user_name, parameter_name, current_value, default_value FROM user_configuration_parameters
   WHERE user_name IN('Ahmed', 'Yvonne') AND parameter_name = 'DepotOperationsForQuery';
 user_name |     parameter_name      | current_value | default_value
-----------+-------------------------+---------------+---------------
 Ahmed     | DepotOperationsForQuery | ALL           | ALL
 Yvonne    | DepotOperationsForQuery | ALL           | ALL
(2 rows)

=> ALTER USER Ahmed SET DepotOperationsForQuery='FETCHES';
ALTER USER
=> ALTER USER Yvonne SET DepotOperationsForQuery='FETCHES';
ALTER USER

Identifying user-level parameters

To identify user-level configuration parameters, query the allowed_levels column of system table CONFIGURATION_PARAMETERS. For example, the following query identifies user-level parameters that affect depot usage:

n=> SELECT parameter_name, allowed_levels, default_value, current_level, current_value
    FROM configuration_parameters WHERE allowed_levels ilike '%USER%' AND parameter_name ilike '%depot%';
     parameter_name      |     allowed_levels      | default_value | current_level | current_value
-------------------------+-------------------------+---------------+---------------+---------------
 UseDepotForReads        | SESSION, USER, DATABASE | 1             | DEFAULT       | 1
 DepotOperationsForQuery | SESSION, USER, DATABASE | ALL           | DEFAULT       | ALL
 UseDepotForWrites       | SESSION, USER, DATABASE | 1             | DEFAULT       | 1
(3 rows)

Viewing user parameter settings

You can obtain user settings in two ways:

• Query system table USER_CONFIGURATION_PARAMETERS:

  => SELECT * FROM user_configuration_parameters;
   user_name |      parameter_name       | current_value | default_value
  -----------+---------------------------+---------------+---------------
   Ahmed     | DepotOperationsForQuery   | FETCHES       | ALL
   Yvonne    | DepotOperationsForQuery   | FETCHES       | ALL
   Yvonne    | LoadSourceStatisticsLimit | 512           | 256
  (3 rows)
  

• Use SHOW USER:

  => SHOW USER Yvonne PARAMETER ALL;
    user  |         parameter         | setting
  --------+---------------------------+---------
   Yvonne | DepotOperationsForQuery   | FETCHES
   Yvonne | LoadSourceStatisticsLimit | 512
  (2 rows)
  
  => SHOW USER ALL PARAMETER ALL;
    user  |         parameter         | setting
  --------+---------------------------+---------
   Yvonne | DepotOperationsForQuery   | FETCHES
   Yvonne | LoadSourceStatisticsLimit | 512
   Ahmed  | DepotOperationsForQuery   | FETCHES
  (3 rows)