Who can run Database Designer

• 1: Granting and enabling the DBDUSER role
• 1.1: Allowing the DBDUSER to run Database Designer using Management Console
• 1.2: Allowing the DBDUSER to run Database Designer programmatically
• 2: DBDUSER capabilities and limitations

1 - Granting and enabling the DBDUSER role

For a non-DBADMIN user to be able to run Database Designer using Management Console, follow the steps described in Allowing the DBDUSER to run Database Designer using Management Console.

For a non-DBADMIN user to be able to run Database Designer programmatically, following the steps described in Allowing the DBDUSER to run Database Designer programmatically.

1.1 - Allowing the DBDUSER to run Database Designer using Management Console

To allow a user with the DBDUSER role to run Database Designer using Management Console, you must create the user on the Vertica server.

As DBADMIN, take these steps on the server:

1. Add a temporary folder to all cluster nodes.

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

2. Create the user who needs access to Database Designer.

   => CREATE USER new_user;
   

3. Grant the user the privilege to create schemas on the database for which they want to create a design.

   => GRANT CREATE ON DATABASE new_database TO new_user;
   

4. Grant the DBDUSER role to the new user.

   => GRANT DBDUSER TO new_user;
   

5. On all nodes in the cluster, grant the user access to the temporary folder.

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

6. Grant the new user access to the database schema and its tables.

   => GRANT ALL ON SCHEMA user_schema TO new_user;
   => GRANT ALL ON ALL TABLES IN SCHEMA user_schema TO new_user;
   

After you have completed this task, map the MC user to new_user:

1. Log in to Management Console as an MC Super user.

2. Click MC Settings.

3. Click User Management.

4. To create a new 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, do the following:

   1. From the Choose a database drop-down list, select the database for which you want the user to be able to create a design.

   2. In the Database username field, enter the user name you created on the Vertica server, new_user in this example.

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

   4. In the Restrict access drop-down list, select the level of MC user you want 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 the user that you created on the Vertica server. Log in as the MC user and use Database Designer to create an optimized design for your database.

For more information about MC users, see Users in Management Console.

1.2 - Allowing the DBDUSER to run Database Designer programmatically

To allow a user with the DBDUSER role to run Database Designer programmatically, take these steps:

1. The DBADMIN user must grant the DBDUSER role:

   => GRANT DBDUSER TO <username>;
   

   This role persists until the DBADMIN user revokes it.

2. For a non-DBADMIN user to run the Database Designer programmatically or using Management Console, one of the following two steps must happen first:

   • If the user's default role is already DBDUSER, skip this step. Otherwise, The user must enable the DBDUSER role:

     => SET ROLE DBDUSER;
     

   • The DBADMIN must add DBDUSER as the default role for that user:

     => ALTER USER  DEFAULT ROLE DBDUSER;
     

2 - DBDUSER capabilities and limitations

The DBDUSER role has the following capabilities and limitations:

• A DBDUSER cannot create a design with a K-safety less than the system K-safety. If the designs violate the current K-safety by not having enough buddy projections for the tables, the design does not complete.

• A DBDUSER cannot explicitly change the ancient history mark (AHM), even during deployment of their design.

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