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