Row-Level Security

Basic table level security is good enough in most situations that do not involve public access to the database. However, if you are running a public site where people can create accounts and save information, it is absolutely necessary to have row-level security.

There are two flavors of row-level security. In the first flavor, a USER_ID column must be placed into a table, and then you specify that a user can only see rows that match their USER_ID. Here is an example:

table orders:
    #  ...miscellaneous settings...
    
    # put in the user_id, and have it always be the person
    # who first saved the order
    column user_id:
        automation_id: uid_ins
        group $LOGIN:
            permrow: Y
        group admins:
            permrow: N

The above example says the "$LOGIN" group will have row-level security on this table. They will only be able to see the rows where the USER_ID column is them. The "$LOGIN" group is a special group that exists in all Andromeda applications. It is the lowest-privilege group, the group whose only certain privilege is the ability to connect to the database. In many public or eCommerce sites this group must be given the ability to write to tables such as the ORDERS table, which is why the row-level security is so important.

A More Flexible Row-Level Security

The above example can become rigid in some circumstances. Consider a case where a user in a financial institution is limited to seeing information from certain accounts. Other users may also be allowed to see the same accounts, so having only one USER_ID column on the rows is too limiting. In this case we want to look out to some cross-reference table, perhaps something that lists companies and users. A user can see a row if the cross reference lists the company and his user_id. That option looks like this:

First we need the cross reference of companies to users:

table usersxcompanies:
    module: companyinfo
    
    foreign_key users:
        primary_key: “Y”
        uisearch: “Y”
    foreign_key companies:
        primary_key: “Y”
        uisearch: “Y”

Now consider a table of invoices. The table has a foreign key to the companies table. If a user is in the cross-reference for that company, they can see the row. Here is what that looks like:

table invoices:
    module: companyinfo
    
    # The nocolumns flag prevents the column being
    # defined.  We will place the column below so we
    # can set properties.
    foreign_key companies:
        nocolumns: “Y”
                       
    column company:
        group $LOGIN:
            table_id_row: usersxcompanies
        group admins:
            permrow: “N”

In this case we need a foreign key to the companies table. However, we need to set properties of the company column. To do this, we specify the "nocolumns" flag on the foreign_key. This means the columns will not be created because of the foreign_key, we have to put them in ourselves.

For the column, we set the property "table_id_row", to indicate which cross reference table should be used.

We also have a setting that turns off row-level security for members of the "admin" group.