> ## Documentation Index
> Fetch the complete documentation index at: https://www.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Create a User-defined Schema
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
This page provides best-practice guidance on creating user-defined schemas, with some simple examples based on Cockroach Labs's fictional vehicle-sharing company, MovR.
For detailed reference documentation on the `CREATE SCHEMA` statement, including additional examples, see the `CREATE SCHEMA` syntax page.
## Before you begin
Before reading this page, do the following:
* Create a CockroachDB Standard cluster or start a local cluster.
* Review the database schema objects.
* Create a database.
## Create a user-defined schema
User-defined schemas belong to the second level of the CockroachDB naming hierarchy.
To create a user-defined schema, use a `CREATE SCHEMA` statement, following [the user-defined schema best practices](#user-defined-schema-best-practices). After reviewing the best practices, see the example we provide [below](#examples).
### User-defined schema best practices
Here are some best practices to follow when creating and using user-defined schemas:
* If you want to create multiple objects (e.g., tables or views) with the same name in your cluster, do so in different user-defined schemas in the same database.
* If you want to separate lower-level objects (e.g., a set of tables or views) for access or organizational purposes, do not create those objects in the preloaded `public` schema. Instead, create user-defined schemas, and then create the objects in the user-defined schemas.
* Create user-defined schemas as a member of the `admin` role (e.g., as the `root` user), and then give ownership of them to a different user, with fewer privileges across the database, following authorization best practices.
* When you create a user-defined schema, take note of the object's owner. You can specify the owner in a `CREATE SCHEMA` statement with the `AUTHORIZATION` keyword. If `AUTHORIZATION` is not specified, the owner will be the user creating the user-defined schema.
* Do not create user-defined schemas in the preloaded `defaultdb` database. Instead, use a database you have created. If you do not specify a database in the `CREATE SCHEMA` statement, the user-defined schema will be created in your SQL session's current database.
* When referring to a lower-level object in a database (e.g., a table), include the object's schema name (e.g., `schema_name.table_name`). Specifying the schema name in a lower-level object reference can prevent users from attempting to access the wrong object, if there are multiple objects with the same name in a database.
* Use a database schema migration tool or the CockroachDB SQL client instead of a client library to execute database schema changes.
### Examples
Suppose you want to separate the tables and indexes in your cluster such that one user manages a group of tables, and another user manages a different group of tables. You can do this with two different user-defined schemas, owned by two different SQL users, with fewer privileges than the `root` user.
Open the `dbinit.sql` file that you created in the Create a Database example, and add the following statements under the `CREATE DATABASE` statement:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
USE movr;
CREATE USER IF NOT EXISTS max;
GRANT CREATE ON DATABASE movr TO max;
CREATE USER IF NOT EXISTS abbey;
GRANT CREATE ON DATABASE movr TO abbey;
```
The first statement sets the `movr` database as the current database. The next two sets of statements create SQL users named `max` and `abbey` in the `movr` database, with `CREATE` privileges on the database. `CREATE` privileges will allow each user to create tables in the database.
Now, under the `CREATE USER` statements, add `DROP SCHEMA` and `CREATE SCHEMA` statements for each user's user-defined schema:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DROP SCHEMA IF EXISTS max_schema CASCADE;
CREATE SCHEMA max_schema AUTHORIZATION max;
DROP SCHEMA IF EXISTS abbey_schema CASCADE;
CREATE SCHEMA abbey_schema AUTHORIZATION abbey;
```
The first set of statement clears the database of any existing schema named `max_schema`, and then creates a schema named `max_schema` with the owner `max`. The next set of statements does the same, but for `abbey_schema`, with `abbey` as the owner.
It might also be a good idea to grant the `USAGE` privilege on each schema to the other user in the database. This will allow the other user to access objects in the schema, but it will not let them delete the schema, or create objects inside of it.
Under the `CREATE SCHEMA` statements for each user-defined schema, add a `GRANT` statement granting `USAGE` privileges on the schema to the other user.
The `dbinit.sql` file should now look something link this:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE DATABASE IF NOT EXISTS movr;
USE movr;
CREATE USER IF NOT EXISTS max;
GRANT CREATE ON DATABASE movr TO max;
CREATE USER IF NOT EXISTS abbey;
GRANT CREATE ON DATABASE movr TO abbey;
DROP SCHEMA IF EXISTS max_schema CASCADE;
CREATE SCHEMA max_schema AUTHORIZATION max;
GRANT USAGE ON SCHEMA max_schema TO abbey;
DROP SCHEMA IF EXISTS abbey_schema CASCADE;
CREATE SCHEMA abbey_schema AUTHORIZATION abbey;
GRANT USAGE ON SCHEMA abbey_schema TO max;
```
To execute the statements in the `dbinit.sql` file as the `root` user, run the following command:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql \
--certs-dir={certs-directory} \
--user=root \
-f dbinit.sql
```
Before the new users can connect to the cluster and start creating objects, they each need a user certificate. To create a user certificate for `max`, open a new terminal, and run the following `cockroach cert` command:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach cert create-client max --certs-dir={certs-directory} --ca-key={my-safe-directory}/ca.key
```
Create a user certificate for `abbey` as well:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach cert create-client abbey --certs-dir={certs-directory} --ca-key={my-safe-directory}/ca.key
```
As one of the new users, use a `SHOW SCHEMAS` statement to show the preloaded and user-defined schemas in the `movr` database:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql \
--certs-dir={certs-directory} \
--user=abbey \
--database=movr \
--execute="SHOW SCHEMAS;"
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
schema_name | owner
---------------------+--------
abbey_schema | abbey
crdb_internal | NULL
information_schema | NULL
max_schema | max
pg_catalog | NULL
pg_extension | NULL
public | admin
(7 rows)
```
You're now ready to start adding tables to the `max_schema` user-defined schema as the `max` user, and to the `abbey_schema` user-defined schema as the `abbey` user.
To use a user-defined schema when connecting to your cluster using a connection URL, use the `options` parameter and set the `search_path` session variable to the schema name. Refer to the example.
For guidance on creating tables, see at Create a Table.
## What's next?
* Create a Table
* Secondary Indexes
You might also be interested in the following pages:
* `CREATE SCHEMA`
* `cockroach` Commands Overview
* Create a Database
* Schema Design Overview
* CockroachDB naming hierarchy