General Permissions

by Richard Li, Michael Yoon, Yon Feldman, and Mark Ciccarello

ACS Documentation : ACS Core Architecture Guide : General Permissions

The Big Picture

The General Permissions package lets you control who can do what with each row in your database, by providing: There are five types of party to whom permissions can be granted: In essence, General Permissions treats rows in the database as operating systems like Unix and Microsoft Windows NT treat files in the filesystem.

The Data Model

The General Permissions data model is simple, consisting of one table:

create table general_permissions (
	permission_id		integer not null primary key,
	-- on_what_id is a varchar to accomodate non-integer ID's
	on_what_id		varchar(30) not null,
	on_which_table		varchar(30) not null,
        scope           	varchar(20),
	user_id			references users,
	group_id		references user_groups,
	role			varchar(200),
	permission_type		varchar(20) not null,
	check ((scope = 'user' and user_id is not null
                and group_id is null and role is null) or
	       (scope = 'group_role' and user_id is null
                and group_id is not null and role is not null) or
	       (scope = 'group' and user_id is null
                and group_id is not null and role is null) or
	       (scope in ('registered_users', 'all_users')
                and user_id is null
                and group_id is null and role is null)),
	unique (on_what_id, on_which_table,
                scope, user_id, group_id, role, permission_type)
The on_what_id and on_which_table columns identify the database row in question. The scope, user_id, group_id, and role columns together identify the party to whom the permission is being granted. Finally, the permission_type column contains values like "read", "comment", "write", and "administer" that represent the actions you want to control. As with Unix, permission types are independent of one another. One permission does not imply another (e.g., "write" does not imply "read").

To define rules for who can insert new rows into a table, the convention is to require "write" access on a row in the table's parent table, e.g., in order to add contact information for a given user (insert a row into the users_contact table), you need to have "write" permission on the corresponding row in the users table.

Enabling Users to Grant and Revoke Permissions

The page /gp/administer-permissions (an abstract URL) provides a reusable interface for granting and revoking permissions on an arbitrary row in the database. You simply link to this page from your own pages, making sure to pass along: Consider the File Storage module, which enables users to upload and store files on the server through a web interface. To allow the user to edit the permissions of a row in the File Storage module's fs_files table, here is an example of how we could construct the link:
# assuming that $file_id contains the ID of a row in fs_files...
set on_what_id $file_id
set on_which_table "fs_files"

# use the value of the file_title column as the "object_name"
set object_name [database_to_tcl_string $db "select file_title
from fs_files
where file_id = $file_id"]

set return_url [ns_conn url]

set edit_permissions_link \
     "/gp/administer-permissions?[export_url_vars on_what_id on_which_table object_name return_url]"
Access to /gp/administer-permissions will be denied unless the user has "administer" permission on the specified database row.

How to Enforce Permissions

The page /file-storage/one-file (also part of the File Storage module) displays all versions of a given file. To make sure that we show the page only to users with "read" permission on the identified file, we include a call to ad_require_permission:
ad_page_variables { file_id }

set user_id [ad_validate_and_get_user_id]

set db [ns_db gethandle]

ad_require_permission $db $user_id "read" $file_id "fs_files"
Let's walk through this example step by step: First, we specify that we expect to receive the identity of a row in the fs_files table as a form or query string variable. Next, we check the identity of the user and grab a database handle. Finally, the call to ad_require_permission is self-explanatory: "read" is the type of permission required and the combination of $file_id and fs_files identifies the database row that the user is attempting to access.

Internally, we first check to see if the user is logged-in. If so, then the following questions are asked to determine if the user has been granted the requested type of permission:

If the answer to all five of these questions is "no," then ad_require_permission prohibits further processing of the page by returning a 403 "Forbidden" error.

If the user is not logged-in, then we check to see if the requested permission has been granted to unregistered as well as registered users (i.e., an all_users-scoped permission). If not, then ad_require_permission redirects to the login page.


In addition to ad_require_permission, the Tcl API of General Permissions provides the ad_user_has_row_permission_p predicate.

To access General Permissions data, you should use the ad_general_permissions PL/SQL package, instead of SQL statements. (The Tcl API procs are just thin wrappers on top of the PL/SQL package.) Here is a summary of how to use the various procedures and functions in the package:

Note that the PL/SQL API (and therefore the Tcl API) is case-insensitive.

One instance in which you may need to query the general_permissions table directly is when you are trying to answer a question like "What are the titles of all files in the File Storage system on which I have administrative permission?" One way to write this query is:

select f.file_title
from fs_files f
where ad_general_permissions.user_has_row_permission_p
 (user_id, 'administer', f.file_id, 'fs_files') = 't'
While this query is simple and readable, it has the unfortunate side-effect of causing Oracle (8.1.5 and below) to execute a full table scan of fs_files. (If function-based indexes worked as advertised, then this would not be an issue.) So, if fs_files were to become large, we would want to rewrite this query with a join to general_permissions to keep performance acceptable; see the implementation of user_has_row_permission_p for what criteria that you would need to make this work.

Future Enhancements

A high-priority enhancement of this module is to design and implement a good scheme for default permissions, e.g., how do we know what permissions to grant when a user creates a new file in the File Storage system? The Unix umask concept is a simple model from which we can start.

A possible extension to the data model would be to support dependency rules between permission types, e.g., a way to say that granting "administer" permission implies granting "write" permission, which, in turn, implies granting "read" permission. It would be important to ensure that this extension would be optional, i.e., that it would not interfere with the current model, in which permission types are independent of one another.