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

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

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

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

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

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'll look through the SQL3 stuff, and see what I can find.

I've now done this,and it's in the SQL3 standard. It is implemented via
Modules. The idea being that all routines (procedures and functions) apear
in a module, and that the module can have a 'Module Authorization
Identifier'. The syntax is:

Create Module MY_MODULE Language SQL
Authorization SOME_ID

Procedure Some_Procedure....

...etc

End Module;

If the auth. ID is specified, then (quoting from the standard p. 95):

"... that <module authorization
identifier> is used as the current <authorization identifier> for
the execution of all <routine>s in the <module>. If the <module
authorization identifier> is not specified, then the SQL-session
<authorization identifier> is used as the current <authorization
identifier> for the execution of each <routine> in the <module>.

Let me know if you want to know more. The relevant standard can be found at:

ftp://gatekeeper.dec.com/pub/standards/sql/sql-foundation-aug94.txt

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

Philip Warner <pjw@rhyme.com.au> writes:

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

I've now done this,and it's in the SQL3 standard. It is implemented via
Modules. The idea being that all routines (procedures and functions) apear
in a module, and that the module can have a 'Module Authorization
Identifier'.

Cool. I doubt anyone will object to adding this SQL3 feature to
Postgres, if you feel like working on it.

regards, tom lane