SQL Reference Guide

Previous Topic

Next Topic

Users, Schemas and Roles

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 (GRANT, SQL GRANT) privileges for that group.

In This Section

Creating and Modifying Users and Groups

Database Names and Filesystem Names