Database Administration

Manage Users, Groups, and Roles

The can have one or more databases, each of which can run multiple applications. Administrators have the ability to define and manage users in to meet the needs of a multi-tenant environment. Through user, group, and role management capabilities in Ocient, administrators can make sure that users only have privileges to the objects and databases they need.

Users

Users in Ocient are represented by a user name and password. They can have privileges to the system or one or more databases within the system. Privileges of a user can be granted directly to the user or inherited through groups or roles. Users can also belong to zero or many groups and can have zero or many pre-defined roles in the system. A user identifier might be used by someone logging directly into Ocient through the Ocient command-line interface (CLI) or by an application connecting to a database.

Fully Qualified User Names

A Fully Qualified User Name (FQUN) is the combination of a username and the database on which the user was created. For example: jsmith@metrics. It is not mandatory for users to use an FQUN to connect to their own database. 

An FQUN uniquely identifies a user and follows the pattern "<user_name>@<database_name>", where <user_name> is the username and <database_name> is the name of the database. Use an FQUN to reference a user associated with a database other than the one you are connected to.

user id

user name

database name

fully qualified user name (FQUN)

000

alice

db1

alice@db1

001

alice

db2

alice@db2

002

jimmy

system

jimmy@system



If you do not specify an FQUN when you reference a user object, Ocient assumes the user is associated with the database of the active connection.

This example shows the intended use of the FQUN and how the database is implicitly selected when you connect to the database.

Given an Ocient System with two databases, operations and metrics, create two administrator users and give administrator rights to each on their respective database.

FQUN

Database

Password

admin@operations

operations

password1

admin@metrics

metrics

password2

These are two separate users. Those users can set up their database for the remainder of their users, including:

  • Create users with various roles and privileges.
  • Set up SSO controls for each database.
  • Create tables.
  • Create pipelines to load data.
  • Create service classes.

It is not mandatory for users to use an FQUN to connect to their database. The first user can connect to the operations database as the user admin with password1. The system treats this login as the user admin@operations.

Similarly, the second user can connect to the metrics database as the user admin with password2.

In this example, neither user has to use their FQUN, because they only work with the database described by their FQUN.

When users have permission to connect to more than one database, they are required to use their FQUN for any database that does not match their FQUN. For example, if the user jsmith@metrics also has access to the auctions database, then the user can connect with their FQUN jsmith@metrics.

Groups

A group is a collection of users that has a set of privileges assigned to it. Administrators can create groups that represent an application or a specific job role for users within the database. For example, a group named US_Analysts might only have privileges that allow access to data from the US. Groups belong to individual databases. In other words, you cannot define a group with privileges that span multiple databases.

A service class defines a set of limits on groups. The database applies service classes on a per-group basis. By default, all groups are in the DEFAULT service class that has no limits. For details, see Workload Management and Service Classes.

Roles

Similar to groups, users can inherit privileges by being granted one of the pre-defined roles in Ocient. The system pre-defines the names of and privileges assigned to roles in Ocient. Roles can apply to the Ocient System or one of the user-defined databases.

System Roles

  • Security Administrator: Can read, create, and modify all users or groups.
  • System Administrator: Can read, create, and modify system objects such as tables, clusters, databases, etc. Can also kill running queries system-wide.
  • System Analyst: Read-only access to the entire system.

You can grant system roles to individual users whether they are members of a specific User Database or defined on system.

Database Roles

  • DB Public: Can grant and distribute privileges using this PUBLIC role. Every user created in the database has this role.
  • Database Administrator: Can read, create, and modify database objects such as tables, clusters, databases, etc. Can also create users for the database and kill running queries.
  • Database Analyst: Read-only rights to database objects and data within the database.

These roles are named with the convention "<database_name> <role_name>", where <database_name> is the name of the database and <role_name> is the name of the role. The sys.roles system catalog table displays this information.

To learn about the syntax for granting privileges, see Data Control Language (DCL) Command Reference.

Use DCL to Assign User Privileges

To assign or revoke privileges to users, administrators can use Data Control Language (DCL) statements. You can execute DCL statements like any other SQL statements against the Ocient System and databases. These statements allow for quick and easy modification of user access. For details, see the Data Control Language (DCL) Command Reference.

System Database

Every Ocient System includes an administration interface named system.

The Ocient System lists system as a database, but it is not actually an active database. system serves as a connection point for administration, particularly during the initial system setup to configure other databases, storage, user access, and other configurations.

When connected to system, users can see metadata for all User Databases to which they have rights. Only users who have System Administrator or System Analyst roles can see metadata for Nodes, Node Service Roles, storage spaces, and storage clusters.

A user can log into system if any of these conditions are true:

  • You create the user on that database — e.g., johndoe@system.
  • You assign the user the role of either System Administrator or System Analyst.
  • The non-system user (e.g., myuser@some_database) has been granted SYSTEM privileges through DCL, e.g., GRANT USE on SYSTEM to USER some_user@some_database;

User Databases

User Databases are defined by users. These databases can house tables, views, and data. You can create ans scope users to a database to allow multi-tenancy on an Ocient System or limit the ability of users to see the data or schemas defined in other User Databases.

When a user is connected to a user database, they can query the user data in this database and see the metadata for this database in system tables.

 Visibility of Catalog Components Based on User Privileges

All system catalog tables (e.g., sys.tables, sys.users, sys.databases) are queryable to all users of Ocient regardless of their connection. The contents of these tables can change based on the rights that the individual user has. For example, if a user is blocked from a given database, then a query on the system catalog by that user would not show that database or any of its objects.

Related Links

SQL Reference