SET PASSWORD
The SET PASSWORD statement provides the following functionality for a secured database:
•The Master user can change the password for the Master user or for another user.
•A normal user (non-Master user) can change his or her logon password to the database.
Syntax
SET PASSWORD [ FOR 'user_name'] = password
user-name ::= name of user logged on the database or authorized to log on the database
password ::= password string
Remarks
SET PASSWORD requires that the database have relational security enabled and may be issued at any time. (In contrast,
SET SECURITY can be issued only when the session for the Master user is the only current database connection. See
SET SECURITY.)
SET PASSWORD may be issued by the Master user or by a normal, non-Master user. The Master user can change the password for any user authorized to log in to the database. Normal users can change only their own password. The changed password takes effect the next time the user logs on the database.
User Issuing SET PASSWORD statement | with FOR clause | without FOR clause |
Master | Master can specify a user name of Master or of any user authorized to log on the database.1 Password changed for user name. | Password changed for entire database (that is, changed for the Master user, which affects the entire database). |
Normal | Normal user can specify his or her user name. The user must be logged on the database. Password changed only for that user. | Password changed only for the user issuing the SET PASSWORD statement. The user must be logged on the database. |
1User-name refers to a user who can log on to a PSQL database, which may differ from the name assigned to a user at the operating system level. For example, assume that Yogine is a user who can log on to the operating system. Security is enabled on database Demodata and Yogine is added to Demodata as a user named DeptMgr. The user name required for this person to log on to Demodata is DeptMgr. |
Password Characteristics
•Passwords are case sensitive. If the password begins with a nonalphabetic character, the password must be enclosed in single quotes.
•The space character may be used in a password provided it is not the first character. If a password contains a space character, the password must be enclosed by single quotes. As a general rule, avoid using the space character in a password.
•“Password” is not a reserved word. It may be used as a name for a table or column. However, if used for a table or column name in a SQL statement, “password” must be enclosed by double quotation marks because it is a keyword.
•If you want to use the literal “null” as a password, you must enclose the word with single quotes (‘null’). The quoted string prevents confusion with the statement SET SECURITY = NULL, which disables security on the database.
Examples
The following example shows the Master user enabling security on the database with the password bluesky. The Master user then grants login privilege to user user45 with the password tmppword and grants that user SELECT permission to the table person. The Master user then changes the Master password to reddawn, which changes it for the entire database. Finally, it changes the user45 password to newuser.
SET SECURITY = bluesky
GRANT LOGIN TO user45:tmppword
GRANT SELECT ON person TO user45
SET PASSWORD = reddawn
SET PASSWORD FOR user45 = newuser
The following example assumes that user45 has logged on to the database with a password newuser. User45 changes its own password to tomato. User45 then selects all records in the table person.
SET PASSWORD FOR user45 = tomato
SELECT * FROM person
See Also