Differences

This shows you the differences between two versions of the page.

--- schema_0_9_10_grants [2008/11/08 16:26] – created daniel
+++ schema_0_9_10_grants [2008/11/08 17:23] – daniel
@@ Line 3: / Line 3: @@
 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 or several groups of users are created with CREATE GROUP commands.
+  * One or several roles (=groups) are created with //CREATE ROLE// commands.
-  * GRANTs are affected to each group for each database object (tables, functions, sequences, views)
+  * Access rights are assigned to each role for each database object (tables, functions, sequences, views) with //GRANT SELECT,INSERT,...// commands.
-  * Per-user account are created with CREATE USER commands
+  * Per-user account are created with //CREATE USER// commands.
-  * user logins are inserted into the ''users'' table
+  * user logins are inserted into the ''users'' table. It is not mandatory, but skipping this step will prevent the user interface to trace who does what.
-  * Users are assigned to groups with //ALTER GROUP ADD user// commands
+  * Users are assigned to groups with //GRANT role TO user// commands.
+Here is a skeleton of an SQL script that grants all rights to the ''mailusers'' group. The administrator can use this as a starting point and remove specific access rights to particular groups of users.
+<code sql>
+-- Tables
+GRANT SELECT,INSERT,UPDATE,DELETE ON
+ addresses,
+ attachment_contents,
+ attachments,
+ body,
+ config,
+ files,
+ filter_action,
+ filter_expr,
+ forward_addresses,
+ header,
+ identities,
+ inverted_word_index,
+ jobs_queue,
+ mail,
+ mail_addresses,
+ mail_status,
+ mail_tags,
+ mailboxes,
+ mime_types,
+ non_indexable_words,
+ notes,
+ programs,
+ raw_mail,
+ runtime_info,
+ tags,
+ tags_words,
+ user_queries,
+ users,
+ words
+TO mailusers;
+-- Sequences
+GRANT SELECT,UPDATE ON
+ seq_addr_id, seq_mail_id, seq_tag_id, seq_thread_id,seq_attachment_id
+TO mailusers;
+</code>