CREATE USER statement (UNIX, Linux)

Use the CREATE USER statement to define internally authenticated users, or to map externally authenticated users to surrogate user properties required for access to HCL OneDB™ resources.

This statement is an extension to the ANSI/ISO standard for the SQL language.

Syntax

Properties

Notes:

¹ Use this path no more than 16 times


Element	Description	Restrictions	Syntax
`directory`	Path name of directory where user files are stored.	Must be 255 bytes or fewer, and must conform to the rules of your operating system. The `directory` must also: Belong to the mapped `user_ID` and `surrog_group_ID`. Have read, write, and execute permissions for the owner.	Quoted String
`password`	Password for internal authentication of `user`.	Must be 6 - 32 bytes.	Quoted String
`surrog_group`	Name of an existing operating system group (surrogate group) that has the permissions to which you want to map `user`. The list of `surrog_group` values must be enclosed in parentheses.	Must be 32 bytes or fewer. You must use one of the surrogates that are specified in the /etc/onedb/allowed.surrogates file.	Owner name
`surrog_group_ID`	Group identifier number (surrogate group) to which you want to map the `user`. The list of `surrog_group_id` value or values that you specify must be enclosed in parentheses.	The `surrog_group_ID` cannot be: A group ID with server administrative privileges (DBSA, DBSSO, AAO, and BARGROUP) Group 0 (root, sometimes referred to as wheel or system) A group ID associated with group bin or group sys You must use one of the surrogates that are specified in the /etc/onedb/allowed.surrogates file.	Literal Number
`surrog_user`	Name of an existing OS user account (surrogate user) on the HCL OneDB host computer that has the permissions to which you want to map `user`.	Must conform to the rules of your operating system. Must be one of the surrogates that are specified in file /etc/onedb/allowed.surrogates file.	Owner name
`user`	Authorization identifier of the specific user that you are mapping to user properties.	Cannot be PUBLIC.	Owner name
`user_ID`	User identifier number to which to map `user`.	Cannot be that of user root or of user informix. Must be one of the surrogates that are specified in file /etc/onedb/allowed.surrogates.	Literal Number

Usage

Only a DBSA can run the CREATE USER statement. With a non-root installation, the user who installs the server is the equivalent of the DBSA, unless the user delegates DBSA privileges to a different user.

The USERMAPPING configuration parameter must be set to a value that enables support for mapped users before users defined by the CREATE USER statement can connect to the database server. A DBSA can issue the CREATE USER statement to map users to properties that correspond to the appropriate level of authorization.

You must also enter values in the SYSUSERMAP table of the sysusers database to map users with the appropriate user properties so that the mapped user statements of SQL to work correctly.

Execution of the CREATE USER statement can be audited with the CRUR audit code.

PASSWORD clause

For a root-privileged server, if an OS user is connecting and the USERMAPPING configuration parameter is unset, OS authentication occurs even though the user exists in the database. When the USERMAPPING parameter is set, internal user authentication takes precedence over OS authentication. Mapped users are authenticated internally or externally. When a user is created without a password, a mapped user is created. When a user is created with a password, an internally authenticated user is created with the properties from the operating system, unless an explicit PROPERTIES clause is also specified in the statement. When the CREATE USER statement contains both the PASSWORD clause and PROPERTIES clause, the user is an internally authenticated user, but has the surrogate properties that are specified in PROPERTIES clause. In this case, the surrogate user or group must also be listed in the /etc/onedb/allowed.surrogates file.

PROPERTIES clause

The PROPERTIES clause can define a new user, and can optionally associate that user with surrogate properties that can include a group and a home directory. CREATE DEFAULT USER is a special case of the CREATE USER statement. The CREATE DEFAULT USER statement defines the properties that are set for the default user. After you define default user properties, you can create new users who have default user properties by omitting the PROPERTIES clause. Mapped users can connect to the database server with the surrogate user properties if they authenticate with pluggable authentication module (PAM), single sign-on (SSO), or internal authentication. Mapped users can connect to the database server with the surrogate user properties if they authenticate with pluggable authentication module (PAM) or internal authentication. Property values are not applicable to non-root installations but must be specified just like a root-privileged server. However, surrogate users and groups in non-root installations are not required in the allowed.surrogates file.

AUTHORIZATION clause

The AUTHORIZATION clause grants a subset of administrative privileges. The USERMAPPING configuration parameter must be set to ADMIN to enable this clause.

Note:

Use of this AUTHORIZATION clause (and of the AUTHORIZATION clause of the ALTER USER or GRANT ACCESS TO PROPERTIES statements) is not recommended. This syntax will not support role separation in a future release.

HOME directory clause

Specifying a directory for the user files with the HOME keyword is optional, but in some cases it is highly desirable. If you do not specify a home directory, an externally authenticated user has the same home directory as the surrogate user account on the HCL OneDB host computer. If the surrogate user identity that does not have a set home directory, then HCL OneDB creates a directory for user files in $ONEDB_HOME/users. In the latter case, the directory name in $ONEDB_HOME/users takes the form uid.ID_number (for example, uid.101).

ACCOUNT LOCK and ACCOUNT UNLOCK keywords

With the ACCOUNT LOCK and ACCOUNT UNLOCK keywords, the DBSA can toggle disabling and enabling the specified user's access to the database server.

Examples

Example 1: Create a mapped user:

The following statement creates a mapped user named joe.

CREATE USER joe;

If the user joe is an OS user, joe has the operating system properties that are associated with his user name.

If the user joe is not an OS user and if default user properties are defined, joe has the surrogate properties of the default user. If default user properties are not defined, an error is returned.

Example 2: Create an internally authenticated user:

The following statement creates an internally authenticated user named joe with a password of joebar:

CREATE USER joe WITH PASSWORD "joebar";

Example 3: Create an internally authenticated user with a locked account:

The following statement creates an internally authenticated user named phil with a locked account:

CREATE USER phil WITH PASSWORD "joebar" ACCOUNT LOCK;

If the user phil is not an OS user and if default user properties are defined, phil has the surrogate properties of the default user. If default user properties are not defined, an error is returned

Example 4: Create an internally authenticated user with specific properties:

The following statement creates an internally authenticated user named mary with a UID, a group, and a home directory:

CREATE USER mary WITH PASSWORD "joebar" PROPERTIES UID 44567 
GROUP(1234) HOME "/home/pd/osuser";

Example 5: Create a mapped user with a surrogate user:

The following statement creates a mapped user named bill with a surrogate user name of foo_os:

CREATE USER bill WITH PROPERTIES user "foo_os";

The user bill has the properties of the operating system user foo_os.

Example 6: Create a default user:

The following statement creates a user, internally named PUBLIC, with the properties of the surrogate user tmp:

CREATE DEFAULT USER WITH PROPERTIES USER "tmp";

Other users created without surrogate properties will have these properties.