Resumo : neste tutorial, você aprenderá como usar funções MySQL para agilizar o gerenciamento de privilégios.
Introdução às funções do MySQL
Normalmente, um servidor de banco de dados MySQL pode ter vários usuários com o mesmo conjunto de privilégios.
Anteriormente, a única maneira de conceder e revogar os mesmos privilégios a vários usuários era modificar os privilégios de cada usuário individualmente, o que consumia muito tempo.
Para agilizar esse processo, o MySQL introduziu um novo conceito conhecido como função. Uma função é essencialmente uma coleção nomeada de privilégios.
Assim como nas contas de usuário, você pode conceder privilégios a funções e revogar privilégios delas. Este recurso ajuda você a simplificar significativamente o gerenciamento de privilégios.
Se quiser conceder o mesmo conjunto de privilégios a vários usuários, siga estas etapas:
- Primeiro, crie uma nova função.
- Segundo, conceda privilégios à função.
- Terceiro, conceda a função aos usuários.
Quando quiser alterar os privilégios dos usuários, você só precisa alterar os privilégios da função concedida. Estas alterações serão aplicadas a todos os usuários aos quais a função foi concedida.
Exemplo de funções MySQL
Primeiro, crie um novo banco de dados chamado crm
, que significa Customer Relationship Management.
CREATE DATABASE crm;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Em seguida, mude o banco de dados atual para o crm
banco de dados:
USE crm;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Em seguida, crie customers
uma tabela dentro do CRM
banco de dados.
CREATE TABLE customers(
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
phone VARCHAR(15) NOT NULL,
email VARCHAR(255)
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Depois disso, insira duas linhas na customers
tabela:
INSERT INTO customers(first_name,last_name,phone,email)
VALUES('John','Doe','(408)-987-7654','[email protected]'),
('Lily','Bush','(408)-987-7985','[email protected]');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Por fim, verifique a inserção usando a seguinte SELECT
instrução:
SELECT * FROM customers;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Criando funções
Suponha que você esteja desenvolvendo um aplicativo que utiliza o CRM
banco de dados. Para interagir com o CRM
banco de dados, você precisa criar contas para desenvolvedores que precisam de acesso total ao banco de dados. Além disso, você precisa criar contas para usuários que precisam apenas de acesso de leitura e outros que necessitam de acesso de leitura e gravação.
Para evitar a concessão de privilégios a cada conta de usuário individualmente, crie um conjunto de funções e conceda as funções apropriadas a cada conta de usuário.
Para criar novas funções, você usa a CREATE ROLE
instrução:
CREATE ROLE
crm_dev,
crm_read,
crm_write;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
O nome da função é semelhante à conta do usuário e consiste em duas partes: o nome e o host:
role_name@host_name
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Se você omitir a parte do host, o padrão será ‘%’, o que significa qualquer host.
Concedendo privilégios a funções
Para conceder privilégios a uma função, você usa GRANT
a instrução. A instrução a seguir concede todos os privilégios à crm_dev
função:
GRANT ALL
ON crm.*
TO crm_dev;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A instrução a seguir concede SELECT
privilégio à crm_read
função:
GRANT SELECT
ON crm.*
TO crm_read;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A instrução a seguir concede privilégios INSERT
, UPDATE
e DELETE
à crm_write
função:
GRANT INSERT, UPDATE, DELETE
ON crm.*
TO crm_write;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Atribuindo funções a contas de usuário
Suponha que você precise de uma conta de usuário como desenvolvedor, uma conta de usuário com acesso somente leitura e duas contas de usuário que possam ter acesso de leitura/gravação.
Para criar novos usuários, você usa CREATE USER
instruções como segue:
-- developer user
CREATE USER crm_dev1@localhost IDENTIFIED BY 'Secure$1782';
-- read access user
CREATE USER crm_read1@localhost IDENTIFIED BY 'Secure$5432';
-- read/write users
CREATE USER crm_write1@localhost IDENTIFIED BY 'Secure$9075';
CREATE USER crm_write2@localhost IDENTIFIED BY 'Secure$3452';
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para atribuir funções aos usuários, você usa a GRANT
instrução.
A instrução a seguir concede a crm_rev
função à conta do usuário crm_dev1@localhost
:
GRANT crm_dev
TO crm_dev1@localhost;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A instrução a seguir concede a crm_read
função à conta do usuário crm_read1@localhost
:
GRANT crm_read
TO crm_read1@localhost;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A instrução a seguir concede as funções crm_read
e crm_write
às contas de usuário crm_write1@localhost
e crm_write2@localhost
:
GRANT crm_read,
crm_write
TO crm_write1@localhost,
crm_write2@localhost;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para verificar as atribuições de função, use a SHOW GRANTS
instrução como no exemplo a seguir:
SHOW GRANTS FOR crm_dev1@localhost;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A instrução retornou o seguinte conjunto de resultados:
Como você pode ver, ele apenas retornou funções concedidas. Para exibir os privilégios representados por essas funções, use a USING
cláusula com os nomes das funções concedidas conforme a seguir:
SHOW GRANTS
FOR crm_write1@localhost
USING crm_write;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A instrução retorna a seguinte saída:
Configurando funções padrão
Se você se conectar ao MySQL usando a crm_read1
conta de usuário e tentar acessar o CRM
banco de dados:
>mysql -u crm_read1 -p
Enter password: ***********
mysql>USE crm;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A declaração emitiu a seguinte mensagem de erro:
ERROR 1044 (42000): Access denied for user 'crm_read1'@'localhost' to database 'crm'
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Isso ocorre porque quando você concedeu funções a uma conta de usuário, as funções não foram ativadas automaticamente quando a conta de usuário se conecta ao servidor de banco de dados.
Se você invocar a CURRENT_ROLE()
função, ela retornará NONE
, indicando que nenhuma função ativa está definida:
SELECT current_role();
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----------------+
| current_role() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para especificar quais funções devem estar ativas sempre que uma conta de usuário se conectar ao servidor de banco de dados, você pode usar a SET DEFAULT ROLE
instrução.
A instrução a seguir define a função padrão da crm_read1@localhost
conta para incluir todas as funções atribuídas:
SET DEFAULT ROLE ALL TO crm_read1@localhost;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Agora, se você se conectar ao servidor de banco de dados MySQL usando a crm_read1
conta de usuário e, em seguida, invocar a CURRENT_ROLE()
função, verá as funções padrão da crm_read1
conta de usuário.
>mysql -u crm_read1 -p
Enter password: ***********
mysql> select current_role();
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----------------+
| current_role() |
+----------------+
| `crm_read`@`%` |
+----------------+
1 row in set (0.00 sec)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Você pode testar os privilégios da crm_read
conta alternando o banco de dados atual CRM
e executando uma SELECT
instrução e uma DELETE
instrução da seguinte maneira:
mysql> use crm;
Database changed
mysql> SELECT COUNT(*) FROM customers;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> DELETE FROM customers;
ERROR 1142 (42000): DELETE command denied to user 'crm_read1'@'localhost' for table 'customers'
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Funcionou como esperado. Quando emitimos a DELETE
instrução, o MySQL emitiu um erro porque a crm_read1
conta do usuário tinha apenas acesso de leitura.
Configurando funções ativas
Uma conta de usuário pode modificar os privilégios efetivos do usuário atual na sessão atual, especificando quais funções concedidas estão ativas.
A instrução a seguir define a função ativa como NONE
, indicando que nenhuma função está ativa:
SET ROLE NONE;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para definir funções ativas para todas as funções concedidas, use a seguinte instrução:
SET ROLE ALL;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para definir funções ativas como funções padrão estabelecidas pela SET DEFAULT ROLE
instrução, você pode usar a seguinte instrução:
SET ROLE DEFAULT;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para definir funções nomeadas como ativas, você pode usar a seguinte instrução:
SET ROLE
granted_role_1
[,granted_role_2, ...]
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Revogando privilégios de funções
Para revogar privilégios de uma função específica, você usa a REVOKE
instrução. A REVOKE
declaração afeta não apenas a função, mas também qualquer conta à qual tenha sido concedida a função.
Por exemplo, para tornar temporariamente todos os usuários de leitura/gravação somente leitura, você pode modificar a crm_write
função da seguinte maneira:
REVOKE INSERT, UPDATE, DELETE
ON crm.*
FROM crm_write;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para restaurar os privilégios, você precisará concedê-los novamente da seguinte forma:
GRANT INSERT, UPDATE, DELETE
ON crm.*
FOR crm_write;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Removendo funções
Para excluir uma ou mais funções, você usa a DROP ROLE
instrução:
DROP ROLE role_name[, role_name, ...];
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Semelhante à REVOKE
declaração, a DROP ROLE
declaração revoga funções de todas as contas de usuário às quais foram concedidas.
Por exemplo, para remover as funções crm_read
e crm_write
, você pode usar a seguinte instrução:
DROP ROLE crm_read, crm_write;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Copiando privilégios de uma conta de usuário para outra
O MySQL trata contas de usuários como funções; como resultado, você pode conceder uma conta de usuário a outra conta de usuário, como conceder uma função a essa conta de usuário. Isso permite copiar privilégios de um usuário para outro.
Suponha que você queira outra conta de desenvolvedor para o CRM
banco de dados:
Primeiro, crie a nova conta de usuário:
CREATE USER crm_dev2@localhost
IDENTIFIED BY 'Secure$6275';
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Segundo, copie os privilégios da crm_dev1
conta de usuário para a crm_dev2
conta de usuário da seguinte maneira:
GRANT crm_dev1@localhost
TO crm_dev2@localhost;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Resumo
- Use funções para simplificar e agilizar o gerenciamento de privilégios em seu banco de dados.
- Use a
CREATE ROLE
instrução para criar coleções nomeadas de privilégios. - Use a
GRANT
instrução para conceder privilégios a uma função. - Use a
SHOW GRANTS
instrução para exibir os privilégios concedidos de uma função. - Use a
REVOKE
instrução para revogar privilégios de uma função. - Use a
DROP ROLE
instrução para excluir uma função.