Applying Database Security

Applying Database Security

This topic is of interest to administrators concerned with implementing security controls based on end-users’ privileges to specified areas of a shared proCube database. This section also functions as a good transition to proCube Server and proCube Xchange, since security is involved almost exclusively in a multi-user, shared database environment.

proCube provides advanced security features that allow database administrators to implement very exact Metadata and Fact Data privileges for a shared database. For example, an Administrator might give a user Read privileges to most data, but only Write privileges to some data in a certain cube, and no viewing rights at all to a precise (but significant) Fact Data range. We will consider Metadata and Fact Data privileges in detail in this topic.

There will also be discussions of setting up secondary administrators, called Operational Administrators; locking fact data ranges; and the Security Export/Import feature.

When implementing security in a database, you must go through the following six steps, which will be discussed sequentially in the following topics:

  • Securing the Database (New or Existing)

  • Defining Users & Groups

  • Assigning Database Privileges (to Add New Dimensions and Cubes)

  • Assigning Metadata and Fact Data Privileges

  • Specifying Cube Access

  • Locking Ranges of Fact Data

  • Sharing the Database

Securing the database

For any database security schema to work, you must first indicate that the database will be a secure database. Doing so will establish a minimum level of security: a User Name/Password Log On requirement for the database. You can secure a database at the time you create it or at a later time.

Administrative privileges

Users placed in a special, pre-defined group named Administrator have access to all functions and data on the system. When a database is initially created in proCube, the Administrator group is automatically created, as is the first user, also named Administrator. Neither this group nor that user can be deleted.

Another level of administrator, called Operational Administrator is available in proCube. This feature is provided so you can set up more than one administrator—perhaps departmental administrators to share the workload—who do not have access to view all data on the system.  It does this by working hand-in-hand with the menu function, Cube Access. Operational Administrators have access to most administrative functions and all cube data except the data in cubes that you choose to restrict. You specify those cubes in the menu function, Model, Cube Access. For instance, you might restrict access to a cube of salary data, but have several operational administrators who can access all other data.

In the remainder of this topic, both levels of access as are referred to as having “administrative privileges,” with the understanding that such privileges might not always apply to every cube.

Securing a New Database

To secure a new database:

  1. In the New Database dialog, check the Secure Database box. (To follow the example in this topic, create a new database called NewDBSecure.)


Figure 1.    New Database Dialog

  1. Click OK. The proCube Database Login dialog will appear. This dialog includes a text box where you can enter a <<User Name>> (a default is initially supplied, based on your Windows networking User Name) and where you must enter and confirm your <<Password>>.


Figure 2.    Change Password Dialog

User Name is not case-sensitive, but Password IS CASE SENSITIVE. A Password must be at least eight (8) characters.

  1. Click OK in this dialog, then click OK in the New Database dialog. You are returned to the main proCube window.

If you establish Security when you create a new database, the User Name you use (in the example, YourName) will automatically be placed in the Administrator Group, with full privileges to every aspect of the database. (Later in this topic, how to manually create and place users in Groups, as well as how to assign privileges is described.)

  1. Select Model, Users & Groups. The User & Groups dialog box appears. Note that the example, YourName, appears in the Administrator Group, along with a User calledAdministrator (shown expanded in the following figure):


Figure 3.    User Group Dialog

There is always a user named Administrator that is created in a Secure Database and who appears in the Administrator  Group. The default password for Administrator is “Administrator” (no quotes). A user with Administrator privileges may change any User’s Password (including his own), define every aspect of other Users’ access to database information, and even delete Users. However, the user Administrator can neither be deleted from the database nor moved out of the Administrators Group. Nor can the Administrator Group be deleted or renamed.

  1. Click OK.

At this point, you can proceed with doing everything required to build your database—creating Dimensions, Cubes, Slices and an advanced, very detailed, adjustable security setup for database Users. In other words, in this scenario, you would build the database first, then grant privileges to Users and Groups, since most of those privileges are predicated on providing User/Group access to Metadata and Fact Data existing in the database. (Creating Users and Groups is described later in this topic.)

At this point, a user with Administrator rights has full access to the Security selections on the Model ribbon.

After you close a Secure Database, whenever users open it, they must supply a valid User Name and Password (unless Security has been dis-enabled).

Securing an existing database

You can institute a Security schema for a database that has already been created. In this scenario, you may have fully created the Metadata and loaded the most up-to-the-instant Fact Data—but you now want to assign privileges to the various users who will access the database. Before granting those privileges, you must indicate that this will be a Secure Database.

To secure a database that already exists (e.g., the Acme Trading Company database):

  1. From the Model ribbon's Security Group, click Database Privileges to open the Database Security dialog. Note that Database Privileges is the only active selection in the Security Group.

  2. Select Secure Database.


Figure 4.    Database Security Dialog

  1. Click OK. The following message appears:


Figure 5.    Changed Database Security Message

  1. Click OK.

You will want to close the database (save changes), then reopen it using the default User Name, Administrator and Password Administrator—the only log on that will work at this point.

Having done so, you can begin to create Users, assigning User Names and Passwords during in the process, as well as create Groups with various security access parameters. (This will be the subject of the next section, after we discuss dis-enabling Security).

Disabling security

There may be an occasion when you want to remove all security from a database. You must be a member of the Administrator group to perform this procedure:

  1. From the Model ribbon, click Database Privileges. The Database Security dialog appears.

  2. Deselect Secure Database.

  1. Click OK.

Again, you will receive the message box stating that Security has been changed; you must close (save changes) and re-open the database for this change (i.e., dis-enabling Security) to take effect.

The next time you open the database, there will be no log-on requirement (i.e., no User Name and Password requirement) for any User.

Once Security has been disabled, it can be re-instituted by following the steps Securing an existing database; the same Security rules will apply, provided no rules were changed when the database was in its un-secured state.


Please sign in to leave a comment.
Powered by Zendesk