User management

The Raijin user management system allows administrators to implement role-based access control (RBAC). See Authentication settings in the reference manual for more information on the supported authentication types.

Creating users

Raijin supports two authentication methods: certificate-based authentication using TLS/SSL certificates and password-based authentication. Raijin users are identified by their username, which is required for all authentication methods.

Authentication methods are mutually exclusive. You can only use one of them at any given time.

Example 1. Creating a user with password-based authentication

In this example, user foo is created with the password 1234_Raijin.

CREATE USER 'foo' IDENTIFIED WITH PASSWORD '1234_Raijin';

Once you create a user, they can log in to the Raijin UI with the username and password you specified.

The password must contain at least one uppercase letter, a lowercase letter, a number, and a symbol.
Example 2. Creating a user with certificate-based authentication

In this example, a user foo is created with an ssl-certificate-common-name-string.

CREATE USER 'foo' IDENTIFIED WITH SSL_CERTIFICATE CN 'ssl-certificate-common-name-string';

To use certificate-based authentication, you must configure TLS/SSL settings in raijin.conf. Uncomment and modify the following parameters according to your environment:

  • CertFile

  • CertKeyFile

  • CAFile

A new user does not have any privileges by default. You can only use such a user to perform actions that do not require any privileges.

Viewing existing users

Sometimes you might need to check information on existing users, such as whether a user already exists or what authentication method is used by a user.

You can view all existing users by using the SHOW USERS command. The command will provide usernames and authentication methods for all users currently in the Raijin Database Engine.

Example 3. Viewing all users
SHOW USERS;
Result
{"name":"foo", "identified_with":"SSL_CERTIFICATE"}
{"name":"bar", "identified_with":"PASSWORD"}

Updating a user

You can update the password of an existing user, for example when a user has been locked out of their account, by using the ALTER USER command.

Example 4. Updating the password of an existing user
ALTER USER 'foo' SET PASSWORD TO '4321_Raijin';

Delete a user

If you no longer need a user or made a configuration mistake and need to recreate the user, you can remove an existing user from the Raijin Database Engine, by using the DROP USER command.

Example 5. Removing a user
DROP USER 'foo';

Granting privileges

Privileges are granted per user using the GRANT command.

Raijin supports the following privileges:

  • ALL PRIVILEGES

  • ALTER

  • CREATE

  • DROP

  • INSERT

  • MAINTENANCE

  • METADATA

  • SELECT

Example 6. Assigning Raijin privileges

This example assigns the user foo the CREATE privilege on a database named db.

GRANT create on db.* TO 'foo';

The following command assigns the user foo the CREATE and SELECT privileges on a database named db.

GRANT create, select ON db.* TO 'foo';

The following command assigns the user foo the CREATE and SELECT privileges on the tbl table in the database named db.

GRANT create, select ON db.tbl TO 'foo';

Privilege mapping

The table below lists SQL statements and the minimum privileges they require.

SQL statement Required minimum privilege

ALTER DATABASE RENAME

ALTER and DROP privileges on the database,
CREATE and INSERT privileges on the table

ALTER TABLE RENAME

ALTER and DROP privileges on the table,
CREATE and INSERT privileges on the table

ALTER TABLE PARTITION BY

ALTER privilege on the table

ALTER TABLE DETACH PARTITION

ALTER privilege on the table

ALTER TABLE DROP PARTITION

ALTER privilege on the table

ALTER VIEW RENAME

ALTER and DROP on the table,
CREATE on the table

ALTER USER

ACCESS MANAGEMENT privileges

COPY FROM

INSERT privilege on the table

COPY TO

SELECT privilege on the table

CREATE DATABASE

CREATE privilege

CREATE TABLE

CREATE privilege on the table

CREATE USER

ALL PRIVILEGES on the database and its tables

CREATE VIEW AS <query>

CREATE privilege on the table and the required privileges for the specified query

DESCRIBE

SELECT privilege on the table

DROP DATABASE

DROP privilege on the database

DROP TABLE

DROP privilege on the table

DROP VIEW

DROP privilege on the table

DROP USER

ACCESS MANAGEMENT privileges

EXPLAIN

none

FLUSH TABLES

MAINTENANCE privilege on the table

FLUSH TABLES WITH READ LOCK

MAINTENANCE privilege on all tables

GRANT

ALL PRIVILEGES on the database and its tables

GRANT ALL PRIVILEGES

ALL PRIVILEGES on the database and its tables

INSERT INTO

INSERT privilege on the table and the required privileges to access the data

SELECT FROM

SELECT privilege on the table

SELECT FIELDS FROM

SELECT privilege on the table

SET <configuration_option> TO

none

SHOW DATABASES

The user only sees the databases they have the privilege to access.

SHOW PARTITIONS IN

METADATA privilege on the table

SHOW TABLES

Any privilege to a table

SHOW USERS

ACCESS MANAGEMENT privileges

SHOW <configuration_option>

none

UNLOCK TABLES

MAINTENANCE privilege on all tables

USE

This statement requires granting privileges to access the database and tables within it.

Creating a superuser

In Raijin Database Engine, a superuser is a special role assigned the highest privileges for all databases and tables. Additionally, it can create other users and assign privileges, including the superuser role.

Example 7. Creating a superuser

This SQL command makes user foo a superuser.

GRANT ALL PRIVILEGES ON *.* TO 'foo'

Connecting to Raijin Database Engine using cURL

Users of the Raijin Database Engine may want or need to use alternative authentication options, such as cURL, instead of the Raijin Database Engine user interface. The easiest way to authenticate with cURL is to obtain a JSON Web Token (JWT).

Example 8. Using cURL to authenticate to Raijin Database Engine

The following command creates the JWT token in /tmp/raijin_cookies.txt. Replace admin, password, and the Raijin URL accordingly.

$ curl -c /tmp/raijin_cookies.txt -X POST -d '{"username":"admin", "password":"password"}' http://localhost:2500/api/v1.1/authentications

Once you create the token, specify the -b option with the path to the cookies file in your subsequent cURL requests.

$ curl -b /tmp/cookies.txt ...