The CREATE AUTHORIZATION statement creates a database user authorization identifier (that is, a user name and optional password), and can be used to assign a default schema for that user. The new user authorization may be used to connect to the database after the transaction that contains the CREATE AUTHORIZATION statement has been successfully completed with the COMMIT command.
To execute the CREATE AUTHORIZATION command, the user authorization must possess DBA privileges.
The user name argument must not match any existing user authorization name in the database. The user name can be up to 128 characters long; it cannot be 'PUBLIC' or 'DBA'. It is strongly recommended that none of the SAND CDBMS SQL keywords be used as an authorization name, as this may cause problems when referencing the authorization in certain SQL statements.
The password argument is optional; if included, it must be preceded by a slash. Passwords can be as long as 128 characters, and are case-sensitive. If a password argument is included in the CREATE AUTHORIZATION statement, the specified password must be supplied whenever the user authorization is used to connect to a database. If the password argument is not included, then the user authorization password has a value of null. This means that no password needs to be supplied in the connect request.
DEFAULT SCHEMA schema name
The DEFAULT SCHEMA clause is optional. If a DEFAULT SCHEMA clause is included in the CREATE AUTHORIZATION statement, schema name will always serve implicitly as the initial schema for the user authorization upon connection to the database. If no DEFAULT SCHEMA clause is specified, the userís initial schema is set to PUBLIC when connecting to the database.
If the schema specified in the DEFAULT SCHEMA clause does not exist in the database, it is automatically created with the new user as owner, implicitly WITH GRANT OPTION. If the default schema already exists, the new user will have to be granted specific privileges to be able to add, drop, and/or manipulate objects in the schema. Refer to the GRANT command for information about grantable privileges.
The following statement creates a user authorization called user1 with the password pass:
CREATE AUTHORIZATION user1 /pass;
The next statement creates the user authorization u1 with a null password, and makes vip the user's default schema:
CREATE AUTHORIZATION u1 DEFAULT SCHEMA vip;