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 . 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  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 zhy...@ucsd.edu Kirill Levchenko klevc...@ucsd.edu References:  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.  idea: global temp tables http://www.postgresql.org/message-id/162867790904271344s1ec96d90j6cde295fdcc78...@mail.gmail.com -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers