Enterprise Manager: Databases > Permissions

To view this topic for the preceding Enterprise Manager, see Enterprise Manager Java App: Database - Permissions and Roles Tabs.

BBj provides two types of database permissions: legacy permissions, and object level permissions. Legacy permissions provide the least amount of control over access to databases and are provided for backward compatibility purposes and to make the assignment of permissions very easy to manage. Object Level Permissions are new to BBj 11.00 and provide object level user access controls (i.e. table, view, stored procedure, etc.) allowing the administrator to grant or revoke permissions to individual objects, users, and groups of users.

Legacy Permissions

Legacy permissions in BBj have been available since the first release of BBj. These permissions allow the administrator to limit user access to databases and limit the kind of operations users can perform on those databases. The granularity of permissions is limited to the entire scope of the database. For finer tuning of user permissions at the individual object level (i.e. table, view, stored procedure, etc.), use Object Level Permissions described below.


Option

Description

Default Permissions

Default access policy for all user accounts on this BASIS DBMS. This means that each user on this BASIS DBMS automatically has this access unless otherwise specified. For example, if read-only is specified, all users have read access to the system. To give certain users write access, they must be added to the read/write list of users (see below). It is recommended that read-only or deny-all be selected for this option. There are four possible selections:

 

Allow All

Complete access, including administrative privileges.

 

Read Only

Read-only access. No administrative privileges.

 

Read/Write

Read/write access. No administrative privileges

 

Deny All (recommended)

Users have no access to the system until they are specifically granted permission on a per-user basis

To grant individual users permissions different than the default permissions:

  1. Select the user from the list of users.

  2. Click in the Permissions column for the selected user.

  3. Choose the permission level for that user from the dropdown list.

  4. When finished changing permissions, click the [Save] button at the bottom right corner of the panel.

Object Level Permissions

BBj 11.0 introduces a powerful new feature called “Object Level Permissions”. When we speak of “objects” in a database, we are referring to tables, views, and stored procedures. This new feature gives the administrator the power to assign different permissions to different users or groups of users, on different objects in the database. For example, a role (group of users) called HUMAN_RESOURCES might contain a list of all those in the HR department. This group of users might have access to information such as salaries, performance reviews, etc. that the company does not want everyone to access. The HR specific tables would only have permission granted to this role, and any other individual users who might need the information. Anyone not explicitly granted permission or made a part of this role would not be able to access the information in these tables. This puts the security at the database level, instead of the application level making it impossible to circumvent by accessing the database outside the application.

Compare/Contrast Standard Permissions and Object Level Permissions

Legacy Permissions

Object Level Permissions

Users can have read or read/write permissions

Users can have SELECT, UPDATE, INSERT, DELETE, or any combination

Permissions are the same for every table, view and stored procedure

Users can have different permissions for every table, view and stored procedure

Permissions can only be set from The Enterprise Manager or the Admin API

Permissions can be set from The Enterprise Manager, Admin API, or using standard SQL GRANT/REVOKE statements

No way to group users with similar permissions

Supports grouping users using ROLES

Using Object Level Permissions

The default setting for databases is to use Standard Permissions. With a single check box, the administrator can switch to using Object Level Permissions or back to Standard at any time.

To enable Object Level Permissions:

  1. While logged into the Enterprise Manager as the “admin” user, click on the database to administer.
  2. Select the “Permissions” tab.
  3. Put a check in the box labeled, “Use Object Level Permissions”.
  4. Click [Save] to update the database configuration.

Once Object Level Permissions are enabled, you can begin to assign permissions at the database level as well as the object level. The “Permissions” tab shows a list of users and roles who have permissions assigned at the database level. These permissions include things such as CREATE TABLE, ALTER TABLE, CREATE PROCEDURE, ALTER PROCEDURE, etc.

Using Roles

Roles are a powerful feature used to make the management of permissions much easier. A “role” is simply a list of users who will have the same permissions for certain objects in the database. An example was given about using a human resources role. Let’s take a look at how to create roles and manage membership in the roles.

Under the "Roles" tab, click ]+] to add a new role.

Enter the Role Name and click [OK].

Click [+] in the left panel titled “Role Members” to add users to the new role.

Check the specific user(s) to add to the “human resources” role.

Click [OK].


The modified role now displays its assigned members.

The Roles tab shows a list of roles currently defined for the database. These roles are specific to only the database for which they were defined. To view the members of a role, simply select the role from the list.

Once you have defined roles, you can assign permissions to roles instead of individual users. This is the recommended way to assign permissions as it is highly unlikely that each user will need completely different permissions on database objects. In this example we have an ADMINISTRATORS role which would be used to assign database level permissions to various users who need to perform administrative tasks such as CREATE and DROP TABLE. The READ_ONLY role will be used for users who need only READ_ONLY access, while READ_WRITE would be used for users who need to perform read/write operations from SQL.

Should it become necessary to change the type of access a user needs, simply add them to or remove them from a role and those permissions are instantly changed for that user on any objects that have permissions defined for that role.

Other Ways To Manage Permissions

There are three ways to manage permissions: using the BBj Enterprise Manager, programmatically using the Admin API, or using SQL GRANT/REVOKE.

SQL Statements Using GRANT/REVOKE

Using GRANT, REVOKE, CREATE ROLE and DROP ROLE, you can manage permissions using standard SQL statements. Here are a few examples of how you would use these statements:

Create a new role:
CREATE ROLE MY_NEW_ROLE

Add a user to a role:
GRANT MY_NEW_ROLE TO 'jash'

Add SELECT permission to a role on a table:
GRANT SELECT ON MY_TABLE TO MY_NEW_ROLE

Add UPDATE and INSERT permission to two users on a table and allow them to do the same for other users:
GRANT UPDATE,INSERT ON MY_TABLE TO 'jash','jdoe' WITH GRANT OPTION

Revoke UPDATE permission from two users on a table:
REVOKE UPDATE ON MY_TABLE FROM 'jash','jdoe'