Granting System Privileges

A system privilege is a set of rights assigned to a database user, group, or role. You use system privileges to create user profiles with different levels of influence over the database content. The procedure for defining privileges is identical for users, groups, and roles.

System privileges are used in association with object permissions (see Granting Object Permissions) to evaluate the rights of a user, group, or role. For example, even if a user has the modify privilege, he cannot modify an object on which he has no update permission.

System privileges are granted to a user. A user with an administrative profile is also allowed to revoke a privilege. By default, a user belonging to a group or having a role inherits the group or role privileges and inherited privileges appear in the Privileges tab of the user property sheet.

System privileges vary according to the DBMS you are using. The list of privileges also includes predefined roles (like connect, or resource) for an easier use. In some DBMS, system privileges are called permissions. In this manual, the term privilege is used for any right granted to a user, a group, or a role. Permissions are defined for objects.

Note: To review and edit the list of available privileges in the Resource Editor, select Database > Edit Current DBMS, select the item Script > Objects > Privilege > System, and edit the list as appropriate. The Privilege category also contains entries that define the syntax for the necessary SQL statements for granting and revoking privileges.
  1. Open the property sheet of a user, role, or group, and click the Privileges tab.
  2. [optional] Click the Show/Hide All Inherited Privileges tool to show privileges that have been inherited from a group. Inherited privileges display in red, while privileges directly granted to the user are blue.
  3. Click the Add Objects tool to choose one or more of the privileges available in the DBMS, and click OK to grant them to the user, role, or group:


  4. [optional] To change the state of a privilege (whether granted directly, or inherited from a group), click in the State column to cycle through the available states, or click on the appropriate tools in the Privilege state group box at the bottom of the tab:

    • Grant – [default] Assigns the privilege to the user.

    • Grant with admin option - Assigns the privilege to the user, and allows the recipient to pass on the privilege to other users, groups, or roles. For example, you assign the CREATE TABLE privilege for user Designer_1 and then click the Grant With Admin Option button to permit Designer_1 to grant this privilege to other users.

    • Revoke – Revokes the privilege inherited from a group or role for the current user or group.

    • None - Cancels any state and cleans up the current cell.



    The following table summarizes the different privilege combinations:

    Privilege combination

    Description


    Privilege granted to user


    Privilege inherited from group


    Privilege inherited from group and revoked to user


    Privilege inherited from group overloaded by "with admin option"
  5. When the privileges are correct, click OK to return to the model.
Parent topic: Users, Groups, and Roles (PDM)