ACS Documentation : ACS Core Architecture Guide : General Permissions
Thecreate 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) );
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.
/gp/administer-permissions
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:
on_what_id
and on_which_table
- the identity of the database row whose permissions will be editedobject_name
- the name of the database row whose permissions will be edited; used only for display purposes: in the page title, etc. (We name this variable "object_name" because a database row typically represents an object of some kind, and because "row_name" is not very intuitive.)return_url
- usually the result of a call to ns_conn url
, i.e., the page containing the linkfs_files
table, here is an example of how we could construct the link:
Access to# 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 [db_string unused "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]"
/gp/administer-permissions
will be denied unless the user has "administer" permission on the specified database row.
/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
:
Let's walk through this example step by step: First, we specify that we expect to receive the identity of a row in thead_page_variables { file_id } set user_id [ad_validate_and_get_user_id] ad_require_permission $user_id "read" $file_id "fs_files"
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:
user
-scoped permission)?group_role
-scoped permission)?group
-scoped permission)?registered_users
-scoped permission)?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.
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:
user_has_row_permission_p
grant_permission_to_user
grant_permission_to_role
grant_permission_to_group
grant_permission_to_reg_users
grant_permission_to_all_users
e.g., ns_ora exec_plsql $db "begin :1 := ad_general_permissions.grant_permission_to_users($user_id, 'read', $version_id, 'FS_VERSIONS'); end;"
user_permission_id
role_permission_id
group_permission_id
reg_users_permission_id
all_users_permission_id
All of these functions return zero if the specified permission has not been granted.
revoke_permission
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:
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 ofselect 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'
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.
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.