STL_USERLOG - Amazon Redshift


Records details for the following changes to a database user:

  • Create user

  • Drop user

  • Alter user (rename)

  • Alter user (alter properties)

This view is visible only to superusers. For more information, see Visibility of data in system tables and views.

Table columns

Column name Data type Description
userid integer ID of the user affected by the change.
username character(50) User name of the user affected by the change.
oldusername character(50) For a rename action, the original user name. For any other action, this field is empty.
action character(10) Action that occurred. Valid values:
  • Alter

  • Create

  • Drop

  • Rename

usecreatedb integer If true (1), indicates that the user has create database privileges.
usesuper integer If true (1), indicates that the user is a superuser.
usecatupd integer If true (1), indicates that the user can update system catalogs.
valuntil timestamp Password expiration date.
pid integer Process ID.
xid bigint Transaction ID.
recordtime timestamp Time in UTC that the query started.

Sample queries

The following example performs four user actions, then queries the STL_USERLOG view.

create user userlog1 password 'Userlog1'; alter user userlog1 createdb createuser; alter user userlog1 rename to userlog2; drop user userlog2; select userid, username, oldusername, action, usecreatedb, usesuper from stl_userlog order by recordtime desc;
userid | username | oldusername | action | usecreatedb | usesuper --------+-----------+-------------+---------+-------------+---------- 108 | userlog2 | | drop | 1 | 1 108 | userlog2 | userlog1 | rename | 1 | 1 108 | userlog1 | | alter | 1 | 1 108 | userlog1 | | create | 0 | 0 (4 rows)