AnalyticDB:CREATE VIEW

CREATE
[OR REPLACE]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name
AS select_statement;

V3.1.9.0 e posteriores

O AnalyticDB for MySQL V3.1.9.0 e versões posteriores são compatíveis com o comportamento padrão SELECT * do MySQL. Ao criar uma view com SELECT *, o cluster expande o asterisco para nomes explícitos de colunas durante a análise sintática. Portanto, adicionar ou remover uma coluna da tabela base não invalida a view.

Caso especial: Se você renomear a Coluna C para Coluna D após criar uma view que referencia a Coluna C, o cluster reportará um erro ao consultar a view. Isso ocorre mesmo que a Coluna C não seja usada no resultado da consulta, pois a resolução de colunas acontece na etapa de análise sintática, antes de qualquer otimização que possa eliminar colunas não utilizadas. Esse é o comportamento esperado de compatibilidade com MySQL.

Versões anteriores à 3.1.9.0

O cluster não expande SELECT * no momento da criação. Em vez disso, ele rastreia apenas o número de colunas. Se uma coluna for adicionada ou removida da tabela base, a consulta à view retornará:

View '<view_name>' is stale; it must be re-created

Desativar o comportamento compatível com MySQL

Para reverter ao comportamento anterior à versão 3.1.9.0, use uma das seguintes abordagens:

Para uma única view — adicione uma dica à instrução CREATE VIEW:

/*+LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false*/
CREATE VIEW v0
AS
SELECT * FROM base0;

Para todas as views no cluster — defina um parâmetro global:

SET ADB_CONFIG LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE = false;

Preparar dados de teste

Execute as instruções a seguir usando a conta privilegiada do seu cluster AnalyticDB for MySQL.

1. Crie um usuário chamado user1:

   CREATE USER user1 IDENTIFIED BY 'user1_pwd';

2. Crie uma tabela chamada t1 no banco de dados adb_demo:

   CREATE TABLE `t1` (
     `id`          bigint AUTO_INCREMENT,
     `id_province` bigint NOT NULL,
     `user_info`   varchar,
     PRIMARY KEY (`id`)
   ) DISTRIBUTED BY HASH(`id`);

3. Insira os dados de teste:

   INSERT INTO t1(id_province, user_info) VALUES (1,'Tom'),(1,'Jerry'),(2,'Jerry'),(3,'Mark');

Criar views com diferentes configurações de SQL SECURITY

Os exemplos a seguir criam três views sobre t1, cada uma com uma configuração de segurança diferente.

-- v1: INVOKER — the querying user must have access to both the view and t1
CREATE SQL SECURITY INVOKER VIEW v1
AS SELECT id_province, user_info FROM t1 WHERE id_province = 1;

-- v2: DEFINER — the querying user only needs access to the view;
--     the privileged account (definer) provides access to t1
CREATE SQL SECURITY DEFINER VIEW v2
AS SELECT id_province, user_info FROM t1 WHERE id_province = 1;

-- v3: no SQL SECURITY specified — defaults to INVOKER
CREATE VIEW v3
AS SELECT id_province, user_info FROM t1 WHERE id_province = 1;

Comparar permissões

Conceder apenas acesso no nível da view (sem acesso à tabela)

Com a conta privilegiada, conceda ao user1 o direito de consultar todas as três views:

GRANT SELECT ON adb_demo.v1 TO 'user1'@'%';
GRANT SELECT ON adb_demo.v2 TO 'user1'@'%';
GRANT SELECT ON adb_demo.v3 TO 'user1'@'%';

Quando o user1 se conecta ao adb_demo e executa:

SELECT * FROM v2;

A consulta tem êxito porque v2 usa DEFINER. O acesso da conta privilegiada a t1 satisfaz a verificação de privilégios:

+-------------+-----------+
| ID_PROVINCE | USER_INFO |
+-------------+-----------+
|           1 | Tom       |
|           1 | Jerry     |
+-------------+-----------+

A consulta a v1 ou v3 falha porque ambas usam INVOKER, e o user1 não possui permissão SELECT em t1:

SELECT * FROM v1;
-- or
SELECT * FROM v3;

ERROR 1815 (HY000): [20049, 2021083110261019216818804803453927668] : Failed analyzing stored view

Conceder acesso no nível da tabela

Após conceder ao user1 acesso a t1:

GRANT SELECT ON adb_demo.t1 TO user1@'%';

Agora é possível consultar todas as três views com êxito, obtendo o mesmo resultado:

+-------------+-----------+
| ID_PROVINCE | USER_INFO |
+-------------+-----------+
|           1 | Tom       |
|           1 | Jerry     |
+-------------+-----------+

Nomes de colunas definidos em minúsculas aparecem em maiúsculas nos resultados da view

Por padrão, o AnalyticDB for MySQL exibe os nomes das colunas em conjuntos de resultados de views em letras maiúsculas. Para preservar os nomes das colunas em minúsculas, execute:

SET ADB_CONFIG VIEW_OUTPUT_NAME_CASE_SENSITIVE=true;