Users are administrator defined accounts with specific access control privileges. Each account is protected with a user defined password and subect to all other database server user accounting, such as licensed limits, password controls, and password timeouts.
A schema is a qualifier for database object names (database objects include tables, indexes, views, synonyms, procedures, and triggers). Schemas assist in organizing database objects Into related collections. FairCom DB SQL defines schema as the user name connected to the database at that time. Out of the box, the provided ADMIN account becomes the default schema until other users are created and connected. A fully qualified database object is referenced as "database"."schema"."object". Schema names follow the same identifier conventions as previously described (FairCom DB SQL Identifiers, /doc/sqlref/33346.htm).
When you connect to a database with a particular user name, that name becomes the default qualifier for database object names. This means you do not have to qualify references to tables, for instance, that were created under the same user name. However, you must qualify references to all other tables with the user name of the user who created them.
For example, when connected to a FairCom DB SQL database with SQL Explorer, you'll find objects organized by user name.
The current connected user name is the default schema for that session and requires no further qualifier to access objects under that user/schema. For example, if the accounting user accesses a "stock" table created and owned by the hr inventory user, they must use a full qualifier such as "inventory"."stock" when referencing that table. For convenience, the SET SCHEMA statement will set a current default for a connected session:
SET SCHEMA 'warehouse'
User accounts can be organized into groups with similar privileges for ease of administration. A specific set of database access control privileges can be assigned to a group, and users added to that group then inherit those permissions. This organization allows for advanced and easy account management for many users. Groups naturally provide database roles. And, indeed, with FairCom DB, SQL database privileges can be assigned to a group name, and connected users inherit all database GRANTed privileges for that group.
Creating and Modifying Users and Groups
Each FairCom DB SQL schema is based on the connected user. New users are created with standard FairCom DB administration utilities. There are three utilities provided for this task.
Security Administrator
Security Administrator is a visual-based utility for creating and managing users, groups and passwords. You'll find this in the /tools/gui folder of your FairCom DB installation.
Command Line Administrator Utility
The command line administrator utility, ctadmn, is an interactive utility with prompted options for creating and managing user, groups and passwords. You'll find this in the tools folder of your FairCom DB installation.
Command Line Security Administrator
The command line administrator utility, sa_admin, is a scriptable utility with prompted options for creating and managing user, groups and passwords. You'll find this in the tools folder of your FairCom DB installation.
Database Names and Filesystem Names
User-based schema names allow different users to create database objects of the same name. For example, the "accounting" user and the "inventory" user can both own an object named "customer". The applied user name as schema keeps these objects related under the user name. FairCom DB stores all persisted objects such as tables and indexes on disk as individual physical files. This creates a challenge with database objects of the same name. To resolve that issue, the schema name of the object owner is prepended along with an underbar character "_" to the object file name. For example, for the "stock" table owned by the "warehouse" user, the physical filename on disk is "warehouse_stock". If user accounting also created a "stock" table, then the resulting table will be physically named "inventory_stock".
See Also
SQL_OPTION OWNER_FILE_NAMES