Documentation

Row Security

Row security allows you to limit which rows in a table a particular group can see.

All row security is based one way or another on a column in a table that holds a user_id. Andromeda supports two different methods for enforcing row security, but both of them depend on saving user_id values somewhere.

The syntax is described here:

# Assume a group of super-users
group admins:
    description: Super users
    permsel: Y
    permins: Y
    permupd: Y
    permdel: Y 

table example:
    module: whatever
    uisequence: 500
    description: Example Table

    
    column user_id:
        group admins:
            permrow: N

The table "example" above contains a user_id column, which we assume is being populated by the application. This column is used to filter who can see which rows in the table. A user can only see the rows that match her user_id.

The permission "permrow: N" says this group is not restricted by row-level security. The presence of this assignment means all other groups are restricted by row-level security that filters on the "user_id" column.

Understanding Row Security

Row security works a little differently than module and table security. It behaves more like column security in that:

• All tables by default have no row security, all users can see all rows (assuming they can see the table).
• If even one row security assignment is made, it is activated for all groups, all groups can see only those rows that match the user's user_id.
• Therefore, row security assignments are usually made by saying who is not restricted. You specify which admin groups are not restricted, and all other groups are.

Using A Lookup Table

Sometimes basic row security can be too restrictive, because it works by letting a user see his own rows, but nobody can see anybody else's rows. Sometimes you want groups of users to share rows.

This can be done by using a second table that lists users and any other value that might be used to group them together.

Consider for example a website that hosts data for different companies. Each company might have more than one user on the system, and all of these users should see all of the company's data, but they should not see data from any other company. We can make this work by setting up a cross-referene of users to tables, and using that table to establish row-level security on other tables.

# Assume a table of companies
table companies:
    # ....details....
    
    column company:
        primary_key:
        
        
# Now create a cross-reference of companies
# to Andromeda's built-in table of users:
table usersxcompanies:
    module: system
    
    foreign_key companies:
        primary_key: Y
    foreign_key users:
    
    # Even though the "foreign_key" creates the
    # user_id column for us, we need to specify it
    # so we can override some properties
    column user_id:
        primary_key: Y
        
        # Activate row security by giving a super-user
        # group free reign on the table, everybody else
        # will get filtered
        group admins:
            permrow: N

So far so good, now we create a table of documents that can only be seen by users who are listed for the company that owns the document:

table documents:
    module: docstorage
    
    # Define a foreign key into companies
    foreign_key companies:
    
    # The "foreign_key" creates the company column
    # for us for free, but we will specify it so
    # we can override some properties:
    column company:
        group customers:
            table_id_row: usersxcompanies

This example says that for members of the 'customers' group, they will be able to see any row whose value of 'company' matches a row they can see in the usersxcompanies cross-reference.

Implications For Web Programming

Because PostgreSQL does not directly support row security, Andromeda accomplishes it by denying all access to the base table and creating different views that reflect the various combinations of permissions that might exist for different groups.

Because of this, if you code manual queries like "Select mysalary from example" the queries will break because all access to the table is denied.

But Andromeda can tell you at run-time which view is appropriate for the currently logged in user. When coding manual queries, it is a good idea to always use the ddView() function to obtain the name of the view:

$view    = ddView('example');
$sql     = "Select * from $view";
$allrows = Sql_allRows($sql);