EM: Databases/SQL: Databases > Security

Introduction

Security is, or should be, a major consideration for any database administrator. Depending on the requirements of each company, this could be as simple as requiring a user name and password to connect to the system, or as complex as different permissions for different types of SQL operations on various objects in the database for each individual user. Standard permissions have been available in BBj since version 1.0. These permissions give the administrator the ability to assign read-only or read/write access to an entire database. However, standard permissions did not permit assigning different permissions to different objects (tables, views, etc.) in the database – it is all or nothing. While this is very easy to manage, it limits the control that the administrator has over access to sensitive data. This in turn could limit the ability for users to have easy desktop data query access to subsets of the company production database. Workarounds are possible but they require far more maintenance and structuring of the database by the database administrator.

BBj 11.0 and higher provides a complete feature set for managing user permissions in a powerful new feature called “Object Level Permissions.” This new feature will, for many customers, unlock meaningful desktop data query access to corporate data for all the users within a company with appropriate restrictions that are easy to structure and maintain. For an in-depth look at permissions and roles, see The BASIS International Advantage article DB Security That You Have Always Dreamed About.

Description

The Databases Security tab configures database access by security model. It supports Legacy Permissions for database-level access and Object Level Permissions for per-user privileges and roles. In the current UI, administrators can set default permissions, review user assignments, and manage privilege and role settings for the selected database.

Note: selecting a database from Databases name list, opens its configuration interface, and enabling access to Security.

Location

EM Navigator → Databases/SQL → Databases → Security

Toolbar

Button	Function
	Opens the New Role dialog and adds the entered role name to the Roles list. Opens the User Selection dialog and adds selected users to the Role Members list.
	Removes/deletes selected Roles/Role Members.

Button

Function

Opens the New Role dialog and adds the entered role name to the Roles list.

Opens the User Selection dialog and adds selected users to the Role Members list.

Removes/deletes selected Roles/Role Members.

Security Type: Legacy Permissions

The Security Type setting determines which security model is used for the selected database. Legacy Permissions applies the legacy user-permission model with default and per-user permission assignments, while Object Level Permissions switches the interface to object-level privilege and role management for the database.

Security Type Settings

Settings	Description
Legacy Permissions	While an administrator may assign read only or read/write access to an entire database, there is no way to assign different permissions to different objects (tables, views, etc) in the database. Legacy permissions are very easy to manage, but limits the control that the administrator has over access to sensitive data.
Object Level Permissions	In BBj 11.0 and higher, the administrator may assign different permissions to different users or groups of users on different objects in the database. “Objects” in a database refer to tables, views, and stored procedures.

Default Permissions

The Default Permissions sets the default database-level permission used by the Legacy Permissions security model. In the current UI, changing this list affects the legacy permission context, while selecting Object Level Permissions switches the interface to privilege and role management instead of using database-wide default permission behavior.

Default Permissions Settings

Settings	Description
Deny All	The legacy default permission to deny all database access.
Read Only	Sets the legacy default permission to allow read-only access to the database.
Read/Write	The legacy default permission to allow reading from and writing to the database
Allow All	Sets the legacy default permission to allow full database access.

Security: Columns

Legacy Permissions assigns database access levels per listed user, allowing each user entry to inherit or override the default permission.

Columns Settings

Settings	Descriptions
User	Lists the users available for permission assignment under the Legacy Permissions security model.
Permissions	Specifies the permission level assigned to each listed user under the Legacy Permissions security model.

Security Type: Object Permissions

When Security Type is set to Object Level Permissions, the Privileges view opens to manage object-level privilege assignments for the selected user. Selecting the Roles tab switches the interface to role management, where roles and their associated Role Members can be created, selected, and maintained for the selected database.

Object Level Permissions Settings

Value

Descriptions

Privileges

Lists object-level privileges for the selected user and allows grant, deny, and with-grant assignments.

Value	Descriptions
admin	Selecting admin loads its object-level privilege assignments, with Grant and With Grant selected by default in the current UI.
guest	Selecting guest loads its object-level privilege assignments, with Grant, Deny, and With Grant initially unselected in the current UI.

Privileges Column

Object-level privilege controls let users apply or clear Grant, Deny, and With Grant selections for all listed privileges of the selected user.

Privileges Column Settings

Value	Description
Grant All/None	Clicking toggles all checkboxes in the Grant column for the listed privileges on or off.
Deny All/None	Clicking toggles all checkboxes in the Deny column for the listed privileges on or off.
With Grant All/None	Clicking toggles all checkboxes in the With Grant column for the listed privileges on or off.

Roles

The Roles manages database roles and their assigned members when Security Type is set to Object Level Permissions. Selecting the Roles tab displays the Roles and Role Members columns. New roles are created from the New Role dialog, and selected users are added through the User Selection dialog.

Value	Description
Roles	Lists the defined database roles, clicking the opens the New Role dialog to add a role.
Role Members	Lists the users assigned to the selected role, clicking the opens the User Selection dialog to add members.