SYSCS_UTIL.SYSCS_CREATE_USER

The SYSCS_UTIL.SYSCS_CREATE_USER system procedure adds a new user account to a database.

This procedure creates users for use with NATIVE authentication.

Syntax

SYSCS_UTIL.SYSCS_CREATE_USER(
		IN userName VARCHAR(128),
		IN password VARCHAR(32672)
		)

userName

A user name that is case-sensitive if you place the name string in double quotes. This user name is an authorization identifier.

Case sensitivity is very specific with user names: if you specify the user name in single quotes, e.g. ‘Fred’, the system automatically converts it into all Uppercase.

The user name is only case sensitive if you double-quote it inside of the single quotes. For example, '"Fred"' is a different user name than 'Fred', because 'Fred' is assumed to be case-insensitive.

password

A case-sensitive password.

Results

When you add a new user, a new schema is automatically created with exactly the same name as the user. For example, here’s a sequence of an administrator adding a new user named fred and then verifying that the schema named fred is now active:

splice> CALL SYSCS_UTIL.SYSCS_CREATE_USER('fred', 'fredpassword');
Statement executed.
splice> VALUES(CURRENT SCHEMA);
1
------------------------------------------------------------------
SPLICE

1 row selected
splice> SET SCHEMA fred;
0 rows inserted/updated/deleted
splice> VALUES(CURRENT SCHEMA);
1
------------------------------------------------------------------
FRED

1 row selected

When the new user’s credentials are used to connect to the database, his/her default schema will be that new schema. If you want the new user to have access to data in other schemas, such as the SPLICE schema, an administrator will need to explicitly grant those access privileges.

Execute Privileges

If authentication and SQL authorization are both enabled, only the database owner has execute privileges on this function by default. The database owner can grant access to other users.

JDBC example

Create a user named FRED:

CallableStatement cs = conn.prepareCall
  ("CALL SYSCS_UTIL.SYSCS_CREATE_USER(?, ?)");
  cs.setString(1, "fred");
  cs.setString(2, "fredpassword");
  cs.execute();
  cs.close();

Create a user named FreD:

CallableStatement cs = conn.prepareCall
  ("CALL SYSCS_UTIL.SYSCS_CREATE_USER(?, ?)");
  cs.setString(1, "\"FreD\"");
  cs.setString(2, "fredpassword");
  cs.execute();
  cs.close();

SQL Example

Create a user named FRED:

splice> CALL SYSCS_UTIL.SYSCS_CREATE_USER('fred', 'fredpassword');
Statement executed.

Create a (case sensitive) user named MrBaseball:

CALL SYSCS_UTIL.SYSCS_CREATE_USER('MrBaseball', 'pinchhitter')
Statement executed.

See Also