Creating and Editing Database Roles
Go Up to Establishing Database Security
Contents
A role is a named collection of privileges. In a business setting, roles are defined according to job competency, authority and responsibility. The ultimate intent of creating database roles is to achieve optimal security administration based on the role each individual plays within the organization.
Advantages of effective role creation and application include:
- Optimally assigning roles to user privileges.
- Identifying users who operate outside the normal pattern.
- Detecting and eliminating redundant or superfluous roles or user privileges.
- Keeping role definitions and user privileges up-to-date.
- Eliminating potential security loopholes and minimizing consequent risks.
By implementing database roles for specific user groups, you can save time by assigning roles to users instead of assigning permissions to each object for each individual user. Database roles enable you to manage the permissions logical model users have to insert, select, update, delete, alter, or reference entities, tables, and views in the logical database. In addition to being supported by tables and views in the physical data model, depending on the database platform, roles also can be applied to select physical models to manage system privileges such as those required to backup databases and logs, or to create databases, defaults, procedures, rules. tables, and views. The system privileges available are database dependent and are listed on page 2 of the Role Wizard and Role Editor.
Database roles are supported in the logical model and by the following physical database platforms:
- Microsoft Azure Synapse Analytics
- Microsoft SQL Server
- Oracle
- Snowflake
To add a database role to a model:
- In the Data Model Window, expand the logical Main Model or the Main Model of a physical model for which database roles are supported, right-click the Roles node, and then click New Database Role.
- In the Role Wizard, assign the permissions desired and then click Finish.
- The options and permissions available are platform-dependent.
General page/tab
- Role Name: The restrictions for the number of character permitted when defining role names is dependent on the database platform.
The following options specify the specific method used to authorize a user before the role can be enables with the SET ROLE statement.
- Password: When selected, specifies that the user is a local user who must specify the password when enabling the role.
- Externally: When selected, specifies that the user is an external user who must be authorized by an external service (such as an operating system or third-party service) to enable the role.
- Globally: When selected, specifies that the user a global user who must be authorized to use the role by the enterprise directory service before the role is enabled or at login.
- Not Identified: When selected, indicates that this role is authorized by the database and no password is required to enable the role.
- Add: When selected, the Select User dialog appears where you can choose the users to assign to the role. CTRL-click to select multiple users. If you have not previously defined a user, the list will be empty.
System Privileges page/tab
Clicking the column name selects the entire column. CTRL-click to select multiple objects individually. The permissions available are dependent on the physical platform selected.
Object Privileges page/tab
Set access privileges for users for objects such as tables and views. Some database platforms also allow detailed access to database objects such as procedures, packages, materialized views, sequences, and functions. Clicking the column name selects the entire column. Control-click to select multiple objects individually.
Dependencies page/tab
If the object associated with the permissions assigned have dependencies, these will be displayed here. Dependencies are SQL code such as functions, triggers, and procedures. For more information, see Creating and Editing Functions, Creating and Editing Triggers, and Creating and Editing Procedures.
Definition page/tab
Enter or edit a definition for the user. Usually used to describe why you are defining the limitations of the user. If the target database supports it, ER/Studio Data Architect adds this definition as a comment when generating SQL code.
DDL tab
View the CREATE ROLE statement needed to build the user, or the GRANT... ON CONNECT statement required to establish user privileges when connecting to the database. In physical models, ER/Studio Data Architect uses the platform-specific parser of the model's selected database platform to generate the view. If you are adding a view to a logical data model, the default parser AINSI SQL is used unless you change the View Parser in the Logical Model Options to another parser. For more information, see Defining Model Options for the Selected Model.
Attachment Bindings tab
Bind an external piece of information, or attachment to the role. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary and must be applied to the role before they will display on this tab. For more information, see Attaching External Documents to the Data Model.
Notes
- To view the roles you create in the Data Model Window, ensure that Display All Security Objects is selected in View > Diagram and Object Display Options > Security Objects . Reverse-engineered diagrams do not display security objects by default; however, models that you create from scratch display security objects by default.
- Once you have created a role, you can assign that role to a particular user in the User Editor or User Wizard.
- You can change the permissions assigned to a role in the Role Editor or on the Permissions tab of the Entity Editor or Table Editor. For more information, see Creating and Editing Entities and Creating and Editing Tables.
- Database roles created in the logical model are propagated to the physical model when you generate a physical model that supports database roles.
- Depending on the database platform, database roles can provide security for entities, tables, functions, materialized views, packages, procedures, and sequences.