Restricting Storage Spaces in DDL Operations

In XPS 8.50 and in earlier versions, users who hold the Resource privilege can create databases and database objects in any dbspace or dbslice. This effectively restricts the ability of the DBA or of the DBSA to manage storage resources, and can degrade system performance when individual users have consumed storage space that is needed by other critical processes.

A new feature of Version 8.51 enables the DBA to specify which dbspaces and dbslices are available for DDL operations that create databases, tables, or indexes. The DBA can implement this restriction with new syntax of the SET ENVIRONMENT statement:

SET ENVIRONMENT USABLE_SPACES  { "space [, space ... ]"    
                             |  'DEFAULT SPACE' | DEFAULT | IMMUTABLE | MUTABLE } 

Here space is the name of a dbspace or of a dbslice where non-DBA users can create database objects.

When USABLE_SPACES is set to MUTABLE, any user can execute SET ENVIRONMENT USABLE_SPACES. Otherwise, only the DBA or user informix can issue this statement.

The database server issues an error (and the DDL operation fails) if a non-DBA subsequently attempts to create an index, table, or database in a dbspace or a dbslice that the most recent SET ENVIRONMENT USABLE_SPACES statement did not specify in the space list.

For more information about setting USABLE_SPACES and displaying its value, see Restricting DDL Storage Spaces.