Thread: RFC: Security and Impersonation
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 be achieved. 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 the userswho 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 |/
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
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 <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
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 |/
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 |/