CREATE USER Statement

Syntax

CREATE USER  user-name  [ IDENTIFIED BY password ]
[ LOGIN POLICY policy-name ]
[ FORCE PASSWORD CHANGE { ON | OFF } ]

Parameters

• user-name – name of the user.
• IDENTIFIED BY – provides the password for the user.
• policy-name – name of the login policy to assign the user. No change is made if you do not specify a login policy.
• FORCE PASSWORD CHANGE – controls whether the user must specify a new password upon logging in. This setting overrides the PASSWORD_EXPIRY_ON_NEXT_LOGIN option setting in the user's login policy.
  Note: This functionality is not currently implemented when logging in to Sybase Control Center. A user will not be prompted to change their password. He or she will be prompted, however, when logging in to SAP Sybase IQ outside of Sybase Control Center (for example, using Interactive SQL).
• password – You do not have to specify a password for the user. A user without a password cannot connect to the database. This is useful if you are creating a role and do not want anyone to connect to the database using the role user ID. A user ID must be a valid identifier.
  User IDs and passwords cannot:

  • Begin with white space, single quotes, or double quotes

  • End with white space

  • Contain semicolons

  A password can be either a valid identifier, or a string (maximum 255 characters) placed in single quotes. Passwords are case-sensitive. The password should be composed of 7-bit ASCII characters, as other characters may not work correctly if the database server cannot convert them from the client's character set to UTF-8.

  You can use the VERIFY_PASSWORD_FUNCTION option to specify a function to implement password rules (for example, passwords must include at least one digit). If you do use a password verification function, you cannot specify more than one user ID and password in the GRANT CONNECT statement.

  The encryption algorithm used for hashing the user passwords is FIPS-certified encryption support:

  • The DLL is called dbfips10.dll
  • The HASH function accepts the algorithms: SHA1_FIPS SHA256_FIPS
  • If the -fips server option is specified and an algorithm that is not FIPS-certified is given to the HASH function, the database server uses SHA1_FIPS instead of SHA1, SHA256_FIPS instead of SHA256, and returns an error if MD5 is used (MD5 is not a FIPS-certified algorithm).
  • If the -fips option is specified, the database server uses SHA256_FIPS for password hashing.

Examples

• Example 1 – creates a user named SQLTester with the password welcome. The SQLTester user is assigned to the Test1 login policy and the password expires on the next login:

  CREATE USER SQLTester IDENTIFIED BY welcome
  LOGIN POLICY Test1
  FORCE PASSWORD CHANGE ON;

Standards

Permissions

Requires the MANAGE ANY USER system privilege.