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 integrity and security:

Simple uses include:

1. Make all tables 'read-only', then all updates must happen through procedures. The procedures can make data-based security checks, and can ensure integrity.

2. Make some tables unreadable, then data can only be retrieved via procedures. Once again, data-based security can be achieved.

The way this is implemented it to specify that when a procedure is run by *any* user, the procedure runs with the access rights of another user/group/entity. 

Procedures must also have security associated with them: it is necessary to grant 'execute' access on procedures to the users 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 that is not under active development by others, and since I am looking for a way to contribute to development, I would be interested 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   |/

Fri, 23 Jul 1999 21:10:35 +1000
Philip Warner <pjw@rhyme.com.au>
RFC: Security and Impersonation

Can't we do this already with views?

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 integrity and security:
> 
> Simple uses include:
> 
> 1. Make all tables 'read-only', then all updates must happen through procedures. The procedures can make data-based security checks, and can ensure integrity.
> 
> 2. Make some tables unreadable, then data can only be retrieved via procedures. Once again, data-based security can be achieved.
> 
> The way this is implemented it to specify that when a procedure is run by *any* user, the procedure runs with the access rights of another user/group/entity. 
> 
> Procedures must also have security associated with them: it is necessary to grant 'execute' access on procedures to the users 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 that is not under active development by others, and since I am looking for a way to contribute to development, I would be interested 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 


Fri, 23 Jul 1999 08:54:58 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] RFC: Security and Impersonation
Philip Warner <pjw@rhyme.com.au> writes:
> 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.

We have some of this, I think, from ACLs on tables and views.  But
as far as I know there is not a notion of a "suid view", one with
different privileges from its caller.  It sounds like a good thing
to work on.  Is there any standard in the area?

			regards, tom lane

Fri, 23 Jul 1999 10:51:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] RFC: Security and Impersonation 
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 maintain and 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 function a 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 the document.

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 not exists(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 returns a useful error message when it fails. The rules for access can be as complex as you like, and this system does not preclude 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 integrity and security:
>> 
>> Simple uses include:
>> 
>> 1. Make all tables 'read-only', then all updates must happen through procedures. The procedures can make data-based security checks, and can ensure integrity.
>> 
>> 2. Make some tables unreadable, then data can only be retrieved via procedures. Once again, data-based security can be achieved.
>> 
>> The way this is implemented it to specify that when a procedure is run by *any* user, the procedure runs with the access rights of another user/group/entity. 
>> 
>> Procedures must also have security associated with them: it is necessary to grant 'execute' access on procedures to the users 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 that is not under active development by others, and since I am looking for a way to contribute to development, I would be interested 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   |/

Sat, 24 Jul 1999 22:40:27 +1000
Philip Warner <pjw@rhyme.com.au>
Re: [HACKERS] RFC: Security and Impersonation
At 10:51 23/07/99 -0400, you wrote:
>
>We have some of this, I think, from ACLs on tables and views.  But
>as far as I know there is not a notion of a "suid view", one with
>different privileges from its caller.  It sounds like a good thing
>to work on.  Is there any standard in the area?
>

I don't know - I'll look into it. The only system I know that implements
this is Dec Rdb, and according to the manuals, is not part of standard SQL.
The way they do it is to define 'modules' with more than one procedure, and
all procedures in the module can have an 'Authorization ID' set, which
means that when the module is run, the access levels of that ID are used.
Moreover, CURRENT_USER returns the Auth. ID, not the actual user, and they
define SESSION_USER which returns the actual user.

My preference is for CURRENT_USER to *always* return the current user, and
to define another name (AUTHORIZATION_USER?) to return the dominant Auth ID.

I'll look through the SQL3 stuff, and see what I can find.


----------------------------------------------------------------
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   |/

Sat, 24 Jul 1999 22:46:33 +1000
Philip Warner <pjw@rhyme.com.au>
Re: [HACKERS] RFC: Security and Impersonation 