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 statement for creating idents has the general form:CREATE IDENT username AS ident-type [USING 'password'] [WITH | WITHOUT SCHEMA];
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
aBccannot both exist in the database because they are identical when case is ignored).
Passwords are composed of case-significant characters and must be entered exactly as they are defined.
Passwords are optional for
USERident with an
OS_USERlogin may connect to Mimer SQL without providing a password. Passwords are required for
PROGRAMidents. Passwords are not used for
PROGRAMident 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 SCHEMAis specified in the
CREATE IDENTstatement. For idents who are not supposed to create database objects, it's good practice to specify
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
SCHEMAprivilege can create additional schemas by using the
CREATE SCHEMAstatement. The objects belonging to the schema can be defined in the
CREATE SCHEMAstatement and created at the same time as the schema, refer to the Mimer SQL Reference Manual, CREATE SCHEMA for details.
Creating Idents and Schemas, Examples
Create a user ident MIMER_ADM with the password 'adm':
Note: Schema MIMER_ADM will also be automatically created.CREATE IDENT mimer_adm AS USER USING 'adm';
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:CREATE IDENT mimer_admin_group AS GROUP;
Create a schema called MIMER_STORE:CREATE SCHEMA mimer_store;
Create table CURRENCIES in the schema MIMER_STORE:CREATE TABLE mimer_store.currencies ( code CHARACTER(3) PRIMARY KEY, ...
Create schema called MIMER_STORE_NEW that contains sequence Z:CREATE SCHEMA mimer_store_new CREATE SEQUENCE z;
Mimer Information Technology AB
Phone: +46 18 780 92 00