Database isolation

The applications connected to a database require an isolated view of the database in the DBS environment.

In the DBS environment, the database is accessible only by users associated with the database. SQL/MX objects can be created only on disks associated with the database. The privileges on the database objects can be granted only to the associated database users or privilege groups of the database. DBS provides isolation at various levels with:

  • Metadata isolation

  • User isolation

  • Resource isolation

Further, SQL/MX restricts use of certain statements in DBS environment as they break the isolation rules and database characteristics. The command-line SQL/MX utilities such as import, and mxtool are supported only for the SUPER.SUPER user in DBS environment.

Shared databases

In certain situations, users may need access to other databases. The databases can be shared to other users on the system. The metadata of the shared database is visible to all other database users on the system. The following operations are supported on shared databases:

  • Users of a shared database can grant DML privileges on objects or schemas to users of other databases.

  • Users of other databases can perform DML operations on objects in the shared database only after they are granted access privileges.

  • Users of other databases cannot perform DDL operations in their own database referring to objects in shared database, even if they hold DML access privileges on objects in the shared database.

Metadata isolation

SQL/MX allows access to SQL/MX system catalog and definition schema tables to all users on the system. In the DBS environment, access to system catalog is not allowed, instead there are number of views in the schema INFORMATION_SCHEMA in each database. To obtain information about associated resources or database objects, users must query the following views in the INFORMATION_SCHEMA:

  • DB_USERS

    Users associated with the database and their association with the database privilege groups.

  • DB_PRIVILEGE_GROUPS

    Privilege groups associated with the database.

  • DB_CPUS

    CPUs associated with the database.

  • DB_DS

    MXCS datasources associated with the database.

  • DB_STORAGE

    Storage volumes assigned to the database.

  • DB_SCHEMAS

    Schemas in the database.

Users of the database in DBS environment have access to the definition schema tables.
The ODBC and JDBC standards provide many catalog APIs, which can be used to access catalog metadata information. To provide metadata isolation, the APIs are restricted to access their own database metadata information. The ODBC applications can use the following APIs:

  • SQLColumns

  • SQLPrimaryKeys

  • SQLStatistics

  • SQLForeignKeys

  • SQLTablePrivileges

  • SQLProcedureColumns

  • SQLGetTypeInfo

  • SQLSpecialColumns

  • SQLTables

  • SQLColumnPrivileges

  • SQLProcedure

User isolation

Database users

Database users are users with External Usernames and are associated with one or more databases in DBS environment. The DBS environment must be configured with Guardian groups, with each group containing 250 users. External Usernames created using DBS commands are associated with the Guardian users that are pre-created for DBS environment. The DBS features ensure that only those users that are associated with the database can establish connection to that database through ODBC/MX or JDBC/MX Type 4 interfaces.

Grantee in GRANT/REVOKE statements

A grantee is a user or a group of users to whom the privilege is being granted. Users associated with a database can grant or revoke privileges on objects to or from users and the privilege groups associated with the database. The following are the considerations for specifying grantees in the GRANT and REVOKE statements:

  • A Guardian username or a Guardian privilege group cannot be specified as a grantee in the GRANT and REVOKE statements.

  • The users of a shared database can specify users or privilege groups of other database as grantee.

  • DB_PUBLIC, which is a substitute for current and future users associated with the database, can be a grantee. When DB_PUBLIC is used as grantee, the GRANT operation is equivalent to granting privileges to the implicit privilege group for all users of the database.

  • The users of a shared database can specify PUBLIC as a grantee.

  • When a database is shared, a user or a privilege group of other databases can be specified as a grantee and they can be granted only DML privileges.

Resource isolation

When a database is created, a set of data volumes is assigned to the database. Applications connected to a database can use the data volumes associated with the database in the DDL statements and SQL utility commands. The DBS resource isolation ensures that an application accessing a database can use only those volumes associated with the database. The default POS_NUM_OF_PARTNS is the number of data volumes associated with the database. The POS_LOCATIONS CQD is set to data volumes associated with the database. Applications accessing the database cannot modify these values. However, the applications can explicitly specify the partition specification attributes at the time of creating a table or an index. Data volumes that are not associated with the database cannot be used in DDL or syntax-based utility operations such as MODIFY.

Database connections

Applications can access a database only through ODBC/MX and JDBC T4 interfaces.

To connect to a database from ODBC applications, set one of the following to the name of the database catalog:

  • CATALOG property in the connection string of the SQLDriverConnect API

  • Catalog option when creating a ODBC/MX client datasource

If not set, the MXCS server defaults to the database's catalog. If set to a different name than the database catalog, the connection fails. The DSN-less connection is not supported to connect to the database.

To connect to a database from JDBC applications, set the serverDataSource driver property to the datasource name associated with the database at connection time. If the CATALOG property is set, it must be set to the database catalog name. This property setting overrides the serverDataSource setting.

Access to the database

Applications accessing a database can execute DDL and DML statements, and syntax-based utility operations such as MODIFY, DUP, FASTCOPY and so on.

MXCS first authorizes and then authenticates the user accessing the database. At the connection time, MXCS first performs user authorization check to validate whether the user is associated with the database. Once authorization is successful, MXCS authenticates the user with the credentials supplied at connection time. Only when both authorization and authentication is successful, a connection is established with the database.

Users of a database cannot connect to a shared database. However, users can access objects in a shared database provided they have access privileges on the objects.

Statement and command access

A user of a database has an access level for that database: READ, WRITE, CREATE, or no access. This access level is assigned when the user is associated with the database, and can be upgraded or downgraded. The access level determines what the user can do with the database.

For DDL statements:

  • Users with CREATE access level on a database can execute DDL statements on that database.

  • Users who are not associated with a database cannot execute DDL statements on that database.

  • Users with READ, WRITE, or no access level on a database cannot execute DDL statements on that database.

  • User with CREATE access level on a database can create objects in the schemas of other users in that database. Such objects are created with the ownership of schema owner.

  • User with CREATE access level on a database can create schemas in that database.

For DML statements:

  • Users with READ, WRITE, or CREATE access level in the database have corresponding DML access to the objects in the database. READ implies SELECT, EXECUTE, and USAGE privilege, WRITE additionally implies INSERT, UPDATE, and DELETE privilege. CREATE additionally implies ALL_DDL privileges.

  • Users who are not associated with the database have DML access to the objects in the shared databases, provided they have been granted access privileges on the objects.

  • Users with no access level in the database do not have DML access on the objects in the database. However, users with CREATE access level in the database can grant DML privileges to such users allowing access on specific objects.

For syntax-based utility commands:

  • Applications accessing the database can execute syntax-based utility commands. Each utility command requires specific DDL/DML privileges. Users executing the syntax-based utility commands must hold the privileges to execute the commands successfully. For information on individual syntax-based utilities, see the SQL/MX 3.5 Release Technical Update.

  • Users of other databases cannot execute syntax-based utility commands on the shared databases.

Restricted SQL Statements

The following statements are not available for applications accessing databases in DBS environment:

  • CREATE CATALOG

  • DROP CATALOG

  • GRANT CREATE CATALOG

  • INITIALIZE SQL

  • REVOKE CREATE CATALOG

  • REGISTER CATALOG

  • UNREGISTER CATALOG

  • UPGRADE/DOWNGRADE

  • GET NAMES OF RELATED CATALOGS

  • DROP SQL

  • GIVE

  • SET NAMETYPE