Database creation scripts

For production systems, it is usually a good idea to always include GRANT and REVOKE statements in the database creation script so that you can be sure that only the right set of users has access to the table. If this is done manually, it is easy to forget. Also, in this way, we are sure that the same roles are used in development and testing environments so that there are no surprises at deployment time.

The following is a sample extract from the database creation script:

CREATE TABLE table1( 
... 
); 
GRANT SELECT ON table1 TO webreaders; 
GRANT SELECT, INSERT, UPDATE, DELETE ON table1 TO editors; 
GRANT ALL ON table1 TO admins;