Skip to content
≡

Administrator

Developer

Tools

External Tools

4. DB Objects and Privileges#

In this chapter, schema objects and non-schema objects will be classified, and the database objects in each category will be explained.

Database Objects#

Database objects can be classified into schema objects that belong to a speicifc schema and non-schema objects that are not associated with any specific schema. This chapter distinguishes and describes schema objects and non-schema objects.

Schema Objects#

A schema is a logical collection of dar or objects. A user owns a schema and manages it using the SQL statement. The objects included in schemas are called schema objects. Altibase supports the following schema objects:

Tables#

A table is the most basic data storage unit and is a collection of records consisting of columns. Altibase tables are divided into memory tables and disk tables depending on where the data are stored, and Altibase tables are also divided into system tables created and managed by system and user tables by users.

System tables are also called the "data dictionary". For more detailed information about the data dictionary provided with Altibase, as well as data dictionary management issues, please refer to Chapter 3. Data Dictionary in the General Reference.

In the cases of replication target tables and large volume tables have special issues related to their management.

For more detailed information, refer to Administrator's Manual > Chapter 5. Database Objects and Privileges > Table

Partitioned Table#

A partitioned table is called when data of the table are divided based on the partitioning conditions (range, list, and hash) and stored each in a different tablespace. In the case of large tables, it is easy to manage data by using partitioned tables.

For more detailed information, please refer to Administrator's Manual > Chapter 7. Partitioned Objects.

Partitioned Index#

Indexes are categorized as partitioned indexes or non-partitioned indexes based on where or not they are partitioned. Non-partitioned indexes are indexes that have not been partitioned, while partitioned indexes (same as partitioned tables) are indexes that have been divided into several indexes based on some partitioning conditions.

For more detailed information, please refer to Administrator's Manual > Chapter 7. Partitioned Objects.

Temporary Table#

A temporary table can be used to temporarily store data while a session or transaction is being held. Temporary tables can speed up the use of complex queries.

Temporary tables can only be created in volatile tablespaces.

Queue Table#

Altibase supports asynchronous data communication between database and user program using message queuing. Queue tables are manipulated using DMLS and DDL statements like database tables.

For more detailed information on the concepts and functionality of queue tables, please refer to in the Administrator's Manual > Chapter 5. Database Objects and Privileges > Queues.

Constraint#

A constraint is a condition that is imposed to maintain data consistency when inserting or changing data in a table.

You can distinguish between column constraints and table constraints depending on the constraints. Altibase supports the following constraints:

  • NOT NULL / NULL Constraints

  • CHECK Constraints

  • Unique key Constraints

  • Primary key Constraints

  • Foreign key Constraints

  • TIMESTAMP Constraints

For more detailed information, please refer to Administrator's Manual > Chapter 5. Database Objects and Privileges > Constraint.

Index#

Indexes are elements that are created in tables and allow records within tables to be accessed much quickly when DML statements are processed.

For more detailed information, please refer to Administrator's Manual > Chapter 5. Database Objects and Privileges > Indexes.

Views#

A view does not contain actual data, but a logical table created one or more tables, material views, or other views.

For more detailed information, please refer to Administrator's Manual > Chapter 5. Database Objects and Privileges > View

Materialized Views#

A materialized view is a database object that stores query results as data. Data can be stored based on one or more tables, views, or other materialized views.

For more detailed information, please refer to Administrator's Manual > Chapter 5. Database Objects and Privileges > Materialized View.

Sequences#

Altibase provides sequences for generating unique keys.

For more detailed information, please refer to Administrator's Manual > Chapter 5. Database Objects and Privileges > Sequences.

Synonyms#

Synonyms are provided as aliases for tables, sequences, views, stored procedures and stored functions so that they can be used without being accessed directly by the object name.

For more detailed information, refer to Administrator's Manual > Chapter 5. Database Objects and Privileges > Synonyms.

Stored Procedures and Functions#

A stored procedure and function are kinds of database object in which all kinds of operations, such as SQL statements, flow control statements, assignment statements, and error handling routines, are programmatically combined into a single module that is permanently stored in the database, after which all of the operations stored therein can be executed merely by calling its name.

For more detailed information, refer to Administrator's Manual > Chapter 5. "Database Objects and Privileges > Stored Procedures and Functions.

In addition, for more detailed information about the special features of stored procedures as provided with Altibase, as well as how to manage them, please refer to the Stored Procedures Manual.

Type Sets#

A type set is a database object that allows users to collect and manage user-defined types used in stored procedures and stored functions.

More detailed information on this can be found in the Stored Procedures Manual.

Database Triggers#

A trigger is a kind of stored procedure that is called automatically by the system when data in a table are inserted, deleted, or updated, thus allowing a specific set of tasks to be automatically performed. By defining constraints and triggers for tables, the user can maintain data consistency.

For more detailed information, please refer to Administrator's Manual > Chapter 5. Database Objects and Privileges > Triggers*.

Database Link allows database servers that are geographically distributed but connected via a network to be linked together to combine the data thereon and output a single result.

More detailed information on this can be found in the Database Link User's Manual.

External Procedures or Functions#

External procedures or external function objects are database objects that correspond to user-defined C/C++ functions on a one-to-one basis. User-defined functions are executed through external procedure objects or external function objects. Depending on whether or not they return values differentiates external procedures from external functions.

More detailed information on this can be found in the C/C++ External Procedures Manual.

Library#

The dynamic library file containing user-defined C/C++ functions to be used with external procedures must be identifiable by the Altibase server. For this purpose, Altibase provides the library object which is a database object that corresponds to the dynamic library file on a one-to-one basis.

More detailed information on this can be found in the C/C++ External Procedures Manual.

Non-schema Objects#

Objects that do not belong to any particular schema, and are thus managed at the level of the entire database, are called non-schema objects. Altibase provides the following non-schema objects:

Directories#

Stored procedures are able to control files, which allows them to read from and write to text files in the file system managed by the operating system. Thanks to this functionality, the user can perform various kinds of tasks using stored procedures such as leaving messages in files, reporting the results into files or reading data from files for insertion into tables. The directory object is used to manage information about the directories accessed by stored procedures.

For more detailed information on the directory object, please refer to SQL Reference.

For more detailed information on how to handle files using stored procedures, please refer to Stored Procedures Manual

Replication#

Replication is an object that allows the system to automatically transfer data from one local server to a remote server, and replicate them to maintain the same table data among the other servers.

For more detailed information on replication, please refer to the Replication Manual.

Tablespaces#

Tablespaces are the largest logical data storage unit, and the database is managed by dividing into several tablespace units.

Altibase creates a system tablespace automatically when a database is created, and the user can arbitrarily create a user-defined tablespace.

Altibase supports disk tablespaces where database objects reside on disk, memory tablespaces resident in memory, and volatile tablespaces, which also resides in memory but differs from memory tablespace in that logging is not performed.

For more detailed information on tablespace management, refer to Administrator's Manual > Chapter 6. Managing Tablespaces.

Users#

The user account is required for Altibase access and is also the owner of the schema. There are two users: system user and general user. System users are created by the system and are administrators of the entire system. General users require proper authorization to access and manage the database.

For more detailed information, please refer to Administrator's Manual > Chapter 5. Objects and Privileges.

Jobs#

A JOB is an object of an execution schedule to a stored procedure. The stored procedure to be executed, the point in time of execution, the interval after which it is to be executed and etc. can be set when creating the JOB object. For the created JOB to automatically run, the value of the JOB_SCHEDULER_ENABLE property must be set to 1. Job creation, alteration and deletion and management of the JOB scheduler are possible only by the SYS user.

For more detailed information, please refer to Administrator's Manual > Chapter 5. Objects and Privilege > JOB.


Privileges#

Users must have appropriate privileges in order to access database objects and data. This section describes the privileges pertaining to users and objects and how to manage them.

Types of Privileges#

Altibase supports system privileges, object privileges and roles.

System Privilege#

System access privileges are typically granted by a DBA, performing certain tasks on the database, or managing objects in all schemas.

Object Privileges#

The object owner manages object privileges, which are the right to access and manipulate objects.

For a complete list of the privileges supported in Altibase, please refer to Administrator's Manual > Chapter 5. Objects and Privileges, and for more detailed information about statements for granting and revoking privileges, please refer to the SQL Reference.

Roles#

A role is a bundle of privileges. It is easy to use roles when granting multiple privileges to users.

For more detailed information and restrictions on roles, refer to the SQL Reference.

Granting Privileges#

For the SYSTEM_ and SYS users who are in the initial state since the database is created, they have full authority as DBA and can grant authority to general users.

When creating general users by CREATE USER statement, the system automatically grants the users the minimum privileges to access the database, such as the authority to execute CREATE SESSION and CREATE TABLE statement.

Other privileges must be explicitly granted by the DBA.

For more detailed information on authorizing and managing privileges, please refer to the SQL Reference and the Administrator's Manual.

Revoking Privileges#

Privileges granted to users other than the SYSTEM_ and SYS users can be explicitly revoked using the REVOKE statement.

With the CREATE USER statement, privileges automatically granted by the system when creating a user can also be freed if necessary.

However, the privileges of the SYSTEM_ and SYS users cannot be revoked.