Users and Roles

The DBA user should create one or more users for managing access to the database, depending on the level of database security to be maintained. If the application will have several users connecting to the database, and all users require the same level of access to all database objects, then the DBA has the option of creating one user that all database connections can use. However, if different users require different levels of access to the same database objects, it would be appropriate for the DBA to create a username for each level of access that one or more users can share.

A database role is a convenient way for a DBA to group several privileges that can be granted to multiple database users. Privileges can be granted to or revoked from the role, thereby applying the change in privileges to the whole group of users at one time. The DBA should create a role that represents the group access and then grant that role to each individual user. See CREATE ROLE.

CREATE ROLE hockey.coach_role;
CREATE ROLE hockey.player_role;
CREATE ROLE hockey.fan_role;

The DBA would then grant privileges to or revoke privileges from that role. See GRANT and REVOKE.

GRANT ALL ON hockey.hockey TO ROLE coach_role;
GRANT UPDATE, SELECT ON hockey.players TO ROLE player_role;
GRANT SELECT ON hockey.hockey TO ROLE hockey.fan_role;
COMMIT;