Privileges of the Database Administrator

Initial Setup

When you created a NuoDB database, you specified a database administrator username and password (see Creating a Database).

This database administrator user is the superuser of the database and is granted the ADMINISTRATOR role and DBA role by default.

For a database created by running NuoDB’s database Helm chart, or Docker compose or via NuoDB’s DBaaS (Database as a Service) interface, this superuser’s username is “dba” by default.
If you created the database manually using nuocmd, then you can choose any user name you prefer.

In this documentation the database superuser is often referred to as the DBA User, not to be confused with the System.DBA role.

Default Roles

The ADMINISTRATOR role grants the privilege to:
- View, modify or delete any existing objects.
- Create new schemas.
- Create, alter and drop functions and stored procedures.
- Create and drop users.
- Modify the system property values (using the SET statement).
- It includes all the privileges of the System.DBA role.
The System.DBA role grants the privilege to:
- Create new schemas.
- Create, alter and drop functions and stored procedures.
- Create and drop users.
- View data from any user in System pseudo tables, such as all connections and transactions.
  - To get a list of psedo table run this SQL: select tablename from system.pseudotables.
    
    However:
- It does not grant permission to modify system property values.
- It does not grant permission to access existing objects in existing schemas.
By default a new user has privileges to run a small number of commands such as SHOW and USE.
- However they must be granted permission to access existing database objects (such as tables and views), create new schemas and/or create new database objects in any schema.
- Access to the contents of System tables
- Limited access to System pseudo tables, but restricted to their own data only, such as connections and transactions.
Schema Owner - this is not an explicit role, but whoever creates a schema has full privileges over it:
- To create, modify or delete objects within the schema
- To grant (or revoke) access by other users to the schema and/or objects within it.

Figure 1. Default roles and privileges

Next Step

Given the right privileges, any user can create roles, select from system tables, create database objects and grant privileges on those database objects to other users. However you are limited to your own data in System psuedo tables (as mentioned above) unless you have System.DBA or System.ADMINISTATOR roles.

To create functions and stored procedures, they must have either the System.ADMINISTRATOR or System.DBA role.

Roles and privileges are granted to users using the GRANT statement.

Initially only the database superuser can create users and grant them roles and privileges.

Best practice recommends that a user with the ADMINISTRATOR role should not be used as an application user.
If the database administrator password is lost and there is no other user with the ADMINISTRATOR role privileges or the ability to change the database administrator user’s password, please contact NuoDB Support for a utility to regain the database administrator access to the database.
Recommendation: Give specific privileges to another user to execute the ALTER USER command on the database superuser using GRANT ALTER ON USER <superuser> TO <another user>.