This page describes how to create, use, and drop named schemas, and how to apply fine-grained access control to objects in named schemas. For overview information about named schemas, see Named schemas.
Before you begin
To perform procedures on this page, you need the following:
- The Database Admin roles/spanner.databaseAdmin role on your user account.
- Understand how fine-grained access control works.
Create a named schema
The CREATE SCHEMA
command (GoogleSQL
and PostgreSQL)
is used to create a named schema.
In the Google Cloud console, open the Spanner page.
Select an instance from the list.
Select a database.
In the navigation menu, click Spanner Studio.
Open a new tab by clicking
New SQL editor tab or New tab.In the Editor tab, enter your DDL.
GoogleSQL
Run the
CREATE SCHEMA
statement to create the named schema, for example:CREATE SCHEMA sch1;
Add database objects in the named schema, for example:
CREATE SEQUENCE sch1.sequence OPTIONS ( sequence_kind = 'bit_reversed_positive' ); CREATE TABLE Singers ( SingerId INT64 NOT NULL, FirstName STRING(1024), LastName STRING(1024), SingerInfo BYTES(MAX), ) PRIMARY KEY(SingerId); CREATE INDEX indexOnSingers ON Singers(FirstName); CREATE TABLE Albums ( SingerId INT64 NOT NULL, AlbumId INT64 NOT NULL, AlbumTitle STRING(MAX), ) PRIMARY KEY(SingerId, AlbumId), INTERLEAVE IN PARENT Singers ON DELETE CASCADE; CREATE TABLE Songs ( SingerId INT64 NOT NULL, AlbumId INT64 NOT NULL, TrackId INT64 NOT NULL, SongName STRING(MAX), ) PRIMARY KEY(SingerId, AlbumId, TrackId), INTERLEAVE IN PARENT Albums ON DELETE CASCADE; CREATE TABLE sch1.Singers ( SingerId INT64 NOT NULL, FirstName STRING(1024), LastName STRING(1024), SingerInfo BYTES(MAX), ) PRIMARY KEY(SingerId); CREATE INDEX sch1.indexOnSingers ON sch1.Singers(FirstName); CREATE TABLE sch1.Albums ( SingerId INT64 NOT NULL, AlbumId INT64 NOT NULL, AlbumTitle STRING(MAX), ) PRIMARY KEY(SingerId, AlbumId), INTERLEAVE IN PARENT sch1.Singers ON DELETE CASCADE; CREATE TABLE sch1.Songs ( SingerId INT64 NOT NULL, AlbumId INT64 NOT NULL, TrackId INT64 NOT NULL, SongName STRING(MAX), ) PRIMARY KEY(SingerId, AlbumId, TrackId), INTERLEAVE IN PARENT sch1.Albums ON DELETE CASCADE; CREATE VIEW sch1.SingerView SQL SECURITY INVOKER AS Select s.FirstName, s.LastName, s.SingerInfo FROM sch1.Singers AS s WHERE s.SingerId = 123456; CREATE VIEW SingerView SQL SECURITY INVOKER AS Select s.FirstName, s.LastName, s.SingerInfo FROM Singers AS s WHERE s.SingerId = 123456;
Spanner only lets you create an index that uses the same schema as the table that uses the index. We need to make sure that the index and table schema names are the same.
PostgreSQL
Run the
CREATE SCHEMA
statement to create the named schema, for example:CREATE SCHEMA sch1;
Add database objects in the named schema, for example:
CREATE SEQUENCE sch1.sequence BIT_REVERSED_POSITIVE CREATE TABLE sch1.singers( singer_id bigint primary key, album_id bigint default(nextval('sch1.sequence'))) CREATE TABLE sch1.albums(k bigint default(nextval('sch1.sequence'))primary key, album_id bigint) CREATE VIEW sch1.singer_view SQL SECURITY INVOKER AS SELECT * FROM sch1.singers CREATE INDEX index_singers ON TABLE sch1.singers(album_id)
Spanner only allows index creation in the same schema. In Spanner, PostgreSQL statements do this by default. You don't need to use fully qualified names to create indexes on named schemas.
View the named schemas and related objects in the Explorer pane.
Add fine-grained access control to a named schema
The following DDL statements add fine-grained access control to a named schema:
- The
USAGE
privilege grants privileges to the schema object. TheUSAGE
privilege is granted, by default, to the default schema. However, you can revoke theUSAGE
privilege for the default schema. Use caution when revoking access because users and roles that are revoked lose all access to objects in the default schema. - The
ALL
statement performs bulk grant privileges on ALL objects of a type in the schema. - The
DEFAULT
keyword refers to the default schema in FGAC DDL statements.
To access an object in a named schema, you must have usage permission on the named schema and corresponding permissions on database objects that use that schema. The following statements provide these permissions:
GRANT ALL
(GoogleSQL and PostgreSQL) grants the role permission to access all objects in the table created with the named schema. This statement only applies to objects that exist at that point in time. If you add more objects to the table later, you need to grant access to those objects as you create them.GRANT USAGE
(GoogleSQL and PostgreSQL) gives the role permission to access objects contained in the schema. This lets the grantee look up objects within the schema.
In the Google Cloud console, open the Spanner page.
Select an instance from the list.
Select a database.
In the navigation menu, click Spanner Studio.
Open a new tab by clicking
New SQL editor tab or New tab.In the Editor tab, enter your DDL.
GoogleSQL
Create your custom role for the named schema. In the following example, we use
role1
androle2
.CREATE ROLE role1 CREATE ROLE role2
Grant the role to the tables that use the named schema using
GRANT ALL
. In the following example, we usesch1
for the named schema androle1
for the role.GRANT SELECT ON ALL TABLES IN SCHEMA sch1 TO ROLE role1
Grant usage on the schema for the roles you created. In the following example, we grant usage on
sch1
torole1
androle2
.GRANT USAGE ON SCHEMA sch1 TO ROLE role1, role2
PostgreSQL
Create your custom role for the named schema. In the following example, we use
role1
androle2
.CREATE ROLE role1 CREATE ROLE role2
Grant the role to the tables that use the named schema using
GRANT ALL
. In the following example, we usesch1
for the named schema androle1
for the role.GRANT SELECT ON ALL TABLES IN SCHEMA sch1 TO role1
Grant usage on the schema for the roles you created. In the following example, we grant usage on
sch1
torole1
androle2
.GRANT USAGE ON SCHEMA sch1 TO role1, role2
Add and revoke fine-grained access control to a default schema
When you have named schemas, the default schema is called default
. You need to
use the default
schema name when adding or revoking fine-grained access control.
Add fine-grained access control to a default schema
By default, all users and roles have the USAGE
permission on the default
schema.
In the Google Cloud console, open the Spanner page.
Select an instance from the list.
Select a database.
In the navigation menu, click Spanner Studio.
Open a new tab by clicking
New SQL editor tab or New tab.In the Editor tab, enter your DDL.
GoogleSQL
In the following example, we grant access to all tables to
role1
.GRANT SELECT ON ALL TABLES IN SCHEMA default TO ROLE role1
PostgreSQL
In the following example, we grant access to all tables to
role1
.GRANT SELECT ON ALL TABLES IN SCHEMA default TO role1
Revoke fine-grained access control to a default schema
You can revoke the default fine-grained access control permissions on the default schema using the
REVOKE USAGE
command.
In the Google Cloud console, open the Spanner page.
Select an instance from the list.
Select a database.
In the navigation menu, click Spanner Studio.
Open a new tab by clicking
New SQL editor tab or New tab.In the Editor tab, enter your DDL.
GoogleSQL
REVOKE USAGE ON SCHEMA DEFAULT FROM ROLE public
PostgreSQL
REVOKE USAGE ON SCHEMA public FROM public
After the previous command is run, we must explicitly grant permissions to the roles that need to access the default schema. In the following example, we grant permissions to
role1
.GoogleSQL
In the following example, we grant access to the default schema to
role1
.SCHEMA default to ROLE role1
PostgreSQL
In the following example, we grant access to the default schema to
role1
.GRANT USAGE ON SCHEMA public To role1
View named schemas
- Select an instance from the list.
- Select a database.
- In the navigation menu, click Spanner Studio.
In the Explorer pane, expand the Schemas drop-down list.
Alternatively, you can use SQL to view all schemas in the
information_schema.schemata
table.
The following example shows how view names schemas and their owners:
SELECT schema_name, schema_owner
FROM information_schema.schemata
ORDER BY schema_owner
This statement provides a list of schemas and owners, similar to the following:
public spanner_admin
products spanner_admin
analytics spanner_admin
logs spanner_admin
pg_catalog spanner_system
information_schema spanner_system
spanner_sys spanner_system
Drop a named schema
The DROP SCHEMA
command (GoogleSQL
and PostgreSQL)
is used to drop a named schema.
In the Google Cloud console, open the Spanner page.
Select an instance from the list.
Select a database.
In the navigation menu, click Spanner Studio.
Open a new tab by clicking
New SQL editor tab or New tab.In the Editor tab, enter your DDL.
GoogleSQL
In the following example, we drop
sch1
.DROP SCHEMA IF EXISTS sch1;
PostgreSQL
In the following example, we drop
sch1
.DROP SCHEMA IF EXISTS sch1;
What's next
- Learn about Schema best practices.
- Learn about Views.
- Learn about Secondary indexes.
- Learn about Foreign keys.