Creating and Editing Database Users
Go Up to Establishing Database Security
Contents
Granting database access, authorities, and privileges to users and groups is one of the most important ways to ensure data security.
In conjunction with database roles, users and the permissions you grant them 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, users also can be applied to specific 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 display on page 2 of the User Wizard and User Editor.
Database users are supported by the logical data model and are also supported by physical data models for the following platforms:
- Amazon Redshift
- IBM DB2 for LUW
- IBM DB2 for OS/390
- IBM DB2 for z/OS
- Microsoft Azure Synapse Analytics
- Microsoft SQL Server
- Oracle
- Snowflake
- Sybase ASE
- The User Wizard and User Editor share the same options, except for Attachment Bindings options which are present only in the editor.
- In the Data Model Window, expand the Main Model of the Logical model, or of a physical model for which database users are supported:
- Right-click the Users node, and then click New Database User to open User Wizard.
- Right-click an user, and then click Edit Database User to open User Editor.
- In the User Wizard/User Editor, assign the permissions desired or make the changes needed and then click Finish.
- In the Data Model Window, expand the Main Model of the Logical model, or of a physical model for which database users are supported:
Note: Options and permissions available are platform-dependant
General page/tab
Not all options are available on all platforms.
- Properties:
- User Name: The restrictions for the number of characters permitted when defining user and group names is dependent on the database platform.
- Password: When selected, specifies that the user is a local user who must specify a password when accessing the database. The password is entered as plain text and is inserted as plain text in the generated CREATE USER DDL statement.
- MD5Hash: When selected, the password is entered in plain text and ER/Studio Data Architect generates the necessary MD5 hash when creating the CREATE USER DDL statement.
- disable: When selected, the Password text field is disabled and the Password Disabled option is inserted in the generated CREATE USER DDL statement.
- 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 access the database.
- Globally: When selected, specifies that the user is a global user who must be authorized to use the database by the enterprise directory service or at login.
- In Group: Members of a user group automatically inherit group privileges. Privileges granted to a user override the privileges associated with any groups the user is a member of, and are retained if the user is later removed from the groups. Explicit privileges granted to a user must be explicitly revoked.
- Roles:
- Add: When clicked, the Select Roles dialog appears where you can select the roles to assign to the user. If you have not previously defined a role, the list will be empty.
- Drop: When clicked, the selected role or roles will be removed for the user.
- Tablespace Quota: On some physical models, you can define tablespace usage parameters (in units of 10KB). This will limit a user's ability to grow a tablespace, but should not be used instead of designed range limits.
- The following options are available for Amazon Redshift only, and are used for generating parts of the CREATE USER DDL statement.
- Valid Until: This would not be validated and would be inserted in the DDL as it is for the VALID UNTIL option.
- Create DB
- Create User
- Syslog Access
- Connection Limit: Is interpreted as UNLIMITED if the checkbox is unchecked.
- The following options are available for Snowflake only, and are used for generating parts of the CREATE USER DDL statement.
- Login Name
- Password
- Display Name
- First Name
- Middle Name
- Last Name
- Must Change Password
- Disabled
- Snowflake Support
- Days to Expiry
- Minutes to Unlock
- Default Warehouse
- Default Namespace
- Default role: You can pick from the available roles in the model.
- Minutes to Bypass MFA
- Disable MFA
- Rsa_public_key
- Rsa_public_key_2
System Privileges page/tab
Set system privileges for users. Select the specific privilege and then Grant or Revoke.To select the entire column click on the column name. Press Ctrl and then click to select multiple lines. 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.
Select the specific privilege and then Grant or Revoke.To select the entire column click on the column name. Press Ctrl and then click to select multiple lines. The permissions available are dependent on the physical platform selected.
Dependencies page/tab
If the object associated with the permissions assigned has 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
Describe why you are defining the limitations of the user. Enter or edit a definition for 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 DDL code needed to build the user and to establish user privileges when connecting to the database. In physical models, ER/Studio Data Architect uses the platform-specific parser of the selected database platform of the model to generate the view. When 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
Attachments are created in the Attachments folder of the Data Dictionary and must be applied to the database user before they will display on this tab. For more information, see Attaching External Documents to the Data Model.
Here you can bind an external piece of information, or attachment to the user. 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.
Notes
- In order to view the users 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 created from scratch do.
- If you don't want to assign a role in the moment you are creating the user, you can also assign that user a particular role later using the User Editor, the Role Editor or the Role Wizard. For more information, see Associating Database Users with New Database Roles and Associating Database Roles with New Database Users.
- You can change the permissions assigned to a user in the User Editor or on the Permissions tab of the Entity Editor or Table Editor. For more information, see Creating and Editing Entities and Tables.
- Database users created in the logical model are propagated to the physical model when you generate a physical model that supports database users.
- Depending on the database platform, database users can provide security for entities, tables, functions, materialized views, packages, procedures, and sequences.