Schema Access Levels

Initial State

NuoDB initializes a database with two schemas: SYSTEM and USER.

The SYSTEM schema contains system tables: see SQL System Tables.
- Users are not allowed to create database objects in the SYSTEM schema, not even a user with the System.ADMINISTRATOR role.
- The SYSTEM schema is reserved for database metadata, its tables should only be queried using SELECT.
The USER schema is the default schema. It is initially empty.
- If no schema is specified when connecting to the database, the connection will use the USER schema.
- If the user explicitly specifies an empty schema, the database reports, "Explicitly set schema must not be empty".

For information on creating new schemas refer to Manage Database Schemas.

Schema Access

Any user can use the SHOW commands to list the database objects in any schema in the database. They can also view any table in the System schema, but can only see their own data in System pseudo tables, such as connections and transactions.

However, users must be granted permission to:

Access any database objects in a schema.
- This includes in the USER schema.
Modify a schema or the objects in it.

By default only the creator of a schema or a user assigned the System.ADMINISTRATOR role can:

Create new database objects in that schema.
Drop that schema.

To give these privileges to another user, you must use the GRANT command.

To give permission for a user to create new schemas requires a special GRANT:
- GRANT CREATE ON System.SCHEMAS TO <user>
  - This is a one-off special case. It is not possible to grant any permission on any other table in the SYSTEM schema.
Specify GRANT CREATE ON SCHEMA to give permission to a user to create tables, views, SQL domains and sequence objects in an existing schema that you own (usually because you created it.).
- However, this command is limited in scope:
  - It does not grant permission to create roles, schemas or users.
  - It does not affect functions and procedures. Only users assigned the System.DBA role have permission to create functions and procedures.
  - It does not affect indexes. A user can create an index on any table for which the user has ALTER privileges. The user does not need CREATE privileges on a schema to create indexes on tables in that schema.
  - It does not affect triggers. A user can create a trigger on any table for which the user has TRIGGER privileges. The user does not need CREATE privileges on a schema to create triggers on tables in that schema.
Specify GRANT ALTER ON SCHEMA to give permission to a user to drop objects from the specified schema.

Examples

-- Logged in as a user with SYSTEM.ADMINISTRATOR role
-- Create some users
CREATE USER user1 PASSWORD 'pass1';
CREATE USER user2 PASSWORD 'pass2';
CREATE USER user3 PASSWORD 'pass3';
QUIT

-- Logged in as new user USER1 (who has no additional permissions).
-- Schema USER is current by default
-- These all succeed
SHOW SCHEMAS
SHOW SCHEMA USER
SHOW TABLE USER.Customers

-- These do not
SELECT * FROM TABLE Customers;
Error 58000: requested access to USER.CUSTOMERS is denied

CREATE TABLE Products(Name STRING);
Error 58000: user USER1 does not have create authority to Schema USER

DROP TABLE Customers;
Error 58000: user USER1 does not have alter authority to Table USER.CUSTOMERS

CREATE Schema Store;
Error 58000: user USER1 does not have create authority to Table SYSTEM.SCHEMAS

QUIT

-- Logged in again as user with SYSTEM.ADMINISTRATOR role
--
-- Allow user1 to create schemas.
GRANT CREATE ON System.SCHEMAS TO user1;
QUIT

-- Logged in again as user1
--
-- Create a new schema
CREATE SCHEMA Store;

-- Allow user2 to create new objects in the schema
GRANT CREATE ON SCHEMA Store TO user2;

-- Allow user2 to modify the schema (to drop objects)
GRANT ALTER ON SCHEMA Store TO user2;
QUIT

-- Logged in as user2
--
-- Create a new table
CREATE TABLE Products(Name STRING, Product_Id int, Description String);

-- Allow user3 to fully manipulate the table
GRANT all ON Products TO user3;

Figure 1. Schema access levels