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.
Row security works a little differently than module and table security. It behaves more like column security in that:
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.
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);