Database Designer access requirements

By default, only users with the DBADMIN role can run Database Designer. Non-DBADMIN users can run Database Designer only if they are granted the necessary privileges and DBDUSER role, as described below. You can also enable users to run Database Designer on the Management Console (see Enabling Users to run Database Designer on Management Console).

1. Add a temporary folder to all cluster nodes with CREATE LOCATION:

   => CREATE LOCATION '/tmp/dbd' ALL NODES;
   

2. Grant the desired user CREATE privileges to create schemas on the current (DEFAULT) database, with GRANT DATABASE:

   => GRANT CREATE ON DATABASE DEFAULT TO dbd-user;
   

3. Grant the DBDUSER role to dbd-user with GRANT ROLE:

   => GRANT DBDUSER TO dbd-user;
   

4. On all nodes in the cluster, grant dbd-user access to the temporary folder with GRANT LOCATION:

   => GRANT ALL ON LOCATION '/tmp/dbd' TO dbd-user;
   

5. Grant dbd-user privileges on one or more database schemas and their tables, with GRANT SCHEMA and GRANT TABLE, respectively:

   => GRANT ALL ON SCHEMA this-schema[,...] TO dbd-user;
   => GRANT ALL ON ALL TABLES IN SCHEMA this-schema[,...] TO dbd-user;
   

6. Enable the DBDUSER role on dbd-user in one of the following ways:

   • As dbd-user, enable the DBDUSER role with SET ROLE:

     => SET ROLE DBDUSER;
     

   • As DBADMIN, automatically enable the DBDUSER role for dbd-user on each login, with ALTER USER:

     => ALTER USER dbd-user DEFAULT ROLE DBDUSER;
     

Enabling users to run Database Designer on Management Console

Users who are already granted the DBDUSER role and required privileges, as described above, can also be enabled to run Database Designer on Management Console:

1. Log in as a superuser to Management Console.

2. Click MC Settings.

3. Click User Management.

4. Specify an MC user:

   • To create an MC user, click Add.

   • To use an existing MC user, select the user and click Edit.

5. Next to the DB access level window, click Add.

6. In the Add Permissions window:

   1. From the Choose a database drop-down list, select the database on which to create a design.

   2. In the Database username field, enter the dbd-user user name that you created earlier.

   3. In the Database password field, enter the database password.

   4. In the Restrict access drop-down list, select the level of MC user for this user.

7. Click OK to save your changes.

8. Log out of the MC Super user account.

The MC user is now mapped to dbd-user. Log in as the MC user and use Database Designer to create an optimized design for your database.

DBDUSER capabilities and limitations

As a DBDUSER, the following constraints apply:

• Designs must set K-safety to be equal to system K-safety. If a design violates K-safety by lacking enough buddy projections for tables, the design does not complete.

• You cannot explicitly advance the ancient history mark (AHM)—for example, call MAKE_AHM_NOW—until after deploying the design.

When you create a design, you automatically have privileges to manipulate that design. Other tasks might require additional privileges: