Resumo : neste tutorial, você aprenderá sobre o controle de acesso a objetos armazenados no MySQL.
No MySQL, programas armazenados, incluindo procedimentos armazenados , funções armazenadas , gatilhos e eventos, são executados dentro de um contexto de segurança que determina seus privilégios.
Usos DEFINERe SQL SECURITYcaracterísticas do MySQL para controlar esses privilégios.
O atributo DEFINER
Ao definir uma rotina armazenada, como um procedimento armazenado ou função, você pode opcionalmente especificar o DEFINERatributo, que é o nome de uma conta MySQL:
CREATE [DEFINER=user] PROCEDURE spName(parameter_list)
...
CREATE [DEFINER=user] FUNCTION sfName()
...
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Se você ignorar o DEFINERatributo, o padrão será a conta do usuário atual.
Você pode especificar qualquer conta no DEFINERatributo se tiver o privilégio SUPERou SET_USER_ID. Se você especificar uma conta de usuário que não existe, o MySQL emitirá um aviso.
A partir do MySQL 8.0.16, você deve ter o SYSTEM_USERprivilégio para definir o DEFINERatributo de um objeto armazenado para uma conta de usuário que tenha o SYSTEM_USERprivilégio.
A característica SQL SECURITY
Rotinas armazenadas (procedimentos e funções armazenados) e visualizações podem incluir uma SQL SECURITYcláusula com um valor de DEFINERou INVOKER:
CREATE [DEFINER=user] PROCEDURE spName(parameter_list)
SQL SECURITY [DEFINER | INVOKER]
...
CREATE [DEFINER=user] FUNCTION sfName(parameter_list)
SQL SECURITY [DEFINER | INVOKER]
...
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )DEFINTOR DE SEGURANÇA SQL
Quando você usa o SQL SECURITY DEFINERpara um objeto armazenado, ele será executado em um contexto de segurança do definidor com o privilégio do usuário especificado pelo DEFINERatributo.
Observe que o usuário que invoca o objeto armazenado (ou invocador) pode não ter o mesmo privilégio que o definidor.
Caso o invocador tenha menos privilégios e o definidor tenha mais privilégios, o invocador pode realizar operações acima de seu privilégio dentro do objeto armazenado.
INVOCADOR DE SEGURANÇA SQL
Se você usar o SQL SECURITY INVOKERpara uma rotina ou visualização armazenada, ele operará dentro dos privilégios do invocador.
O DEFINERatributo não tem efeito durante a execução do objeto.
Exemplo de controle de acesso a objetos armazenados
Primeiro, crie um novo banco de dados chamado testdb:
CREATE DATABASE testdb;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, selecione o banco de dados testdb com o qual trabalhar:
USE testdb;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Terceiro, crie uma nova tabela chamada messages:
CREATE TABLE messages (
id INT AUTO_INCREMENT,
message VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Quarto, crie um procedimento armazenado que insira uma nova linha na messagestabela:
DELIMITER $$
CREATE DEFINER = root@localhost PROCEDURE InsertMessage(
msg VARCHAR(100)
)
SQL SECURITY DEFINER
BEGIN
INSERT INTO messages(message)
VALUES(msg);
END$$
DELIMITER ;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Neste procedimento armazenado, o definidor é root@localhosto superusuário que possui todos os privilégios.
A Segurança SQL está definida para o definidor. Isso significa que qualquer conta de usuário que chamar este procedimento armazenado será executada com todos os privilégios do definidor, ou seja, root@localhostconta de usuário.
Quinto, crie um novo usuário chamado dev@localhost:
CREATE USER dev@localhost
IDENTIFIED BY 'Abcd1234';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Sexto, conceda o EXECUTEprivilégio dev@localhostpara que ele possa executar qualquer procedimento armazenado no testdbbanco de dados:
GRANT EXECUTE ON testdb.*
TO dev@localhost;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Sétimo, use dev@localhostpara fazer login no servidor MySQL:
mysql -u dev@localhost -pLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Oito, use SHOW DATABASESpara exibir o banco de dados que dev@localhostpode acessar:
mysql> show databases;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Aqui está a lista:
+--------------------+
| Database |
+--------------------+
| information_schema |
| testdb |
+--------------------+
2 rows in set (0.00 sec)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Nono, selecione o testdbbanco de dados:
mysql> use testdb;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Décimo, chame o InsertMessageprocedimento para inserir uma linha na messagestabela:
mysql> call InsertMessage('Hello World');Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Esta é a saída:
Query OK, 1 row affected (0.01 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Mesmo dev@localhostnão tendo nenhum privilégio na messagestabela, ele pode inserir uma nova linha nessa tabela com êxito por meio do procedimento armazenado porque o procedimento armazenado é executado no contexto de segurança da root@localhostconta do usuário.
Décimo primeiro, vá para a sessão raiz e crie um procedimento armazenado que atualize a messagestabela:
DELIMITER $$
CREATE DEFINER=root@localhost
PROCEDURE UpdateMessage(
msgId INT,
msg VARCHAR(100)
)
SQL SECURITY INVOKER
BEGIN
UPDATE messages
SET message = msg
WHERE id = msgId;
END$$
DELIMITER ;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Possui UpdateMessageo contexto de segurança de INVOKERquem chama esse procedimento armazenado.
Décimo segundo, vá para a dev@localhostsessão do e chame o UpdateMessage()procedimento armazenado:
mysql> call UpdateMessage(1,'Good Bye');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Desta vez, o UpdateMessage()procedimento armazenado é executado com os privilégios do chamador, que é dev@localhost.
Por dev@localhostnão possuir nenhum privilégio na messagestabela, o MySQL emite um erro e rejeita a atualização:
ERROR 1142 (42000): UPDATE command denied to user 'dev'@'localhost' for table 'messages'
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Neste tutorial, você aprendeu sobre o controle de acesso a objetos armazenados do MySQL.
