Redshift ALTER USER

“Anyone familiar with the working of databases and data warehousing knows the concept of database users. Amazon Redshift service also allows the creation of different users in your Redshift cluster, and each user can be granted limited privileges to perform specific tasks. Parameters and permissions associated with these users can be changed and updated using the ALTER USER command. You can manage the user privileges or change your database user’s password using this command.

In this article, we will explain the usage of the Redshift ALTER USER command in detail to change the properties of the existing users in Redshift.”

Permissions to Use ALTER USER

Before we move with the ALTER USER command, we should keep in mind that not every user has the right to use the ALTER USER command in Redshift. Either the superuser or any other user with the ALTER USER privileges can execute this command in Redshift to change the user attributes. Moreover, it can be used if anyone wants to update the password of his own database user. In this section, we will see how to grant superuser privileges to any user in the Redshift cluster.

First, we have to create a new user using the following Redshift CREATE USER function.

CREATE USER <User Name> <‘password’>

Now our user is created, and we can grant this user the superuser rights using the ALTER USER command. Following will be the Redshift SQL query that can be executed in the Redshift to change the permissions of a user.

ALTER USER <User Name> CREATEUSER

Now, this user has the full rights to perform any action in Redshift, including the usage of the ALTER USER command.

To see whether this user has actually got the superuser rights, you can query the system table pg_user, which shows all the users and the assigned privileges of these users.

SELECT * FROM pg_user

From the table, we can see that the demo_user now has superuser permissions in Redshift.

ALTER USER Command

In this section, you are going to learn everything you need to know about using and mastering the Redshift ALTER USER command. This is one of the most critical commands and is often used by system admins as it is mostly used for administrative tasks like granting or taking back access from different users or managing other user settings.

Change Username

Take a case where you have multiple users in your Redshift cluster. A new member joins your database team, and you have created a user for him, but after some time, the team is facing problems due to the username of the new person, which is why the admin wants to change this username. You can change the username to resolve this issue with the help of ALTER USER command.

ALTER USER <Current User Name>
RENAME TO <New User Name>

The above query successfully changed the user name, as you can see in the above screenshot. A thing to remember is that you must always update the user password whenever you change the username; otherwise, you will be facing issues in the login. In the next section, we will see how we can reset the Redshift user password using the ALTER USER command.

Reset User Password

Changing the user password is a common routine but important in the sense that it is responsible for the security and privacy of your data. In order to change the user password in the Redshift cluster, the following query can be executed.

ALTER USER <User Name>
PASSWORD <‘New Password’>

While changing the user password, you can also set the password expiry date or validation time. This is done to make sure that the password is successfully being rotated after some specific time period. The following Redshift query explains how the password is set for a specific time.

ALTER USER <User Name>
PASSWORD <‘New Password’>
VALID UNTIL <‘Date and Time’>

The superuser in the Redshift cluster can change the password for any user without even knowing the present user password, so you must be careful about anyone you are assigning the superuser privileges to. However, a user can change their own password anytime without the superuser privilege.

Set User Permissions

Whenever you add a new user in Redshift, you need to set some permissions for it to grant the limited user privileges. You can grant these limited permissions to the new user by using the ALTER USER command. You can decide whether the new user can add new databases to your system or can further add or remove other users from the system or not. Here you are going to see how it is possible to set the permission for the user to allow the creation of new databases. You can create a new Redshift database by using the following query.

ALTER USER demo_user1 CREATEDB;

Restrict Access to System Tables

Have you ever thought about removing a user’s ability to see all the database system tables? The Redshift ALTER USER command can be used to remove the user privileges to see the database system tables. Suppose to keep with some of your organization’s privacy rules; you do not want all the users to access information about what other users are doing or have done. To do this, you have to restrict the user by applying the following method.

ALTER USER <User Name>
SYSLOG ACCESS < RESTRICTED | UNRESTRICTED >

Now, this user can only view the changes that he made himself in the system tables, and full system tables access has been removed from the user.

Set Connection Limit

Each user in Redshift can be limited to making a specific number of concurrent database connections with redshift. These are the concurrent connections, and you can set them to be unlimited if you want. The following Redshift query can be used to set the limited concurrent connections for a user.

ALTER USER <User Name>
CONNECTION LIMIT < UNLIMITED |Limit Value >

Set Session Timeout

Session timeout defines how long it takes to leave the currently running session if it is in an idle state. Usually, it is set to default if you don’t specify when you create a new user, but it can be altered in Redshift if you want. Suppose you want to set the session timeout time of ten minutes for one specific user; you will execute the following query in Redshift.

ALTER USER <User Name>
SESSION TIMEOUT <Time in Seconds>

The above Redshift query will set the session timeout to 10 minutes, and you can reset it again by executing the same command in Redshift.

ALTER USER <User Name>
RESET SESSION TIMEOUT

Conclusion

There are multiple user settings that can be easily managed using the Redshift built-in ALTER USER function. If you have the right to use this ALTER USER command, it allows you to set user permissions, update and reset the username and passwords, set session timeout for the users, set or reset parameters for different uses, and much more. So it can be deduced from all this discussion that it is a useful command, and you must know it if you are responsible for managing things in Redshift.