Users and Roles
The DBA user (superuser) 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 a Database Administrator 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 a Database Administrator to create a username for each level of access that one or more users can share (known as the Principle of Least Privilege).
CREATE USER coach_user PASSWORD 'CoAc4u$er';
CREATE USER player_user PASSWORD '!P1ay3ru$er';
CREATE USER fan_user PASSWORD 'P@s$w0rd1';A database role is a convenient way for a Database Administrator 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.
A Database Administrator 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;A Database Administrator 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;