Tutorial: Criar funções e consultar com o RBAC

Para criar a função de administrador db_admin, use o exemplo a seguir.

CREATE ROLE db_admin;

Para criar um usuário do banco de dados chamado dbadmin, use o exemplo a seguir.

CREATE USER dbadmin PASSWORD 'Test12345';

Para conceder a função definida pelo sistema chamada sys:dba à função db_admin, use o exemplo a seguir. Quando a função sys:dba é concedida, o usuário dbadmin pode criar esquemas e tabelas. Para ter mais informações, consulte Funções definidas pelo sistema do Amazon Redshift.

Conecte-se ao banco de dados dev como o usuário dbadmin utilizando o editor de consultas v2. Para ter mais informações sobre como se conectar a um banco de dados, consulte Trabalhar com o editor de consultas v2.

Para criar esquemas de bancos de dados de vendas e marketing, use o exemplo a seguir.

CREATE SCHEMA sales;
CREATE SCHEMA marketing;

Para criar e inserir valores em tabelas em um esquema de vendas, use o exemplo a seguir.

CREATE TABLE sales.cat(
catid smallint,
catgroup varchar(10),
catname varchar(10),
catdesc varchar(50)
);
INSERT INTO sales.cat(SELECT * FROM category);

CREATE TABLE sales.dates(
dateid smallint,
caldate date,
day char(3),
week smallint,
month char(5),
qtr char(5),
year smallint,
holiday boolean
);
INSERT INTO sales.dates(SELECT * FROM date);

CREATE TABLE sales.events(
eventid integer,
venueid smallint,
catid smallint,
dateid smallint,
eventname varchar(200),
starttime timestamp
);
INSERT INTO sales.events(SELECT * FROM event);

 CREATE TABLE sales.sale(
salesid integer,
listid integer,
sellerid integer,
buyerid integer,
eventid integer,
dateid smallint,
qtysold smallint,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp
);
INSERT INTO sales.sale(SELECT * FROM sales);

Para criar e inserir valores em tabelas em um esquema de marketing, use o exemplo a seguir.

CREATE TABLE marketing.cat(
catid smallint,
catgroup varchar(10),
catname varchar(10),
catdesc varchar(50)
);
INSERT INTO marketing.cat(SELECT * FROM category);

CREATE TABLE marketing.dates(
dateid smallint,
caldate date,
day char(3),
week smallint,
month char(5),
qtr char(5),
year smallint,
holiday boolean
);
INSERT INTO marketing.dates(SELECT * FROM date);

CREATE TABLE marketing.events(
eventid integer,
venueid smallint,
catid smallint,
dateid smallint,
eventname varchar(200),
starttime timestamp
);
INSERT INTO marketing.events(SELECT * FROM event);

CREATE TABLE marketing.sale(
marketingid integer,
listid integer,
sellerid integer,
buyerid integer,
eventid integer,
dateid smallint,
qtysold smallint,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp
);
INSERT INTO marketing.sale(SELECT * FROM marketing);

Conecte-se ao banco de dados como o usuário dbadmin.

Para criar a função sales_ro, use o exemplo a seguir.

CREATE ROLE sales_ro;

Para criar a função salesanalyst, use o exemplo a seguir.

CREATE USER salesanalyst PASSWORD 'Test12345';

Para conceder acesso de uso e seleção à função sales_ro aos objetos do esquema de vendas, use o exemplo a seguir.

GRANT USAGE ON SCHEMA sales TO ROLE sales_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO ROLE sales_ro;

Para conceder ao usuário salesanalyst a função sales_ro, use o exemplo a seguir.

GRANT ROLE sales_ro TO salesanalyst;

Conecte-se ao banco de dados como o usuário salesanalyst.

Para encontrar as dez vendas com as comissões mais altas, use o exemplo a seguir.

SET SEARCH_PATH TO sales;
SELECT DISTINCT events.dateid, sale.commission, cat.catname
FROM sale, events, dates, cat   
WHERE events.dateid=dates.dateid AND events.dateid=sale.dateid AND events.catid = cat.catid
ORDER BY 2 DESC LIMIT 10;
               
+--------+------------+----------+
| dateid | commission | catname  |
+--------+------------+----------+
|   1880 |     1893.6 | Pop      |
|   1880 |     1893.6 | Opera    |
|   1880 |     1893.6 | Plays    |
|   1880 |     1893.6 | Musicals |
|   1861 |       1500 | Plays    |
|   2003 |       1500 | Pop      |
|   1861 |       1500 | Opera    |
|   2003 |       1500 | Plays    |
|   1861 |       1500 | Musicals |
|   1861 |       1500 | Pop      |
+--------+------------+----------+

Para selecionar dez eventos na tabela de eventos no esquema de vendas, use o exemplo a seguir.

SELECT * FROM sales.events LIMIT 10;
               
+---------+---------+-------+--------+--------------------+---------------------+
| eventid | venueid | catid | dateid |     eventname      |      starttime      |
+---------+---------+-------+--------+--------------------+---------------------+
|    4836 |      73 |     9 |   1871 | Soulfest           | 2008-02-14 19:30:00 |
|    5739 |      41 |     9 |   1871 | Fab Faux           | 2008-02-14 19:30:00 |
|     627 |     229 |     6 |   1872 | High Society       | 2008-02-15 14:00:00 |
|    2563 |     246 |     7 |   1872 | Hamlet             | 2008-02-15 20:00:00 |
|    7703 |      78 |     9 |   1872 | Feist              | 2008-02-15 14:00:00 |
|    7903 |      90 |     9 |   1872 | Little Big Town    | 2008-02-15 19:30:00 |
|    7925 |     101 |     9 |   1872 | Spoon              | 2008-02-15 19:00:00 |
|    8113 |      17 |     9 |   1872 | Santana            | 2008-02-15 15:00:00 |
|     463 |     303 |     8 |   1873 | Tristan und Isolde | 2008-02-16 19:00:00 |
|     613 |     236 |     6 |   1873 | Pal Joey           | 2008-02-16 15:00:00 |
+---------+---------+-------+--------+--------------------+---------------------+

Para tentar atualizar eventname para eventid 1, execute o exemplo a seguir. Este exemplo gerará um erro de permissão negada porque o usuário salesanalyst só tem permissões SELECT na tabela de eventos no esquema de vendas. Para atualizar a tabela de eventos, você deve conceder permissões UPDATE à função sales_ro. Para obter mais informações sobre como conceder permissões para atualizar uma tabela, consulte o parâmetro UPDATE para GRANT. Para obter mais informações sobre o comando UPDATE, consulte UPDATE.

UPDATE sales.events
SET eventname = 'Comment event'
WHERE eventid = 1;
                  
ERROR: permission denied for relation events

Para tentar selecionar todos os eventos na tabela no esquema de marketing, use o exemplo a seguir. Este exemplo gerará um erro de permissão negada porque o usuário salesanalyst só tem permissões SELECT para a tabela de eventos no esquema de vendas. Para selecionar dados da tabela de eventos no esquema de marketing, você deve conceder à função sales_ro as permissões SELECT na tabela de eventos do esquema de marketing.

SELECT * FROM marketing.events;
               
               ERROR: permission denied for schema marketing

Conecte-se ao banco de dados como o usuário dbadmin.

Para criar a função sales_rw no esquema de vendas, use o exemplo a seguir.

CREATE ROLE sales_rw;

Para criar o usuário salesengineer, use o exemplo a seguir.

CREATE USER salesengineer PASSWORD 'Test12345';

Para conceder acesso de uso e seleção à função sales_rw aos objetos do esquema de vendas atribuindo a função sales_ro a ela, use o exemplo a seguir. Para obter mais informações sobre como as funções herdam permissões no Amazon Redshift, consulte Hierarquia de funções.

GRANT ROLE sales_ro TO ROLE sales_rw;

Para atribuir a função sales_rw ao usuário salesengineer, use o exemplo a seguir.

GRANT ROLE sales_rw TO salesengineer;

Conecte-se ao banco de dados como o usuário salesengineer.

O usuário salesengineer pode ler com êxito os dados da tabela de eventos do esquema de vendas. Para selecionar o evento com eventid 1 na tabela de eventos no esquema de vendas, use o exemplo a seguir.

SELECT * FROM sales.events where eventid=1;
                  
+---------+---------+-------+--------+-----------------+---------------------+
| eventid | venueid | catid | dateid |    eventname    |      starttime      |
+---------+---------+-------+--------+-----------------+---------------------+
|       1 |     305 |     8 |   1851 | Gotterdammerung | 2008-01-25 14:30:00 |
+---------+---------+-------+--------+-----------------+---------------------+

Para tentar selecionar todos os eventos na tabela no esquema de marketing, use o exemplo a seguir. Como o usuário salesengineer não tem permissões para tabelas no esquema de marketing, essa consulta gerará um erro de permissão negada. Para selecionar dados da tabela de eventos no esquema de marketing, você deve conceder à função sales_rw as permissões SELECT na tabela de eventos do esquema de marketing.

SELECT * FROM marketing.events;

ERROR: permission denied for schema marketing

Para tentar atualizar eventname para eventid 1, execute o exemplo a seguir. Este exemplo gerará um erro de permissão negada porque o usuário salesengineer só tem permissões de seleção na tabela de eventos no esquema de vendas. Para atualizar a tabela de eventos, você deve conceder permissões de UPDATE à função sales_rw.

UPDATE sales.events
SET eventname = 'Comment event'
WHERE eventid = 1;

ERROR: permission denied for relation events

Conecte-se ao banco de dados como o usuário dbadmin.

Para conceder as permissões UPDATE, INSERT e DELETE à função sales_rw, use o exemplo a seguir.

GRANT UPDATE, INSERT, ON ALL TABLES IN SCHEMA sales TO role sales_rw;

Conecte-se ao banco de dados como o usuário salesengineer.

Para atualizar eventname para eventid 1, execute o exemplo a seguir.

UPDATE sales.events
SET eventname = 'Comment event'
WHERE eventid = 1;

Para visualizar a alteração feita na consulta anterior, use o exemplo a seguir para selecionar o evento com eventid 1 na tabela de eventos no esquema de vendas.

SELECT * FROM sales.events WHERE eventid=1;

+---------+---------+-------+--------+---------------+---------------------+
| eventid | venueid | catid | dateid |   eventname   |      starttime      |
+---------+---------+-------+--------+---------------+---------------------+
|       1 |     305 |     8 |   1851 | Comment event | 2008-01-25 14:30:00 |
+---------+---------+-------+--------+---------------+---------------------+

Para analisar a tabela de eventos atualizada no esquema de vendas, use o exemplo a seguir. Este exemplo gerará um erro de permissão negada porque o usuário salesengineer não tem as permissões necessárias e não é o proprietário da tabela de eventos no esquema de vendas. Para analisar a tabela de eventos, você deve conceder permissões ANALYZE à função sales_rw usando o comando GRANT. Para obter mais informações sobre o comando ANALYZE, consulte ANALYZE.

ANALYZE sales.events;
               
               ERROR: skipping "events" --- only table or database owner can analyze

Para limpar a tabela de eventos atualizada, use o exemplo a seguir. Este exemplo gerará um erro de permissão negada porque o usuário salesengineer não tem as permissões necessárias e não é o proprietário da tabela de eventos no esquema de vendas. Para limpar a tabela de eventos, você deve conceder permissões VACUUM à função sales_rw usando o comando GRANT. Para obter mais informações sobre o comando VACUUM, consulte VACUUM.

VACUUM sales.events;
                  
ERROR: skipping "events" --- only table or database owner can vacuum it

Conecte-se ao banco de dados como o usuário dbadmin.

Para analisar a tabela de eventos no esquema de vendas, use o exemplo a seguir.

ANALYZE sales.events;

Para limpar a tabela de eventos no esquema de vendas, use o exemplo a seguir.

VACUUM sales.events;

Para analisar a tabela de eventos no esquema de marketing, use o exemplo a seguir.

ANALYZE marketing.events;

Para limpar a tabela de eventos no esquema de marketing, use o exemplo a seguir.

VACUUM marketing.events;

Conecte-se ao banco de dados como o usuário salesengineer.

Para tentar excluir todas as linhas na tabela de eventos no esquema de vendas, use o exemplo a seguir. Este exemplo gerará um erro porque o usuário salesengineer não tem as permissões necessárias e não é o proprietário da tabela de eventos no esquema de vendas. Para truncar a tabela de eventos, você deve conceder permissões TRUNCATE à função sales_rw usando o comando GRANT. Para obter mais informações sobre o comando TRUNCATE, consulte TRUNCATE.

TRUNCATE sales.events;
               
ERROR: must be owner of relation events

Conecte-se ao banco de dados como o usuário dbadmin.

Para conceder à função sales_rw privilégios para truncar a tabela, use o exemplo a seguir.

GRANT TRUNCATE TABLE TO role sales_rw;

Conecte-se ao banco de dados como o usuário salesengineer utilizando o editor de consultas v2.

Para ler os dez primeiros eventos na tabela de eventos no esquema de vendas, use o exemplo a seguir.

SELECT * FROM sales.events ORDER BY eventid LIMIT 10;
               
+---------+---------+-------+--------+-----------------------------+---------------------+
| eventid | venueid | catid | dateid |          eventname          |      starttime      |
+---------+---------+-------+--------+-----------------------------+---------------------+
|       1 |     305 |     8 |   1851 | Comment event               | 2008-01-25 14:30:00 |
|       2 |     306 |     8 |   2114 | Boris Godunov               | 2008-10-15 20:00:00 |
|       3 |     302 |     8 |   1935 | Salome                      | 2008-04-19 14:30:00 |
|       4 |     309 |     8 |   2090 | La Cenerentola (Cinderella) | 2008-09-21 14:30:00 |
|       5 |     302 |     8 |   1982 | Il Trovatore                | 2008-06-05 19:00:00 |
|       6 |     308 |     8 |   2109 | L Elisir d Amore            | 2008-10-10 19:30:00 |
|       7 |     309 |     8 |   1891 | Doctor Atomic               | 2008-03-06 14:00:00 |
|       8 |     302 |     8 |   1832 | The Magic Flute             | 2008-01-06 20:00:00 |
|       9 |     308 |     8 |   2087 | The Fly                     | 2008-09-18 19:30:00 |
|      10 |     305 |     8 |   2079 | Rigoletto                   | 2008-09-10 15:00:00 |
+---------+---------+-------+--------+-----------------------------+---------------------+

Para truncar a tabela de eventos no esquema de vendas, use o exemplo a seguir.

TRUNCATE sales.events;

Para ler os dados da tabela de eventos atualizada no esquema de vendas, use o exemplo a seguir.

SELECT * FROM sales.events ORDER BY eventid LIMIT 10;
               
+---------+---------+-------+--------+-----------------------------+---------------------+
| eventid | venueid | catid | dateid |          eventname          |      starttime      |
+---------+---------+-------+--------+-----------------------------+---------------------+

Conecte-se ao banco de dados como o usuário dbadmin.

Para criar funções somente leitura e leitura e gravação para o esquema de marketing, use o exemplo a seguir.

CREATE ROLE marketing_ro;

CREATE ROLE marketing_rw;

GRANT USAGE ON SCHEMA marketing TO ROLE marketing_ro, ROLE marketing_rw;

GRANT SELECT ON ALL TABLES IN SCHEMA marketing TO ROLE marketing_ro;

GRANT ROLE marketing_ro TO ROLE marketing_rw;

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA marketing TO ROLE marketing_rw;

CREATE USER marketinganalyst PASSWORD 'Test12345';

CREATE USER marketingengineer PASSWORD 'Test12345';

GRANT ROLE marketing_ro TO marketinganalyst;

GRANT ROLE marketing_rw TO marketingengineer;

Para visualizar uma lista de usuários aos quais se concedem funções no cluster explicitamente, use o exemplo a seguir.

SELECT * FROM svv_user_grants;

Para visualizar uma lista de funções às quais se concedem funções no cluster explicitamente, use o exemplo a seguir.

SELECT * FROM svv_role_grants;

Conecte-se ao banco de dados como o usuário salesengineer.

Para visualizar as primeiras cinco entradas na tabela cat, use o exemplo a seguir.

SELECT * 
FROM sales.cat
ORDER BY catid ASC
LIMIT 5;
                  
+-------+----------+---------+---------------------------------+
| catid | catgroup | catname |             catdesc             |
+-------+----------+---------+---------------------------------+
|     1 | Sports   | MLB     | Major League Baseball           |
|     2 | Sports   | NHL     | National Hockey League          |
|     3 | Sports   | NFL     | National Football League        |
|     4 | Sports   | NBA     | National Basketball Association |
|     5 | Sports   | MLS     | Major League Soccer             |
+-------+----------+---------+---------------------------------+

Conecte-se ao banco de dados como o usuário dbadmin.

Para criar uma política de RLS para a coluna catdesc na tabela cat, use o exemplo a seguir.

CREATE RLS POLICY policy_mlb_engineer
WITH (catdesc VARCHAR(50)) 
USING (catdesc = 'Major League Baseball');

Para anexar a política de RLS à função sales_rw, use o exemplo a seguir.

ATTACH RLS POLICY policy_mlb_engineer ON sales.cat TO ROLE sales_rw; 

Para alterar a tabela a fim de ativar a RLS, use o exemplo a seguir.

ALTER TABLE sales.cat ROW LEVEL SECURITY ON; 

Conecte-se ao banco de dados como o usuário salesengineer.

Para tentar visualizar as primeiras cinco entradas na tabela cat, use o exemplo a seguir. Observe que as entradas só aparecem quando a coluna catdesc é Major League Baseball.

SELECT * 
FROM sales.cat
ORDER BY catid ASC
LIMIT 5;
               
+-------+----------+---------+-----------------------+
| catid | catgroup | catname |        catdesc        |
+-------+----------+---------+-----------------------+
|     1 | Sports   | MLB     | Major League Baseball |
+-------+----------+---------+-----------------------+

Conecte-se ao banco de dados como o usuário salesanalyst.

Para tentar visualizar as primeiras cinco entradas na tabela cat, use o exemplo a seguir. Observe que nenhuma entrada aparece porque a política padrão de negar tudo é aplicada.

SELECT * 
FROM sales.cat
ORDER BY catid ASC
LIMIT 5;
               
+-------+----------+---------+-----------------------+
| catid | catgroup | catname |        catdesc        |
+-------+----------+---------+-----------------------+

Conecte-se ao banco de dados como o usuário dbadmin.

Para conceder a permissão IGNORE RLS à função sales_ro, use o exemplo a seguir. Isso concede ao usuário salesanalyst as permissões para ignorar as políticas de RLS, pois ele é membro da função sales_ro.

GRANT IGNORE RLS TO ROLE sales_ro; 

Conecte-se ao banco de dados como o usuário salesanalyst.

Para visualizar as primeiras cinco entradas na tabela cat, use o exemplo a seguir.

SELECT * 
FROM sales.cat
ORDER BY catid ASC
LIMIT 5;
               
+-------+----------+---------+---------------------------------+
| catid | catgroup | catname |             catdesc             |
+-------+----------+---------+---------------------------------+
|     1 | Sports   | MLB     | Major League Baseball           |
|     2 | Sports   | NHL     | National Hockey League          |
|     3 | Sports   | NFL     | National Football League        |
|     4 | Sports   | NBA     | National Basketball Association |
|     5 | Sports   | MLS     | Major League Soccer             |
+-------+----------+---------+---------------------------------+

Conecte-se ao banco de dados como o usuário dbadmin.

Para revogar a permissão IGNORE RLS da função sales_ro, use o exemplo a seguir.

REVOKE IGNORE RLS FROM ROLE sales_ro;

Conecte-se ao banco de dados como o usuário salesanalyst.

Para tentar visualizar as primeiras cinco entradas na tabela cat, use o exemplo a seguir. Observe que nenhuma entrada aparece porque a política padrão de negar tudo é aplicada.

SELECT * 
FROM sales.cat
ORDER BY catid ASC
LIMIT 5;
               
+-------+----------+---------+-----------------------+
| catid | catgroup | catname |        catdesc        |
+-------+----------+---------+-----------------------+

Conecte-se ao banco de dados como o usuário dbadmin.

Para desanexar a política de RLS da tabela cat, use o exemplo a seguir.

DETACH RLS POLICY policy_mlb_engineer ON cat FROM ROLE sales_rw;

Conecte-se ao banco de dados como o usuário salesanalyst.

Para tentar visualizar as primeiras cinco entradas na tabela cat, use o exemplo a seguir. Observe que nenhuma entrada aparece porque a política padrão de negar tudo é aplicada.

SELECT * 
FROM sales.cat
ORDER BY catid ASC
LIMIT 5;
               
+-------+----------+---------+---------------------------------+
| catid | catgroup | catname |             catdesc             |
+-------+----------+---------+---------------------------------+
|     1 | Sports   | MLB     | Major League Baseball           |
|     2 | Sports   | NHL     | National Hockey League          |
|     3 | Sports   | NFL     | National Football League        |
|     4 | Sports   | NBA     | National Basketball Association |
|     5 | Sports   | MLS     | Major League Soccer             |
+-------+----------+---------+---------------------------------+

Conecte-se ao banco de dados como o usuário dbadmin.

Para descartar a política de RLS, use o exemplo a seguir.

DROP RLS POLICY policy_mlb_engineer;

Para remover a RLS, use o exemplo a seguir.

ALTER TABLE cat ROW LEVEL SECURITY OFF;