Crie e faça a gestão de esquemas com nome

Esta página descreve como criar, usar e eliminar esquemas com nomes, e como aplicar o controlo de acesso detalhado a objetos em esquemas com nomes. Para ver informações gerais sobre os esquemas denominados, consulte o artigo Esquemas denominados.

Nota: para fazer referência a um objeto de base de dados num esquema com nome não predefinido no Spanner, tem de usar o respetivo nome totalmente qualificado. A utilização de um nome não qualificado com um caminho de pesquisa de esquema denominado configurado não é suportada.

Antes de começar

Para realizar os procedimentos nesta página, precisa do seguinte:

• A função Database Admin roles/spanner.databaseAdmin na sua conta de utilizador.
• Compreenda como funciona o controlo de acesso detalhado.

Crie um esquema com nome

O comando CREATE SCHEMA (GoogleSQL e PostgreSQL) é usado para criar um esquema com nome.

1. Na Google Cloud consola, abra a página Spanner.

   Aceda ao Spanner

2. Selecione uma instância na lista.

3. Selecione uma base de dados.

4. No menu de navegação, clique em Spanner Studio.

5. Abra um novo separador clicando em Novo separador do editor de SQL ou Novo separador.

6. No separador Editor, introduza o DDL.

   GoogleSQL

   Execute a declaração CREATE SCHEMA para criar o esquema com nome, por exemplo:

   CREATE SCHEMA sch1;
   

   Adicione objetos de base de dados no esquema com nome, por exemplo:

   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;
   

   O Spanner só permite criar um índice que use o mesmo esquema que a tabela que usa o índice. Temos de garantir que os nomes do esquema do índice e da tabela são iguais.

   PostgreSQL

   Execute a declaração CREATE SCHEMA para criar o esquema com nome, por exemplo:

   CREATE SCHEMA sch1;
   

   Adicione objetos de base de dados no esquema com nome, por exemplo:

   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)
   

   O Spanner só permite a criação de índices no mesmo esquema. No Spanner, as declarações PostgreSQL fazem isto por predefinição. Não precisa de usar nomes totalmente qualificados para criar índices em esquemas com nome.

7. Veja os esquemas com nome e os objetos relacionados no painel Explorador.

Adicione um controlo de acesso detalhado a um esquema com nome

As seguintes declarações DDL adicionam um controlo de acesso detalhado a um esquema denominado:

• O privilégio USAGE concede privilégios ao objeto de esquema. Por predefinição, o privilégio USAGE é concedido ao esquema predefinido. No entanto, pode revogar o privilégio USAGE para o esquema predefinido. Tenha cuidado ao revogar o acesso, porque os utilizadores e as funções revogadas perdem todo o acesso aos objetos no esquema predefinido.
• A declaração ALL executa privilégios de concessão em massa em TODOS os objetos de um tipo no esquema.
• A palavra-chave DEFAULT refere-se ao esquema predefinido nas declarações DDL de FGAC.

Para aceder a um objeto num esquema com nome, tem de ter autorização de utilização no esquema com nome e autorizações correspondentes em objetos da base de dados que usam esse esquema. Por exemplo, as seguintes declarações concedem autorizações para ler a partir de uma tabela:

• GRANT SELECT ON TABLE TABLE_NAME IN SCHEMA TO ROLE ROLE_NAME (GoogleSQL e PostgreSQL) concede à autorização de função a permissão para ler de uma tabela específica num esquema. Em alternativa, pode conceder a autorização de função para ler de todas as tabelas no esquema através da palavra-chave ALL. Por exemplo, GRANT SELECT ON ALL TABLES IN SCHEMA TO ROLE ROLE_NAME. A declaração ALL anterior aplica-se apenas às tabelas presentes no momento da execução da declaração.
• GRANT USAGE ON SCHEMA SCHEMA_NAME TO ROLE ROLE_NAME (GoogleSQL e PostgreSQL) concede à função autorização para aceder a objetos contidos no esquema se as autorizações do objeto contido também forem satisfeitas. Por exemplo, para uma tabela num esquema, precisa de "USAGE" no esquema e "SELECT" na tabela para ler a partir da tabela.

1. Na Google Cloud consola, abra a página Spanner.

   Aceda ao Spanner

2. Selecione uma instância na lista.

3. Selecione uma base de dados.

4. No menu de navegação, clique em Spanner Studio.

5. Abra um novo separador clicando em Novo separador do editor de SQL ou Novo separador.

6. No separador Editor, introduza o DDL.

   GoogleSQL

   Crie a sua função personalizada para o esquema com nome. No exemplo seguinte, usamos role1 e role2.

   CREATE ROLE role1
   CREATE ROLE role2
   

   Conceda a função às tabelas que usam o esquema com nome através de GRANT ALL. No exemplo seguinte, usamos sch1 para o esquema com nome e role1 para a função.

   GRANT SELECT ON ALL TABLES IN SCHEMA sch1 TO ROLE role1
   

   Conceda utilização no esquema para as funções que criou. No exemplo seguinte, concedemos a utilização de sch1 a role1 e role2.

   GRANT USAGE ON SCHEMA sch1 TO ROLE role1, role2
   

   PostgreSQL

   Crie a sua função personalizada para o esquema com nome. No exemplo seguinte, usamos role1 e role2.

   CREATE ROLE role1
   CREATE ROLE role2
   

   Conceda a função às tabelas que usam o esquema com nome através de GRANT ALL. No exemplo seguinte, usamos sch1 para o esquema com nome e role1 para a função.

   GRANT SELECT ON ALL TABLES IN SCHEMA sch1 TO role1
   

   Conceda utilização no esquema para as funções que criou. No exemplo seguinte, concedemos a utilização de sch1 a role1 e role2.

   GRANT USAGE ON SCHEMA sch1 TO role1, role2
   

Adicione e revogue o controlo de acesso detalhado a um esquema predefinido

Quando tem esquemas com nomes, o esquema predefinido chama-se default. Tem de usar o nome do esquema default quando adicionar ou revogar o controlo de acesso detalhado.

Adicione um controlo de acesso detalhado a um esquema predefinido

Por predefinição, todos os utilizadores e funções têm a autorização USAGE no esquema predefinido.

1. Na Google Cloud consola, abra a página Spanner.

   Aceda ao Spanner

2. Selecione uma instância na lista.

3. Selecione uma base de dados.

4. No menu de navegação, clique em Spanner Studio.

5. Abra um novo separador clicando em Novo separador do editor de SQL ou Novo separador.

6. No separador Editor, introduza o DDL.

   GoogleSQL

   No exemplo seguinte, concedemos acesso a todas as tabelas a role1.

   GRANT SELECT ON ALL TABLES IN SCHEMA default TO ROLE role1
   

   PostgreSQL

   No exemplo seguinte, concedemos acesso a todas as tabelas a role1.

   GRANT SELECT ON ALL TABLES IN SCHEMA default TO role1
   

Revogue o controlo de acesso detalhado a um esquema predefinido

Pode revogar as autorizações de controlo de acesso detalhado predefinidas no esquema predefinido através do comando REVOKE USAGE.

1. Na Google Cloud consola, abra a página Spanner.

   Aceda ao Spanner

2. Selecione uma instância na lista.

3. Selecione uma base de dados.

4. No menu de navegação, clique em Spanner Studio.

5. Abra um novo separador clicando em Novo separador do editor de SQL ou Novo separador.

6. No separador Editor, introduza o DDL.

   GoogleSQL

   REVOKE USAGE ON SCHEMA DEFAULT FROM ROLE public
   

   PostgreSQL

   REVOKE USAGE ON SCHEMA public FROM public
   

7. Depois de executar o comando anterior, temos de conceder explicitamente autorizações às funções que precisam de aceder ao esquema predefinido. No exemplo seguinte, concedemos autorizações à app role1.

   GoogleSQL

   No exemplo seguinte, concedemos acesso ao esquema predefinido a role1.

   GRANT USAGE ON SCHEMA default to ROLE role1
   

   PostgreSQL

   No exemplo seguinte, concedemos acesso ao esquema predefinido a role1.

   GRANT USAGE ON SCHEMA public To role1
   

Veja esquemas com nomes

1. Selecione uma instância na lista.
2. Selecione uma base de dados.
3. No menu de navegação, clique em Spanner Studio.

4. No painel Explorador, expanda a lista pendente Esquemas.

   

Em alternativa, pode usar SQL para ver todos os esquemas na tabela information_schema.schemata.

O exemplo seguinte mostra como ver esquemas de nomes de visualizações e os respetivos proprietários:

SELECT schema_name, schema_owner
  FROM information_schema.schemata
  ORDER BY schema_owner

Esta declaração fornece uma lista de esquemas e proprietários, semelhante à seguinte:

public spanner_admin
products  spanner_admin
analytics  spanner_admin
logs  spanner_admin
pg_catalog spanner_system
information_schema spanner_system
spanner_sys   spanner_system

Elimine um esquema com nome

Nota: o Spanner não suporta uma eliminação em cascata para esquemas com nomes. Tem de eliminar objetos da base de dados que usam o esquema com nome antes de eliminar o esquema.

O comando DROP SCHEMA (GoogleSQL e PostgreSQL) é usado para eliminar um esquema com nome.

1. Na Google Cloud consola, abra a página Spanner.

   Aceda ao Spanner

2. Selecione uma instância na lista.

3. Selecione uma base de dados.

4. No menu de navegação, clique em Spanner Studio.

5. Abra um novo separador clicando em Novo separador do editor de SQL ou Novo separador.

6. No separador Editor, introduza o DDL.

   GoogleSQL

   No exemplo seguinte, eliminamos sch1.

   DROP SCHEMA IF EXISTS sch1;
   

   PostgreSQL

   No exemplo seguinte, eliminamos sch1.

   DROP SCHEMA IF EXISTS sch1;
   

O que se segue?

• Saiba mais sobre as práticas recomendadas para esquemas.
• Saiba mais sobre as visualizações.
• Saiba mais sobre os índices secundários.
• Saiba mais sobre as chaves externas.