The ALTER AUTHORIZATION command is used to change the attributes of a database user authorization, by specifying a new password, a new default schema, or a new partitioned table for a particular user. In SAND CDBMS SQL, each ALTER AUTHORIZATION statement can specify only one alteration action; to specify more than one action, use multiple ALTER AUTHORIZATION statements.
Any user authorization can execute the ALTER AUTHORIZATION command to change their own password, default schema, or default partitioned table.
A user authorization with DBA privileges can also execute the ALTER AUTHORIZATION command to change another user's password, default schema, or partitioned table.
The specified user name must be an existing user authorization in the database.
The new user password must begin with a letter, may be as long as 128 characters, and is case-sensitive. The specified password must be supplied whenever the user authorization is used to connect to a database.
The SET DEFAULT SCHEMA clause identifies the user's default schema upon connecting to the database. If schema name does not already exist in the database, a new schema with that name will be created, with the authorization specified as the schema owner, implicitly WITH GRANT OPTION. If the default schema already exists, no special privileges on the schema or its contents are conferred on the user other than those previously granted.
The SET DEFAULT PARTITION clause specifies an existing partitioned table as the user's default. If a user has a default partitioned table set, that user will be automatically placed into MPP mode in the context of the partitioned table when connecting to the database.
Refer to CREATE AUTHORIZATION and SAND CDBMS SQL Database Objects and Language Elements for information about passwords, schema names, and default schemas.
The following command changes user1’s default schema to vip:
ALTER AUTHORIZATION user1 SET DEFAULT SCHEMA vip;
The next command sets user1’s password to “pass123”:
ALTER AUTHORIZATION user1 SET PASSWORD pass123;
The final command sets user1's default partitioned table to part1, which must already exist in schema s1:
ALTER AUTHORIZATION user1 SET DEFAULT PARTITION s1.part1;
Note that separate ALTER AUTHORIZATION commands are required to change user1’s default schema and default partitioned table, and set the user’s password .