Home
Designing databases
The first step in creating a relational database is to construct a data model, which is a precise, complete definition of the data you want to store. After you prepare your data model, you must implement it as a database and tables. To implement your data model, you first select a data type for each column and then you create a database and tables and populate the tables with data. You can also implement fragmentation strategies and control access to your data.
Designing and Implementing a Database
The Informix® Database Design and Implementation Guide provides information to help you design, implement, and manage your Informix databases. It includes data models that illustrate different approaches to database design and shows you how to use structured query language (SQL) to implement and manage your databases.
Managing databases
Grant and limit access to your database
Grant privileges
Automate privileges
Roles
Create a role

Designing databases
The first step in creating a relational database is to construct a data model, which is a precise, complete definition of the data you want to store. After you prepare your data model, you must implement it as a database and tables. To implement your data model, you first select a data type for each column and then you create a database and tables and populate the tables with data. You can also implement fragmentation strategies and control access to your data.
- Designing and Implementing a Database
  The Informix® Database Design and Implementation Guide provides information to help you design, implement, and manage your Informix databases. It includes data models that illustrate different approaches to database design and shows you how to use structured query language (SQL) to implement and manage your databases.
  - Basics of database design and implementation
  - Managing databases
    - Table fragmentation strategies
    - Grant and limit access to your database
      - Use SQL to restrict access to data
      - Control access to databases
      - Grant privileges
        Database-level privileges
        Ownership rights
        Table-level privileges
        Column-level privileges
        Type-level privileges
        Routine-level privileges
        Language-level privileges
        Automate privileges
        Automate with a command script
        Roles
        Create a role
        Manipulate user privileges and grant roles to other roles
        As DBA, you can use the GRANT statement to grant role privileges to users. You can also give a user the option to grant privileges to other users. Use the WITH GRANT OPTION clause of the GRANT statement to do this.
        Enable default roles and non-default roles
        Confirm membership In roles and drop roles
        Determine current role at runtime
      - Use SPL routines to control access to data
      - Views
      - Privileges and views
    - Distributed queries
  - Object-relational databases

Create a role

To start the role creation process, determine the name of the role and the connections and privileges that you want to grant to users who hold that role. Although the connections and privileges are strictly in your domain, you must consider some factors when you declare the name of a new role. For the name of a user-defined role, do not use any of the following SQL keywords, access privileges, or built-in roles:

ALTER
C
CONNECT
DBA
DBSECADM
DEFAULT
DELETE
EXECUTE
EXTEND
INDEX
INSERT
NONE
NULL
PUBLIC
REFERENCES
RESOURCE
SELECT
SETSESSIONAUTH
SPL
UPDATE

Because role names are authorization identifiers, rather than SQL identifiers, the maximum length of a role name is 32 bytes.

A role name must be different from existing role names in the same database. A role name must also be different from user names that are known to the operating system, including network users known to the server computer. To make sure that your new role name is unique, check the names of the users in the shared memory structure who are currently using the database, and in the following system catalog tables:

sysusers
systabauth
syscolauth
sysfragauth
sysprocauth
sysroleauth
syssecpolicyexemptions
sysxtdtypeauth

When the situation is reversed and you are adding a user to the database, check that the user name is not the same as any of the existing role names.

After you approve the role name, use the CREATE ROLE statement to create a new role. After the role is created, all privileges for role administration are, by default, given to the DBA.

Important: The scope of a role is the current database only. When you execute the SET ROLE statement, the specified role takes effect in the current database only. As a security precaution, a user who holds access privileges only through a role cannot access tables in a remote database through a view, trigger, or procedure.