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.

    • 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.

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 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. Specify GRANT CREATE ON SCHEMA to give permission to a user to create tables, views, SQL domains and sequence objects in the specified schema.

This command does not do the following:

  • It does not grant permission to create role, schema or user objects.

  • It does not affect functions and procedures. Only users assigned the 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
SQL> CREATE USER user1 PASSWORD 'pass1';
SQL> CREATE USER user2 PASSWORD 'pass2';
SQL> CREATE USER user3 PASSWORD 'pass3';
SQL> QUIT

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

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

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

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

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

SQL> QUIT

-- Logged in again as user with SYSTEM.ADMINISTRATOR role

-- Allow user1 to create schemas.
SQL> GRANT CREATE ON System.SCHEMAS TO user1;

-- Create a new schema
SQL> CREATE SCHEMA Store;

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

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

-- Create a new table
SQL> SQL> CREATE TABLE Products(Name STRING, Product_Id int, Description String);

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