Access Control

The roles table discussed earlier provides Role-Based Access Control, a typical approach. It would be better, however, if the application were precluded from making access control decisions. Other types of access control are possible (a complex topic). Another approach involves groups and resources, which we discuss now.

For Group-Based Access Control, define groups and resources as follows:

• groups( group, description )
• resources( resource, url, link_text )

The url field is the URL of the protected resource. The link_text field is suitable for constructing hyperlinks (on a portal page, for instance).

The groups table has two columns:

CREATE TABLE groups (
  group       VARCHAR(12)  NOT NULL,
  description VARCHAR(60)  NOT NULL,
  PRIMARY KEY (group) );

The resources table has three columns:

CREATE TABLE resources (
  resource   VARCHAR(16)  NOT NULL,
  url        VARCHAR(80)  NOT NULL,
  link_text  VARCHAR(40)  NOT NULL,
  PRIMARY KEY (resource) );

The group and username fields are the primary keys of the groups and users tables, respectively.

A number of intersect tables are defined:

• group_membership( group, username )
• group_access( group, resource )
• user_access( username, resource )

The group_membership table assigns users to groups. It has two columns:

CREATE TABLE group_membership (
  group      VARCHAR(12)  NOT NULL,
  username   VARCHAR( 8)  NOT NULL,
  PRIMARY KEY (group,username) );

The group_access table attaches groups to resources. It has two columns:

CREATE TABLE group_access (
  group      VARCHAR(12)  NOT NULL,
  resource   VARCHAR(16)  NOT NULL,
  PRIMARY KEY (group,resource) );

The group and resource fields are the the primary keys of the groups and resources tables, respectively.

The user_access table attaches individual users to resources. Like the group_access table, the user_access table has two columns:

CREATE TABLE user_access (
  username   VARCHAR( 8)  NOT NULL,
  resource   VARCHAR(16)  NOT NULL,
  PRIMARY KEY (username,resource) );

Individual user access is primarily for convenience.

What groups does a particular user belong to?

SELECT group FROM group_membership
  WHERE username = 'username';

What groups have access to a particular resource?

SELECT group FROM group_access
  WHERE resource = 'resource';

What users have direct access to a particular resource?

SELECT username FROM user_access
  WHERE resource = 'resource';

Now assume the web service knows the name of the protected resource that requests it. The web service does the following:

1. Validates the session ID
2. Authorizes the corresponding user

The latter depends on whether the user belongs to a privileged group.

What users have group access to a particular resource?

SELECT username FROM group_membership
  WHERE group IN (
    SELECT group FROM group_access
      WHERE resource = 'resource'
  );

Rewriting the subselect as a join:

SELECT username FROM group_membership, group_access
  WHERE group_membership.group = group_access.group
  AND resource = 'resource';

List all users having access to a particular resource:

SELECT username FROM user_access
  WHERE resource = 'resource'
UNION
SELECT username FROM group_membership
  WHERE group IN (
    SELECT group FROM group_access
      WHERE resource = 'resource'
  );

Recall that the default login target is a portal home page. Any authenticated user is allowed access to the portal page. The web service returns a list of URLs to the portal page, which it uses to construct a personalized home page.

What resources does a particular user have direct access to?

SELECT resource FROM user_access
  WHERE username = 'username';

What resources does a particular user have group access to?

SELECT resource FROM group_access
  WHERE group IN (
    SELECT group FROM group_membership
      WHERE username = 'username'
  );

Rewriting the subselect as a join:

SELECT resource FROM group_access, group_membership
  WHERE group_access.group = group_membership.group
  AND username = 'username';

What are the URLs of all the resources a particular user has access to?

SELECT url, link_text FROM resources
  WHERE resource IN (
    SELECT resource FROM user_access
      WHERE username = 'username'
    UNION
    SELECT resource FROM group_access
      WHERE group IN (
        SELECT group FROM group_membership
          WHERE username = 'username'
      )
  );

The URLs and the corresponding text are sufficient to construct a list of links.