Assign Database Access Levels

Privileges can be granted directly to a user or by using roles. We have [already seen] the two predefined roles System.ADMINISTRATOR and System.DBA.

Let’s compare the two options.

Assigning Access Levels Directly to Users

Granting roles directly to a user is simplest, but for two users to have the same privileges, you must use GRANT twice, once for each. For example:

CREATE USER coach_user PASSWORD 'CoAc4u$er';
CREATE USER player_user PASSWORD '!P1ay3ru$er';
CREATE USER fan_user PASSWORD 'P@s$w0rd1';

GRANT INSERT, UPDATE, DELETE, SELECT ON players TO coach_user;
GRANT SELECT,INSERT, UPDATE ON scoring TO player_user;
GRANT SELECT ON teams TO fan_user;

Figure 1. Schema access levels by user

Assign Access Levels to Users by Assigning Roles

Alternatively, roles allow the same privileges to be more easily given to multiple users.

First, create the roles and then grant the appropriate privileges:

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

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;

Roles are associated with a schema. In the examples above, they all belong to the hockey schema.

Now that database roles have been created, a Database Administrator can create database users and grant them access to the database. Each user can be assigned to one or more existing roles. The user will then inherit the access privileges granted to that role.

Continuing the example:

CREATE USER coach_user PASSWORD 'CoAc4u$er';
GRANT coach_role TO coach_user;

CREATE USER player_user PASSWORD '!P1ay3ru$er';
GRANT player_role TO player_user;
GRANT player_role TO coach_user;

CREATE USER fan_user PASSWORD 'P@s$w0rd1';
GRANT fan_role TO fan_user;
GRANT fan_role TO coach_user;

Figure 2. Schema access levels by role

Changing Passwords

NuoDB does not enforce changing passwords. However, users should change their passwords periodically. Use the ALTER USER command to change a user password.

ALTER USER fan_user PASSWORD 'passwd2';

LDAP supports password expiry - refer to LDAP Integration.