Alter User Examples
Change the characteristics of an existing user:
1. Change an existing user, specifying privileges and group.
ALTER USER bspring WITH
GROUP = engineering,
NOPRIVILEGES;
2. Change an existing user, specifying privileges and group.
ALTER USER barney WITH
GROUP = marketing,
PRIVILEGES = (CREATEDB,TRACE,SECURITY);
3. Specify no expiration date for a predefined user.
ALTER USER bspring WITH NOEXPIRE_DATE
4. Allow a user with maintain_users privilege to change or remove any password.
ALTER USER username
WITH PASSWORD='theirpassword'
5. Grant createdb privilege to user bspring.
ALTER USER bspring ADD PRIVILEGES (CREATEDB)
6. Specify a profile for a particular user
ALTER USER bspring WITH PROFILE = dbop
where "dbop" is an existing profile.
7. Specify that a user has an externally verified password.
ALTER USER bspring WITH EXTERNAL_PASSWORD
8. Specify that user roy has the privilege to change his password.
ALTER USER roy WITH PRIVILEGES = (CHANGE_PASSWORD)
9. Roy changes his own password.
ALTER USER roy WITH
OLDPASSWORD='myoldpassword',
PASSWORD='mypassword';
10. Specify that a user has the privilege to change his password:
ALTER USER cornelius WITH PRIVILEGES = (CHANGE_PASSWORD);