Creating Idents and Schemas
Idents are authorized users of the system or groups of users defined for easier ident management, see Idents.
A schema defines a local environment within which private database objects can be created. The ident creating the schema has the right to create objects in it and to drop objects from it.
The case of letters is insignificant for an ident name and it must be composed of a unique sequence of case-less characters (e.g. the idents ABC and aBc cannot both exist in the database because they are identical when case is ignored).
The case of the characters in an ident name can be made significant by enclosing the string in double quotes (" ").
Passwords are composed of case-significant characters and must be entered exactly as they are defined.
The statement for creating idents has the general form:CREATE IDENT username AS ident-type [USING 'password'];
Passwords are required for user and program idents but are not used for group idents. Passwords are optional for OS_USER idents: an OS_USER with a password may connect to Mimer SQL in the same way as any other user ident.
When a USER, OS_USER or PROGRAM ident is created, a schema with the same name can also be created automatically and the created ident becomes the creator of the schema. This happens by default unless WITHOUT SCHEMA is specified in the CREATE IDENT statement.
All private database objects created by an ident must belong to a schema which, by default, is the schema with the same name as the ident. When any private database object is created, its name can be specified in the fully qualified form that explicitly identifies which schema the object is to belong to. An ident may create objects in schemas 'owned' by it (i.e. the schema created automatically when the ident was created and any schemas explicitly created by the ident).
An ident with IDENT or SCHEMA privilege can create additional schemas by using the CREATE SCHEMA statement. The objects belonging to the schema can be defined in the CREATE SCHEMA statement and created at the same time as the schema, refer to the Mimer SQL Reference Manual for details.
Creating Idents and Schemas, Examples
Create a user ident HOTELADM with the password 'Hoteladm':
Note: Schema HOTELADM will also be automatically created.CREATE IDENT HOTELADM AS USER USING 'Hoteladm';
Create a program ident AUDIT with the password 'economy' without creating a schema:CREATE IDENT AUDIT AS PROGRAM USING 'economy' WITHOUT SCHEMA;
Create a group ident for the group ECONOMY_DEPT:CREATE IDENT ECONOMY_DEPT AS GROUP;
Create a schema called NEW_SCHEMA:CREATE SCHEMA NEW_SCHEMA;
Create table Y in the schema called NEW_SCHEMA:CREATE TABLE NEW_SCHEMA.Y (A INTEGER);
Create schema called SCHEMA_S which contains sequence Z:CREATE SCHEMA SCHEMA_S CREATE UNIQUE SEQUENCE Z;
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40