Didacticiel : Création de rôles et interrogation avec RBAC

Pour créer le rôle d’administrateur db_admin, utilisez l’exemple suivant.

CREATE ROLE db_admin;

Pour créer un utilisateur de base de données nommé dbadmin, utilisez l’exemple suivant.

CREATE USER dbadmin PASSWORD 'Test12345';

Pour attribuer le rôle défini par le système nommé sys:dba au rôle db_admin, utilisez l’exemple suivant. Lorsque le rôle sys:dba est attribué, l’utilisateur dbadmin peut créer des schémas et des tables. Pour plus d’informations, consultez Rôles définis par le système Amazon Redshift.

Établissez une connexion à la base de données dev en tant qu’utilisateur dbadmin à l’aide de l’éditeur de requête v2. Pour plus d’informations sur la connexion à une base de données, consultez Utilisation de l’éditeur de requête v2.

Pour créer les schémas de la base de données de vente et de marketing, utilisez l’exemple suivant.

CREATE SCHEMA sales;
CREATE SCHEMA marketing;

Pour créer et insérer des valeurs dans les tables du schéma de vente, utilisez l’exemple suivant.

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);

Pour créer et insérer des valeurs dans les tables du schéma de marketing, utilisez l’exemple suivant.

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);

Établissez une connexion à la base de données en tant qu’utilisateur dbadmin.

Pour créer le rôle sales_ro, utilisez l’exemple suivant.

CREATE ROLE sales_ro;

Pour créer l’utilisateur salesanalyst, utilisez l’exemple suivant.

CREATE USER salesanalyst PASSWORD 'Test12345';

Pour accorder au rôle sales_ro l’accès d’utilisation et de sélection des objets du schéma de vente, utilisez l’exemple suivant.

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

Pour attribuer le rôle sales_ro à l’utilisateur salesanalyst, utilisez l’exemple suivant.

GRANT ROLE sales_ro TO salesanalyst;

Établissez une connexion à la base de données en tant qu’utilisateur salesanalyst.

Pour trouver les 10 ventes avec les commissions les plus élevées, utilisez l’exemple suivant.

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      |
+--------+------------+----------+

Pour sélectionner 10 événements dans la table des événements du schéma de vente, utilisez l’exemple suivant.

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 |
+---------+---------+-------+--------+--------------------+---------------------+

Pour tenter de mettre à jour le nom de l’événement avec eventid 1, exécutez l’exemple suivant. Cet exemple provoquera une erreur de refus d’autorisation car l’utilisateur salesanalyst ne dispose que des autorisations SELECT sur la table des événements du schéma de vente. Pour mettre à jour la table des événements, vous devez donner l’autorisation UPDATE au rôle sales_ro. Pour plus d’informations sur la manière de donner l’autorisation de mettre à jour une table, consultez le paramètre UPDATE pour GRANT. Pour plus d’informations sur la commande UPDATE, consultez UPDATE.

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

Pour essayer de tout sélectionner dans la table des événements du schéma de marketing, utilisez l’exemple suivant. Cet exemple provoquera une erreur de refus d’autorisation car l’utilisateur salesanalyst ne dispose que des autorisations SELECT sur la table des événements du schéma de vente. Pour sélectionner des données dans la table des événements du schéma de marketing, vous devez accorder au rôle sales_ro les autorisations SELECT sur la table des événements du schéma de marketing.

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

Établissez une connexion à la base de données en tant qu’utilisateur dbadmin.

Pour créer le rôle sales_rw dans le schéma de vente, utilisez l’exemple suivant.

CREATE ROLE sales_rw;

Pour créer l’utilisateur salesengineer, utilisez l’exemple suivant.

CREATE USER salesengineer PASSWORD 'Test12345';

Pour accorder au rôle sales_rw l’accès d’utilisation et de sélection des objets du schéma de vente en lui attribuant le rôle sales_ro, utilisez l’exemple suivant. Pour plus d’informations sur la façon dont les rôles héritent des autorisations dans Amazon Redshift, consultez Hiérarchie des rôles.

GRANT ROLE sales_ro TO ROLE sales_rw;

Pour attribuer le rôle sales_rw à l’utilisateur salesengineer, utilisez l’exemple suivant.

GRANT ROLE sales_rw TO salesengineer;

Établissez une connexion à la base de données en tant qu’utilisateur salesengineer.

L’utilisateur salesengineer peut lire avec succès les données de la table des événements du schéma de vente. Pour sélectionner l’événement eventid1 dans la table des événements du schéma de vente, utilisez l’exemple suivant.

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

Pour essayer de tout sélectionner dans la table des événements du schéma de marketing, utilisez l’exemple suivant. L’utilisateur salesengineer n’étant pas autorisé à accéder aux tables du schéma de marketing, cette requête provoquera une erreur de refus d’autorisation. Pour sélectionner des données dans la table des événements du schéma de marketing, vous devez accorder au rôle sales_rw les autorisations SELECT sur la table des événements du schéma de marketing.

SELECT * FROM marketing.events;

ERROR: permission denied for schema marketing

Pour tenter de mettre à jour le nom de l’événement avec eventid 1, exécutez l’exemple suivant. Cet exemple provoquera une erreur de refus d’autorisation car l’utilisateur salesengineer ne dispose que des autorisations SELECT sur la table des événements du schéma de vente. Pour mettre à jour la table des événements, vous devez donner l’autorisation UPDATE au rôle sales_rw.

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

ERROR: permission denied for relation events

Établissez une connexion à la base de données en tant qu’utilisateur dbadmin.

Pour accorder les autorisations UPDATE, INSERT et DELETE au rôle sales_rw, utilisez l’exemple suivant.

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

Établissez une connexion à la base de données en tant qu’utilisateur salesengineer.

Pour mettre à jour le nom de l’événement avec eventid 1, exécutez l’exemple suivant.

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

Pour afficher la modification apportée dans la requête précédente, utilisez l’exemple suivant pour sélectionner l’événement eventid 1 dans la table des événements du schéma de vente.

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 |
+---------+---------+-------+--------+---------------+---------------------+

Pour analyser la table d’événements mise à jour dans le schéma de vente, utilisez l’exemple suivant. Cet exemple provoquera une erreur de refus d’autorisation car l’utilisateur salesengineer ne dispose pas des autorisations nécessaires et n’est pas le propriétaire de la table des événements dans le schéma de vente. Pour analyser la table des événements, vous devez accorder au rôle sales_rw l’autorisation ANALYZE à l’aide de la commande GRANT. Pour plus d’informations sur la commande ANALYZE, consultez ANALYSE.

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

Pour vider la table des événements mise à jour, utilisez l’exemple suivant. Cet exemple provoquera une erreur de refus d’autorisation car l’utilisateur salesengineer ne dispose pas des autorisations nécessaires et n’est pas le propriétaire de la table des événements dans le schéma de vente. Pour vider la table des événements, vous devez accorder au rôle sales_rw les autorisations VACUUM à l’aide de la commande GRANT. Pour plus d’informations sur la commande VACUUM, consultez VACUUM.

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

Établissez une connexion à la base de données en tant qu’utilisateur dbadmin.

Pour analyser la table des événements dans le schéma de vente, utilisez l’exemple suivant.

ANALYZE sales.events;

Pour vider la table des événements dans le schéma de vente, utilisez l’exemple suivant.

VACUUM sales.events;

Pour analyser la table des événements dans le schéma de marketing, utilisez l’exemple suivant.

ANALYZE marketing.events;

Pour vider la table des événements dans le schéma de marketing, utilisez l’exemple suivant.

VACUUM marketing.events;

Établissez une connexion à la base de données en tant qu’utilisateur salesengineer.

Pour essayer de supprimer toutes les lignes de la table des événements dans le schéma de vente, utilisez l’exemple suivant. Cet exemple provoquera une erreur car l’utilisateur salesengineer ne dispose pas des autorisations nécessaires et n’est pas le propriétaire de la table des événements dans le schéma de vente. Pour tronquer la table des événements, vous devez accorder au rôle sales_rw l’autorisation TRUNCATE à l’aide de la commande GRANT. Pour plus d’informations sur la commande TRUNCATE, consultez TRUNCATE.

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

Établissez une connexion à la base de données en tant qu’utilisateur dbadmin.

Pour accorder les privilèges de tronquer les tables au rôle sales_rw, utilisez l’exemple suivant.

GRANT TRUNCATE TABLE TO role sales_rw;

Établissez une connexion à la base de données en tant qu’utilisateur salesengineer à l’aide de query editor v2.

Pour lire les 10 premiers événements dans la table des événements du schéma de vente, utilisez l’exemple suivant.

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 |
+---------+---------+-------+--------+-----------------------------+---------------------+

Pour tronquer la table des événements dans le schéma de vente, utilisez l’exemple suivant.

TRUNCATE sales.events;

Pour lire les données de la table d’événements mise à jour dans le schéma de vente, utilisez l’exemple suivant.

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

Établissez une connexion à la base de données en tant qu’utilisateur dbadmin.

Pour créer des rôles en lecture seule et en lecture-écriture pour le schéma de marketing, utilisez l’exemple suivant.

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;

Pour afficher la liste des utilisateurs auxquels des rôles sont explicitement accordés dans le cluster, utilisez l’exemple suivant.

SELECT * FROM svv_user_grants;

Pour afficher la liste des rôles auxquels des rôles sont explicitement accordés dans le cluster, utilisez l’exemple suivant.

SELECT * FROM svv_role_grants;

Établissez une connexion à la base de données en tant qu’utilisateur salesengineer.

Pour afficher les 5 premières entrées de la table cat, utilisez l’exemple suivant.

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             |
+-------+----------+---------+---------------------------------+

Établissez une connexion à la base de données en tant qu’utilisateur dbadmin.

Pour créer une politique RLS pour la colonne catdesc de la table cat, utilisez l’exemple suivant.

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

Pour attacher la politique RLS au rôle sales_rw, utilisez l’exemple suivant.

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

Pour modifier la table afin d’activer la politique RLS, utilisez l’exemple suivant.

ALTER TABLE sales.cat ROW LEVEL SECURITY ON; 

Établissez une connexion à la base de données en tant qu’utilisateur salesengineer.

Pour tenter d’afficher les 5 premières entrées de la table cat, utilisez l’exemple suivant. Notez que seules les entrées apparaissent uniquement lorsque la colonne catdesc est Major League Baseball.

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

Établissez une connexion à la base de données en tant qu’utilisateur salesanalyst.

Pour tenter d’afficher les 5 premières entrées de la table cat, utilisez l’exemple suivant. Notez qu’aucune entrée n’apparaît car la politique par défaut de tout refuser est appliquée.

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

Établissez une connexion à la base de données en tant qu’utilisateur dbadmin.

Pour accorder l’autorisation IGNORE RLS au rôle sales_ro, utilisez l’exemple suivant. Cela donne à l’utilisateur salesanalyst l’autorisation d’ignorer les politiques RLS puisqu’il est membre du rôle sales_ro.

GRANT IGNORE RLS TO ROLE sales_ro; 

Établissez une connexion à la base de données en tant qu’utilisateur salesanalyst.

Pour afficher les 5 premières entrées de la table cat, utilisez l’exemple suivant.

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             |
+-------+----------+---------+---------------------------------+

Établissez une connexion à la base de données en tant qu’utilisateur dbadmin.

Pour révoquer l’autorisation IGNORE RLS du rôle sales_ro, utilisez l’exemple suivant.

REVOKE IGNORE RLS FROM ROLE sales_ro;

Établissez une connexion à la base de données en tant qu’utilisateur salesanalyst.

Pour tenter d’afficher les 5 premières entrées de la table cat, utilisez l’exemple suivant. Notez qu’aucune entrée n’apparaît car la politique par défaut de tout refuser est appliquée.

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

Établissez une connexion à la base de données en tant qu’utilisateur dbadmin.

Pour détacher la politique RLS de la table cat, utilisez l’exemple suivant.

DETACH RLS POLICY policy_mlb_engineer ON cat FROM ROLE sales_rw;

Établissez une connexion à la base de données en tant qu’utilisateur salesanalyst.

Pour tenter d’afficher les 5 premières entrées de la table cat, utilisez l’exemple suivant. Notez qu’aucune entrée n’apparaît car la politique par défaut de tout refuser est appliquée.

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             |
+-------+----------+---------+---------------------------------+

Établissez une connexion à la base de données en tant qu’utilisateur dbadmin.

Pour supprimer la politique RLS, utilisez l’exemple suivant.

DROP RLS POLICY policy_mlb_engineer;

Pour retirer RLS, utilisez l’exemple suivant.

ALTER TABLE cat ROW LEVEL SECURITY OFF;