Schema Access Levels
NuoDB initializes a database with two schemas:
SYSTEMschema contains system tables: see SQL System Tables.
Users are not allowed to create database objects in the
SYSTEMschema is reserved for database metadata, its tables should only be queried using
USERschema is the default schema. It is initially empty.
If no schema is specified when connecting to the database, the connection will use the
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.
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
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 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
DBArole 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
ALTERprivileges. The user does not need
CREATEprivileges 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
TRIGGERprivileges. The user does not need
CREATEprivileges on a schema to create triggers on tables in that schema.
GRANT ALTER ON SCHEMA to give permission to a user to drop objects from the specified schema.
-- 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';
-- 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
-- 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;