A mechanism securing web applications in DBMS - Mailing list pgsql-hackers

Hi all,

I am a graduate student from UC San Diego. My adviser, Dr. Kirill
Levchenko, and I have been working on a web/DB security project for
the last few months. Since fine-grained access control in DBMS is part
of our project and the PostgreSQL community is also working on it now,
we would like to exchange some ideas on the issue with you.

1. Background
Nowadays people entrust more and more sensitive data to web
applications, while security vulnerabilities are common in these
applications. The normal structure of web applications consists of
three tiers: client-side code, server-side code and a database. In
server-side code a database user representing all the
application-level users interacts with the database with full
privileges. Since currently database built-in access control is too
coarse, no access control mechanism is used in most of web
applications. This situation is not ideal since a malicious
application user can tamper with other users’ data by exploiting
vulnerabilities in the application, and if the application is
completely under the malicious user’s control so is the database.

2. Our Mechanism.
In order to fix the problem, we believe there are two underlying
problems to be addressed: authenticating application-level users to
DBMS and fine-grained access control in the DBMS. Note that these two
problems are related. Without authenticating application-level users
to the DBMS, can the DBMS not know which application-level user it is
actually interacting with, thus the DBMS cannot apply fine-grained
access control policy correctly.

2.1 The authentication problem
We address the authentication problem by requiring developers to
define an authentication function in the DBMS. This function is
invoked whenever an application-level user logs in. An authentication
function contains the authentication logic in the server-side code.
Besides, it also caches some attributes of the current
application-level user, which will be checked by the fine-grained
access control mechanism.

In our implementation, we added a new type of function called
authentication function. Developers are supposed to use CREATE
AUTHENTICATION FUNCTION command to define authentication function,
which contains the application authentication logic and must be
defined to return table rows. When the authentication function is
invoked, the return values are cached in a temp table, called the
authentication table,  with the same name as the authentication
function. The authentication table is available to access control
statements (GRANT-WHERE statements).

2.2 The fined-grained access control problem

We address the fined-grained access control problem by using
GRANT-WHERE statements from [1]. Concretely, the syntax of GRANT-WHERE
statements is
     GRANT <privilege> [, ...] ON <relname> TO <user> [, ...]     USING <referenced relname> [, ...]     WHERE
<predicate>;

where <privilege> can be SELECT, INSERT, UPDATE or DELETE, the USING
clause lists the tables of which should be available to the predicate
and the WHERE clause specifies the predicate.

3. Integrate our mechanism to PostgreSQL.

3.1 Authentication Function

The major challenge of this part is how to cache users’ authentication
information. In our prototype implementation, an authentication table
is actually a temporary table. But it makes our GRANT-WHERE statements
also temporary since they refer to the authentication table. Since
every session has its own authentication table and all the
authentication tables share the same schema, the global temporary
table [2] would be a perfect fit for caching  users’ authentication
information. Also, it will not make GRANT-WHERE statements temporary
anymore.

3.2 Fined-Grained Access Control

We noticed that the PostgreSQL community is currently working on
fine-grained access control. Access control policies can be defined in
form of “ALTER TABLE … SET ROW SECURITY” statement. Although
functionally “ALTER TABLE … SET ROW SECURITY” statements and
GRANT-WHERE statements are equivalent, however, there are some
advantages in the latter one:

1) GRANT-WHERE statement is very similar to the SQL GRANT statement,
which should be already familiar to most of the developers, thus the
developers may feel more comfortable to use it.

2) In GRANT-WHERE syntax, a DB user can be specified as the recipient
of the GRANT. In the current proposed row security syntax, one needs
to write a big statement containing all the predicates for all the
possible recipients per table per operation.

For example, suppose we have the following two GRANT-WHERE statements on table1

GRANT UPDATE ON table1 TO Alice
WHERE p1;

GRANT UPDATE ON table1 TO Bob
WHERE p2;

The equivalent statement in the “ALTER TABLE … SET ROW SECURITY” syntax is

ALTER TABLE table1 SET ROW SECURITY FOR UPDATE

TO WHERE (current_user = ‘Alice’ AND p1)
       OR (current_user = ‘Bob’ AND p2);.

As you can see, this syntax requires us to combine two logically
separate statement into one statement. Also, it can be hard for
developers to debug.

4. Collaboration.

We are looking forward to joining forces with PostgreSQL community on
this problem. More specifically, we have the following suggestions
which we hope the community would consider:

1)  Using GRANT-WHERE syntax for the row-level access control. If for
any reasons it is impossible, we hope at least a clause will be added
to the “ALTER TABLE … SET ROW SECURITY” syntax such that a recipient
can be specified;

2) Restarting Global Temporary Table project;

3) Accepting “CREATE AUTHENTICATION FUNCTION” as a variant of “CREATE
FUNCTION” command.

We’d like to discuss the mechanisms above with the community further,
and if any of the mechanisms are accepted by the community, we also
would like to take part in implementing it. Please feel free to
contact us if you have any questions.


Zhaomo Yang      zhy001@ucsd.edu
Kirill Levchenko   klevchen@ucsd.edu


References:
[1] S. Chaudhuri, T. Dutta, and S. Sudarashan. Fine Grained
Authorization Through Predicated Grants. In Proceedings of the 23rd
IEEE International Conference on Data Engineering (ICDE), pages
1174–1183, April 2007.

[2] idea: global temp tables
http://www.postgresql.org/message-id/162867790904271344s1ec96d90j6cde295fdcc7806f@mail.gmail.com