schema_0_9_10_grants
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
schema_0_9_10_grants [2008/11/08 16:26] – created daniel | schema_0_9_10_grants [2008/11/08 18:02] (current) – daniel | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== Schema access rights ====== | ====== Schema access rights ====== | ||
- | The policy for dealing with access | + | Access |
This page gives some basic guidelines on how to manage a database with multiple users in a typical scenario: | This page gives some basic guidelines on how to manage a database with multiple users in a typical scenario: | ||
* One dedicated database user is the owner of the database and all its objects. This is the user that runs the initial creation script. | * One dedicated database user is the owner of the database and all its objects. This is the user that runs the initial creation script. | ||
- | * One or several groups | + | * One or several |
- | * GRANTs | + | * Access rights |
- | * Per-user account are created with CREATE USER commands | + | * Per-user account are created with //CREATE USER// commands. |
- | * user logins are inserted into the '' | + | * user logins are inserted into the '' |
- | * Users are assigned to groups with //ALTER GROUP ADD user// commands | + | * Users are assigned to groups with //GRANT role TO user// commands. |
+ | ==== Script for tables and sequences ==== | ||
+ | Here is a skeleton of an SQL script that grants all rights to the '' | ||
+ | <code sql> | ||
+ | -- Tables | ||
+ | GRANT SELECT, | ||
+ | | ||
+ | | ||
+ | | ||
+ | body, | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | mail, | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | tags, | ||
+ | | ||
+ | | ||
+ | | ||
+ | words | ||
+ | TO mailusers; | ||
+ | |||
+ | -- Sequences | ||
+ | GRANT SELECT, | ||
+ | | ||
+ | TO mailusers; | ||
+ | |||
+ | </ | ||
+ | |||
+ | ==== Functions ==== | ||
+ | Special care must be taken with functions since the execute privilege is granted to PUBLIC (every user) by default. In order to restrict the rights to use functions, the administrator should first revoke that privilege: | ||
+ | <code sql> | ||
+ | REVOKE EXECUTE ON FUNCTION | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | FROM public; | ||
+ | </ | ||
+ | |||
+ | It is especially interesting to remove the right to call delete_msg() on all or specific users to prevent accidental deletion of messages when the local policy it to never delete any mail. | ||
schema_0_9_10_grants.txt · Last modified: 2008/11/08 18:02 by daniel