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.

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

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 on the database. A normal user can change only his or her 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 a Pervasive 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 DEMODATA is “DeptMgr.”

Password Characteristics

•

See Identifier Restrictions by Identifier Type in Advanced Operations Guide for the maximum length of a password and the characters allowed.

•

Passwords are case sensitive. If the password begins with a non-alphabetic 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 an SQL statement, “password” must be enclosed by double quotes 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.

The following example shows the Master user enabling security on the database with a password of “bluesky.” The Master user then grants logon authority to user “user45” with a password of “tmppword” and grants user45 SELECT permissions to table person. The Master user then changes the security password for Master to “reddawn” and changes user45’s password to “newuser.”

SET SECURITY = bluesky

--this enables security on the database

GRANT LOGIN TO user45:tmppword

GRANT SELECT ON person TO user45

SET PASSWORD = reddawn

--this changes the password for Master, which means for the entire database

SET PASSWORD FOR user45 = newuser

The following example assumes that user45 has logged on to the database with a password of “newuser.” User45 changes her password to “tomato.” User45 then selects all records in the person table.

SET PASSWORD FOR user45 = tomato

--this changes only user45’s password

SELECT * FROM person

--user45 still has SELECT rights on table person