Sybernet Projector

Introduction

While working on a project for SRI Consulting, I needed the ability to implement row-level security for all of my procedures. The procedures were getting numerous (okay, 4 actually) and I didn't want to have to code each of them separately. Turns out a view would accomplish everything I desired. All I needed to do was create that view, change the procedures to reference this view, and write another procedure for administering the permissions.

Consider the following table:

    create table PROJ
    (   PROJ_ID            varchar(12)
    ,   PROJ_NAME          varvhar(30)
    ,   ORG_ID             varchar(12)
    )

A simiplified version of our stored procedure would resemble this:

    create procedure show_projects
    as
        select * from PROJ
        return

What I want is a view that would only select those rows from the PROJ table based on my user_name and containing only those ORG_ID's I am allowed to see. The view for my username might do something like this:

    create view my_view
    as
        select *
        from PROJ
        where ORG_ID in ("1.023","1.024")

Obviously, something more flexible that would work with any username would be required. If such a view existed, all I would have to do is replace all references to PROJ by that view's name. The change is trivial:

    create procedure show_projects
    as
        select * from my_view
        return

Sybernet Projector

Everything described above can now be done with the click of a mouse. Sybernet Projector is a stored procedure that lets you perform the following tasks:

Define the tables and columns you wish to track user permissions.
Create the appropriate views.
Modify your stored procedure to use these views.
Administer user permissions on the columns you defined.

The procedure can handle any datatype (char, numeric) although some datatypes don't make sense (like text and image data). It will work on either a case-sensitive or case-insensitive server, and can pretty much handle any number of non-unique values subject to the memory available to your browser; for example, selecting an employee's name (or worse, their employee number) is probably not a good idea, but the procedure is able to cope with a very large number of non-unique values. My original design would linear search the HTML form elements with JavaScript. This was almost unuseable on large sets of data. Now JavaScript does a binary search and the results are now quite acceptable.

Installing Projector In Your Database

Before you can begin using Projector, a private copy of the procedure, a table, and an index on that table is required. Under Sybernet Utilties is a link to Sybernet Projector. This is actually the installation program. Although safe to use even after Projector has been installed, you should actually register your personal copy of Projector with the Sybernet Stored Procedure Register. The installation program performs the following steps:

Creates the profile table (dataprotects) in your database.
Creates an index.
Copies and compiles the stored procedure sp_html_projector.

As I said, it is safe to call the installation program. In fact, it is a good idea to invoke it periodically for changes I may have made. You won't lose any data in your profile and the procedure is recompiled only if the latest version is newer than the one you have.

Getting Started

The main Projector screen contains four links for managing your data. The first link called Configure Profile is where you want to begin adding table and column names to your profile. You'll need to start here, but you can always add and drop columns in the future.

Since you can create any number of views on the same table, creating views is done with a separate link called, Create Views. You simply select the appropriate table and columns, give the view a name, and click a button to automatically generate your view.

The third link is called Modify Procedures, which can be used to automatically replace table names with view names. You simply select which table, procedure and view, and click the Modify button.

The last link on the main screen is called User Administration. This is where you determine which users can see which rows in your tables. Once the view is created, you can add or drop permissions at anytime. You do not need to recreate the view unless you add or drop columns from the profile.

The Profile

The profile is a simple table with 4 columns. Here is the definition of that table:

    create table dataprotects
    (   uid                int              not null -- user_id.
    ,   id                 int              not null -- object_id.
    ,   colid              int              not null -- column_id.
    ,   value              varchar(30)      not null -- "1.023"
    )

The limit of 30 characters should be adequate for most applications. A larger value would require modification of the stored procedure, but also probably means the number of possible values in your table are too great for efficently using this mechanism. Values in your tables which exceed this length are automatically truncated.

Configure Profile

To add tables and columns to your profile, click the link for Configure Profile. The left margin will display a list of tables from the current database. Simply click the checkbox next to the table name you wish to modify.

Checked checkboxes means this table already exists in your profile. You would still click this to add or delete columns.

Once you have selected a table name, the left margin displays a list of all columns in that table. Again, simply select the columns you wish to modify.

Checked checkboxes means this column already exists in your profile. Unchecking a checkbox causes the column and all data to be removed from the profile, and checking a checkbox causes that column to be added to the profile. In the former case, an alert is displayed confirming that you actually want to delete this column.

Create Views

Select the Create Views link to create your views. This link causes the left margin to display the name of every table that has been defined in your profile. Select the table you wish to create a view for by clicking the checkbox.

Once you have selected a table name, the left margin displays a list of all columns that were specified when you defined your profile. Check all column names that you want to use to create your view.

Following the column names are one or more radio buttons. The first radio button says to prompt you for the view name. The remaining radio buttons (if any) correspond to views you have already created. You can select one of those to recreate a view.

Click the button and your view is created.

You don't need to worry that you might accidently drop an object. The procedure won't let you drop any object that is not a view, nor any views that were not created by this procedure.

Modify Procedures

In reality, you will probably never use this option of Projector because you should really be modifying the original source and not the one stored in Sybase; however, the facility is here if you need it.

Clicking this link displays a list of all tables that have been defined in your profile. Selet the table name you are interested in.

One you have selected a table name, the left margin displays a list of only those procedures and views which reference this table. Simply select the procedure you are interested in, the view you wish to use, and click the Modify button to update your procedure.

If for some reason the procedure fails to compile, a JavaScript alert will be displayed. You should pay close attention to this alert because it means your procedure is now gone. It's time to panic!

Administer Users

This link allows you to manage the permissions for each username in your database based on the table and column names you specified in your profile.

When you click this link, the left margin is replaced by a list of all usernames in your database and the right margin is replaced by a list of all possible values from all tables and columns that you defined in your profile.

You must select a username from the left margin before you can begin defining permissions. The checkbox next to each username will be checked if the username you selected has the exact identical permission. You can also click the checkbox next to any username to copy the same permissions from the username you previously selected.

Once you have selected a username, simply check or uncheck the appropriate values from each column and table. Use the "Grant All" button if they can view all values. Use the "Revoke All" button to revoke all permissions on a particular column.

Note: The "Grant All" button only grants permissions to those values that are currently in your database. If new values are added, permission is not granted to that row.

A Quick Tutorial

I have a table called PROJ and a stored procedure which references this table called sp_html_psr. I want to perform row-level security based on the value of ORG_ID in this table. Since I am the dbo, I need to see all rows in the table.

Here are the steps:

Select the link for Configure Profile.
- Check the checkbox next to PROJ.
- Check the checkbox next to ORG_ID.
Select the link for Create Views.
- Check the checkbox next to PROJ.
- Make sure the checkbox next to ORG_ID is already checked.
- Click the radio button next to Prompt For Name.
- At the prompt, enter DELTEK.PROJECTS.
Select the link for Modify Procedures.
- Check the checkbox next to PROJ.
- Click the radio button next to sp_html_psr.
- Click the radio button next to PROJECTS.
- Click the Modify button.
Select the link for Administer Users.
- Wait while both frames load.
- Check the checkbox next to dbo.
- Click the "Grant All" button.

You're done!

Known Bugs

The frames for Administer Users do not load in the correct order. If you select a username before the East frame finishes loading, a nasty JavaScript error will occur.

Work around: Wait for both frames to load before selecting a username.

Using the browser's Go menu item or Back button to return to Projector will sometimes cause a JavaScript error.

Work around: Click the Reload button.