Show TOC

CREATE USER StatementLocate this document in the navigation structure

Creates a user.

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

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

(back to top)

  • user-name name of the user.
  • IDENTIFIED BY 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 SAP IQ Cockpit. However, when logging in to SAP IQ outside of SAP IQ Cockpit (for example, using Interactive SQL), users are then prompted to enter a new password.
  • 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

(back to top)

  • 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

(back to top)

  • SQL–Vendor extension to ISO/ANSI SQL grammar.
  • SAP Database products–Not supported by SAP ASE.
Permissions

(back to top)

Requires the MANAGE ANY USER system privilege.

Related Information