Users, Authentication, and Authorization

Oracle AI Database security differs significantly from that of MongoDB. This section explains the security model of Oracle Database API for MongoDB, including the creation of users, their authentication, and their authorization to perform different operations.

By default, MongoDB deployments do not enable user authentication and authorization checks. Oracle AI Database always requires authentication, and it always verifies that a connected user is authorized to perform a requested operation. A valid user name and password must be provided for authentication.

Oracle Database API for MongoDB supports the following connection-option values for authentication:

Oracle Database API for MongoDB relies on Oracle AI Database users, privileges, and roles. You cannot add or modify these users and roles using MongoDB clients or drivers. You can instead do this using SQL or Oracle Database Actions.

The minimum Oracle AI Database role required to use the API is DB_DEVELOPER_ROLE. A schema also needs sufficient tablespace quota to create collections.

A user who connects to Oracle Database API for MongoDB must also be enabled for use with Oracle REST Data Services (ORDS). This can be done by invoking PL/SQL procedure ORDS.enable_schema or using Oracle AI Database Actions.

For MongoDB, a database is a set of collections. A MongoDB database corresponds to an Oracle AI Database schema, which is typically associated with a database user of the same name. This chapter uses user when discussing authentication and authorization, and schema when discussing the database that contains MongoDB collections.

The following example connects with mongosh:

mongosh 'mongodb://scott:<password>@localhost:27017/scott?authMechanism=PLAIN&authSource=$external&retryWrites=false&loadBalanced=true&tls=true'

In this example, the credentials are for Oracle AI Database user scott. The URL path also selects scott as the initial MongoDB database, which corresponds to schema scott.

A user who connects using SQL or the MongoDB API also requires a secure password.

Access to the User’s Own Schema

A user of the API can perform operations within its own schema. For this own-schema access, the user name and schema name are the same. Examples of such operations include creating new collections, reading and writing documents, and creating indexes.

The preceding mongosh example connects as user scott and selects database scott. After connecting, this command inserts a document into collection employees in schema scott:

db.employees.insertOne({ name: "Joe" })

If collection employees does not already exist, the insert operation creates it automatically.

Access to Other Schemas

To access another schema, the application selects the target schema by using a MongoDB database name. It can do this with commands such as use <database>, db.getSiblingDB("<database>"), or by setting a command $db value.

Oracle Database API for MongoDB handles access to other schemas differently for administrative users and ordinary users:

Details are covered in Administrative Users and Cross-Schema Access for Ordinary Users.

Administrative Users

An administrative user is one that has all of the following Oracle AI Database privileges:

CREATE USER
ALTER USER
DROP USER

An administrative user can do the following:

An administrative user can access another schema in either of two ways:

When an administrative user tries to insert data into a database schema that does not exist, that schema is created automatically as a schema-only account. A schema-only account does not have a password and cannot be logged into. The new schema is granted these privileges: CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, and CREATE JOB. The schema is also given an unlimited tablespace quota and is enabled for using ORDS.

Oracle recommends that you do not allow production applications to use an administrative user. Applications should instead connect as ordinary users, with a minimum set of privileges. In particular, connect an application to the database using a MongoClient that is specific to a particular schema or application user.

Cross-Schema Access for Ordinary Users

An ordinary user can access collections in another Oracle AI Database schema when Oracle AI Database grants allow the requested operation on the underlying collection table or view. The target schema does not need to be ORDS-enabled.

For example, an application can connect as read_user, switch to database human_resources, and query human_resources.employees if read_user has the required privileges on that collection.

mongosh "mongodb://read_user:<password>@localhost:27017/read_user?authMechanism=PLAIN&authSource=$external&retryWrites=false&loadBalanced=true&tls=true"

use human_resources
db.employees.find({ name: "Joe" })

If the user does not have the required privileges, the operation fails with an Oracle AI Database authorization error.

The following examples show common grant patterns; some use the show collections command, which lists only collections that are visible to the authenticated user. See Listing Collections.

Granting Read Access to Collections in Another Schema

To give read_user read access to all JSON collections in schema human_resources:

CREATE USER read_user IDENTIFIED BY password;

GRANT DB_DEVELOPER_ROLE TO read_user;
GRANT READ ANY TABLE ON SCHEMA human_resources TO read_user;

After connecting as read_user, the user can query existing and future collections in human_resources:

use human_resources

db.employees.find({ name: "Joe" })
db.departments.find({ name: "Engineering" })

The command show collections lists the collections in human_resources that are visible to read_user:

show collections
departments
employees

If read_user tries to insert into a collection without INSERT privilege, the operation fails:

db.employees.insertOne({ name: "Jill" })

Example error:

ORA-41900: missing INSERT privilege on "HUMAN_RESOURCES"."EMPLOYEES"

To limit access to existing collections only, grant privileges on the individual collections, as described in the next section.

Granting Access to Specific Collections

You can grant access to one collection instead of granting access to every collection in a schema.

GRANT READ ON human_resources.employees TO read_user;

After this grant, read_user can query human_resources.employees:

use human_resources
db.employees.find({ name: "Joe" })

Collections for which the user has no privilege are not listed by show collections.

show collections
employees

A query on a collection that is not visible to the user behaves as if the collection does not exist:

db.departments.find({ name: "Engineering" })

This query returns no result.

For insert operations, MongoDB uses create-if-not-exists semantics. If the collection does not exist and the user lacks privileges to create a collection in the target schema, Oracle AI Database rejects the operation.

db.XYZ.insertOne({ name: "Jill" })

Example error:

ORA-01031: insufficient privileges

Granting Full Access to Collections in Another Schema

To allow a user to create, read, update, delete, index, and drop collections in another schema, grant schema-level privileges.

CREATE USER app_user IDENTIFIED BY password;

GRANT DB_DEVELOPER_ROLE TO app_user;
GRANT ALL PRIVILEGES ON SCHEMA human_resources TO app_user;

After connecting as app_user, the user can create and use collections in human_resources:

use human_resources

db.createCollection("jobs")
db.jobs.insertOne({ fullTimeJob: "programmer" })

show collections
departments
employees
jobs

Granting CRUD Access Without DDL Access

To allow CRUD operations but prevent collection creation and drop operations, grant DML privileges without DDL privileges.

CREATE USER app_user2 IDENTIFIED BY password;

GRANT DB_DEVELOPER_ROLE TO app_user2;

GRANT SELECT ANY TABLE ON SCHEMA human_resources TO app_user2;
GRANT INSERT ANY TABLE ON SCHEMA human_resources TO app_user2;
GRANT UPDATE ANY TABLE ON SCHEMA human_resources TO app_user2;
GRANT DELETE ANY TABLE ON SCHEMA human_resources TO app_user2;

After connecting as app_user2, CRUD operations on existing collections are allowed:

use human_resources
db.employees.insertOne({ name: "Morpheus" })

Collection DDL is not allowed:

db.createCollection("XYZ")
db.employees.drop()

Example error:

ORA-01031: insufficient privileges

Privileges Required by Common MongoDB Operations

The following table summarizes the Oracle AI Database privileges required for common MongoDB operations. These privileges can be granted directly on an object or through schema-level grants.

MongoDB operation Required Oracle AI Database privileges
find SELECT or READ
insert INSERT and SELECT; if the target collection does not exist and the insert creates it automatically, CREATE ANY TABLE and CREATE ANY INDEX on the target schema are also required
update UPDATE and SELECT; INSERT is required if an upsert takes the insert path; if the target collection does not exist and an upsert creates it automatically, CREATE ANY TABLE and CREATE ANY INDEX on the target schema are also required
delete DELETE and SELECT
findAndModify SELECT; plus UPDATE for updates, DELETE for removals, and INSERT if an upsert takes the insert path; if the target collection does not exist and an upsert creates it automatically, CREATE ANY TABLE and CREATE ANY INDEX on the target schema are also required
createCollection CREATE ANY TABLE and CREATE ANY INDEX on the target schema
drop collection SELECT and DROP ANY TABLE on the target schema
createIndex SELECT and CREATE ANY INDEX on the target schema
dropIndex SELECT and DROP ANY INDEX on the target schema
Read aggregation stages SELECT or READ
Write aggregation stages Privileges required for the source reads and target writes or DDL. For example, stages such as $out and $merge can require create, drop, insert, update, or delete privileges on the target schema

When an operation creates a collection, the schema that owns the collection must also have sufficient tablespace quota.

Listing Collections

The show collections command lists collections in the current MongoDB database that are visible to the authenticated user. This includes collections in the user’s own schema and collections in other schemas on which the user has at least one Oracle AI Database privilege, such as SELECT, READ, INSERT, UPDATE, or DELETE. The privilege can be granted directly on the collection table or view, or through a schema-level grant. In SQL terms, a JSON collection is visible to the user when it appears in that user’s ALL_JSON_COLLECTIONS view.

Listing Databases

MongoDB commands such as show databases list MongoDB databases, which correspond to Oracle AI Database schemas.

The command show databases does not require a separate DML or DDL privilege. What it returns depends on the same collection-visibility rule used by show collections: at least one privilege on at least one JSON collection in a schema.

For an ordinary user, show databases returns:

For example, if ordinary user scott can access JSON collections in schema scott2, then show databases can include both schemas:

scott   423.23 KiB
scott2  256.96 KiB

For an administrative user, show databases returns:

Dropping a MongoDB Database

Using Oracle Database API for MongoDB to drop a MongoDB database does not drop the underlying Oracle AI Database schema. Instead, it drops all collections within the schema.

To drop the schema itself, an administrative user must use SQL, for example by using Database Actions with an Autonomous Oracle AI Database.

Transactions Across Schemas

Transactions can include operations on collections in more than one schema, as long as the authenticated user has the required Oracle AI Database privileges on each collection.

Example:

const session = db.getMongo().startSession();

session.startTransaction();

session.getDatabase("human_resources").employees.insertOne(
  { name: "Jonathan" }
);

session.getDatabase("app_user2").orders.insertOne(
  { name: "Jonathan", item: "Laptop", cost: 2500 }
);

session.commitTransaction();
session.endSession();

If a required privilege is missing, the failing operation returns an error. The application can then abort the transaction.

session.abortTransaction();

User, Schema, and Database name restrictions

See Also: