Re: [HACKERS] RFC: Security and Impersonation - Mailing list pgsql-hackers

At 08:54 23/07/99 -0300, The Hermit Hacker wrote:
>
>Can't we do this already with views?
>

Not really. A combination of Views, Triggers and Rules will almost do it, but at the expense of being harder to
maintainand more difficult to understand. It may be worth giving a real-world example:
 

Create Table Access_Codes(ACCESS_CODE Char(4), DESCRIPTION Varchar(62));
Insert into ACCESS_CODES Values('SUPR','User may perform any action');
...+various others

Create Table USER_ACCESS(USER_ID Int4, ACCESS_CODE Char(4));

Create Table USERS(USER_ID Int4, USERNAME Varchar(30));

Create Table GROUPS(GROUP_ID Int4, GROUP_NAME Varchar(30));

Create Table USER_GROUPS(GROUP_ID Int4, USER_ID Int4);
Insert Into...etc

The idea is to have 'ACCESS_CODES' function like priviledges - possibly overriding group membership, and have groups
functiona lot like unix groups.
 

Next define the things you want to control (in my case documents stored as blobs):

Create Table DOCUMENTS(DOCUMENT_ID Int4, DOCUMENT_SOURCE <Blob>, ....) etc.

Create Table DOCUMENT_GROUPS(DOCUMENT_ID Int4, GROUP_ID Int4);

The idea is that documents can be members of groups, and that a user must be a member of a group before they can change
thedocument.
 

Next write the 'update' procedure:

CREATE FUNCTION Update_Document (int4,...<args>...)       RETURNS Varchar(255) AS '
Declare   DocID    Alias for $1;   UserID   int4;   Msg    Varchar(255);   isOK    int4;
...declare some other stuff..
Begin   Set :isOK = 1;   Set Msg = 'OK';   Set UserID = (Select USER_ID From USERS Where USERNAME = CURRENT_USER;   If
notexists(Select * From USER_GROUPS UG, DOCUMENT_GROUPS DG Where                   UG.USER_ID = UserID        And
DG.GROUP_ID= UG.GROUP_ID                   And DG.DOCUMENT_ID = DocID) Then
 
       If Not Exists(Select * From USER_ACCESS Where USER_ID = UserID                      and ACCESS_CODE = 'SUPR')
   Then           Set :isOK = False;           Set :Msg = 'User has no access to document';       End If;   End If;
 
   If isOK == 1 Then       <Do The Update>;   End If;
   Return Msg;

End;

And finally, set the table protections:

Revoke All On Table <All> from <All>;
Grant All On Table <All> To SPECIAL_USER;

Grant Execute on Function UPDATE_DOCUMENT To Public;

Set Authorization On Function UPDATE_DOCUMENT To SPECIAL_USER;
^
|
+-- This is the important bit.


What we now have is a table that can only be updated according to a set of rules contained in one procedure, and which
returnsa useful error message when it fails. The rules for access can be as complex as you like, and this system does
notpreclude the use of triggers to enforce both integrity and further security.
 
The same could probably be achieved using rules and triggers for updates, but would not return a nice message on
failure,and would, IMO, be less 'clear'.
 

Sorry for the length of the example, but I hope it puts things a little more clearly.

>On Fri, 23 Jul 1999, Philip Warner wrote:
>
>> A very useful feature in some database systems is the ability to restrict who can run certain external or stored
procedures,and to grant extra access rights to users when they do run those procedures.
 
>> 
>> The usefulness of this may not be imediately obvious, but it is a very powerful feature, especially for preserving
integrityand security:
 
>> 
>> Simple uses include:
>> 
>> 1. Make all tables 'read-only', then all updates must happen through procedures. The procedures can make data-based
securitychecks, and can ensure integrity.
 
>> 
>> 2. Make some tables unreadable, then data can only be retrieved via procedures. Once again, data-based security can
beachieved.
 
>> 
>> The way this is implemented it to specify that when a procedure is run by *any* user, the procedure runs with the
accessrights of another user/group/entity. 
 
>> 
>> Procedures must also have security associated with them: it is necessary to grant 'execute' access on procedures to
theusers who need to execute them.
 
>> 
>> Since this *seems* like it is not likely to get too far into the internals of the optimizer, and seems to be an area
thatis not under active development by others, and since I am looking for a way to contribute to development, I would
beinterested in comments that:
 
>> 
>> 1. Tell me if this is much bigger than I think it is.
>> 2. Tell me if it sounds useful.
>> 3. Is a good learning excercise.
>> 4. If it is stepping on other people's toes.
>> 5. How to do it 8-}
>> 
>> I look forward to comments and suggestions...I think.
>> 
>> 
>> 
>> ----------------------------------------------------------------
>> Philip Warner                    |     __---_____
>> Albatross Consulting Pty. Ltd.   |----/       -  \
>> (A.C.N. 008 659 498)             |          /(@)   ______---_
>> Tel: +61-03-5367 7422            |                 _________  \
>> Fax: +61-03-5367 7430            |                 ___________ |
>> Http://www.rhyme.com.au          |                /           \|
>>                                  |    --________--
>> PGP key available upon request,  |  /
>> and from pgp5.ai.mit.edu:11371   |/
>> 
>
>Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
>Systems Administrator @ hub.org 
>primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 
>
>
----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: +61-03-5367 7422            |                 _________  \
Fax: +61-03-5367 7430            |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/