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:
- IBM DB2 for LUW 5.x, 6.x, 7.x, 8.x, 9.x. 10.x
- IBM DB2 for OS/390 5.x - 10.x, and DB2 for z/OS 7.x, 8.x, 9.x, and 10.x
- Microsoft SQL Server 4.x, 6.x, 7.x, 2000, 2005, 2012, 2014
- Oracle 7.x, 8.x, 9i, 10g, 11g, 12c
- Sybase ASE 11.0, 11.5, 11.9, 12.0, 12.5, 15
- 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.
- In the User Wizard, assign the permissions desired, and then click Finish.
- Options and permissions available are platform-dependant.
General page/tab
- User Name: The restrictions for the number of character permitted when defining user and group names is dependent on the database platform.
- Is 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.
- Password: When selected, specifies that the user is a local user who must specify a password when accessing the database.
- 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.
- Add: When clicked, the Select Roles dialog appears where you can select the roles to assign to the user. Hold CTRL-Shift to select multiple roles. If you have not previously defined a role, the list will be empty.
- 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.
System Privileges page/tab
Clicking the column name selects the entire column. Control-click to select multiple objects. 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 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 CREATE USER 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 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
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. 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.
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.
- Once you have created a user, you can assign that user a particular role in the Role Editor or 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.