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 crmbanco de dados:
USE crm;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Em seguida, crie customersuma tabela dentro do CRMbanco 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 customerstabela:
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 SELECTinstruçã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 CRMbanco de dados. Para interagir com o CRMbanco 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 ROLEinstruçã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_nameLinguagem 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 GRANTa instrução. A instrução a seguir concede todos os privilégios à crm_devfunção:
GRANT ALL
ON crm.*
TO crm_dev;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A instrução a seguir concede SELECTprivilégio à crm_readfunçã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, UPDATEe DELETEà crm_writefunçã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 USERinstruçõ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 GRANTinstrução.
A instrução a seguir concede a crm_revfunçã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_readfunçã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_reade crm_writeàs contas de usuário crm_write1@localhoste 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 GRANTSinstruçã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 USINGclá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_read1conta de usuário e tentar acessar o CRMbanco 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 ROLEinstrução.
A instrução a seguir define a função padrão da crm_read1@localhostconta 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_read1conta de usuário e, em seguida, invocar a CURRENT_ROLE()função, verá as funções padrão da crm_read1conta 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_readconta alternando o banco de dados atual CRMe executando uma SELECTinstrução e uma DELETEinstruçã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 DELETEinstrução, o MySQL emitiu um erro porque a crm_read1conta 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 ROLEinstruçã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 REVOKEinstrução. A REVOKEdeclaraçã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_writefunçã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 ROLEinstrução:
DROP ROLE role_name[, role_name, ...];Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Semelhante à REVOKEdeclaração, a DROP ROLEdeclaração revoga funções de todas as contas de usuário às quais foram concedidas.
Por exemplo, para remover as funções crm_reade 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 CRMbanco 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_dev1conta de usuário para a crm_dev2conta 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 ROLEinstrução para criar coleções nomeadas de privilégios. - Use a
GRANTinstrução para conceder privilégios a uma função. - Use a
SHOW GRANTSinstrução para exibir os privilégios concedidos de uma função. - Use a
REVOKEinstrução para revogar privilégios de uma função. - Use a
DROP ROLEinstrução para excluir uma função.
