Tutorial: Membuat peran dan menanyakan dengan RBAC

Untuk membuat peran administrator db_admin, gunakan contoh berikut.

CREATE ROLE db_admin;

Untuk membuat pengguna database bernama dbadmin, gunakan contoh berikut.

CREATE USER dbadmin PASSWORD 'Test12345';

Untuk memberikan peran yang ditentukan sistem bernama sys:dba ke peran db_admin, gunakan contoh berikut. Ketika diberikan peran sys:dba, pengguna dbadmin dapat membuat skema dan tabel. Untuk informasi selengkapnya, lihat Peran yang ditentukan sistem Amazon Redshift.

Hubungkan ke database dev sebagai pengguna dbadmin menggunakan editor kueri v2. Untuk informasi selengkapnya tentang menghubungkan ke database, lihat Bek erja dengan editor kueri v2.

Untuk membuat skema database penjualan dan pemasaran, gunakan contoh berikut.

CREATE SCHEMA sales;
CREATE SCHEMA marketing;

Untuk membuat dan menyisipkan nilai ke dalam tabel dalam skema penjualan, gunakan contoh berikut.

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

Untuk membuat dan memasukkan nilai ke dalam tabel dalam skema pemasaran, gunakan contoh berikut.

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

Hubungkan ke database sebagai pengguna dbadmin.

Untuk membuat peran sales_ro, gunakan contoh berikut.

CREATE ROLE sales_ro;

Untuk membuat pengguna salesAnalyst, gunakan contoh berikut.

CREATE USER salesanalyst PASSWORD 'Test12345';

Untuk memberikan penggunaan peran sales_ro dan memilih akses ke objek skema penjualan, gunakan contoh berikut.

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

Untuk memberikan peran sales_ro kepada pengguna salesanalis, gunakan contoh berikut.

GRANT ROLE sales_ro TO salesanalyst;

Hubungkan ke database sebagai pengguna analis penjualan.

Untuk menemukan 10 penjualan dengan komisi tertinggi, gunakan contoh berikut.

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

Untuk memilih 10 peristiwa dari tabel peristiwa dalam skema penjualan, gunakan contoh berikut.

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

Untuk mencoba memperbarui nama acara untuk eventid 1, jalankan contoh berikut. Contoh ini akan mengakibatkan kesalahan izin ditolak karena pengguna salesAnalyst hanya memiliki izin SELECT pada tabel peristiwa dalam skema penjualan. Untuk memperbarui tabel peristiwa, Anda harus memberikan izin peran sales_ro untuk UPDATE. Untuk informasi selengkapnya tentang pemberian izin untuk memperbarui tabel, lihat parameter UPDATE untukHIBAH. Untuk informasi selengkapnya tentang perintah UPDATE, lihatUPDATE.

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

Untuk mencoba memilih semua dari tabel peristiwa dalam skema pemasaran, gunakan contoh berikut. Contoh ini akan mengakibatkan kesalahan izin ditolak karena pengguna salesAnalyst hanya memiliki izin SELECT untuk tabel peristiwa dalam skema penjualan. Untuk memilih data dari tabel peristiwa dalam skema pemasaran, Anda harus memberikan izin SELECT peran sales_ro pada tabel peristiwa dalam skema pemasaran.

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

Hubungkan ke database sebagai pengguna dbadmin.

Untuk membuat peran sales_rw dalam skema penjualan, gunakan contoh berikut.

CREATE ROLE sales_rw;

Untuk membuat pengguna salesengineer, gunakan contoh berikut.

CREATE USER salesengineer PASSWORD 'Test12345';

Untuk memberikan penggunaan peran sales_rw dan memilih akses ke objek skema penjualan dengan menetapkan peran sales_ro padanya, gunakan contoh berikut. Untuk informasi selengkapnya tentang bagaimana peran mewarisi izin di Amazon Redshift, lihatHirarki peran.

GRANT ROLE sales_ro TO ROLE sales_rw;

Untuk menetapkan peran sales_rw ke pengguna salesengineer, gunakan contoh berikut.

GRANT ROLE sales_rw TO salesengineer;

Hubungkan ke database sebagai pengguna salesengineer.

Pengguna salesengineer dapat berhasil membaca data dari tabel peristiwa skema penjualan. Untuk memilih acara dengan eventid 1 dari tabel peristiwa dalam skema penjualan, gunakan contoh berikut.

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

Untuk mencoba memilih semua dari tabel peristiwa dalam skema pemasaran, gunakan contoh berikut. Pengguna salesengineer tidak memiliki izin untuk tabel dalam skema pemasaran, jadi kueri ini akan menghasilkan kesalahan izin ditolak. Untuk memilih data dari tabel peristiwa dalam skema pemasaran, Anda harus memberikan izin SELECT peran sales_rw pada tabel peristiwa dalam skema pemasaran.

SELECT * FROM marketing.events;

ERROR: permission denied for schema marketing

Untuk mencoba memperbarui nama acara untuk eventid 1, jalankan contoh berikut. Contoh ini akan mengakibatkan kesalahan izin ditolak karena pengguna salesengineer hanya memiliki izin pilih pada tabel peristiwa dalam skema penjualan. Untuk memperbarui tabel peristiwa, Anda harus memberikan izin peran sales_rw untuk UPDATE.

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

ERROR: permission denied for relation events

Hubungkan ke database sebagai pengguna dbadmin.

Untuk memberikan izin UPDATE, INSERT, dan DELETE ke peran sales_rw, gunakan contoh berikut.

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

Hubungkan ke database sebagai pengguna salesengineer.

Untuk memperbarui nama acara untuk eventid 1, jalankan contoh berikut.

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

Untuk melihat perubahan yang dibuat dalam kueri sebelumnya, gunakan contoh berikut untuk memilih acara dengan eventid 1 dari tabel peristiwa dalam skema penjualan.

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

Untuk menganalisis tabel peristiwa yang diperbarui dalam skema penjualan, gunakan contoh berikut. Contoh ini akan mengakibatkan kesalahan izin ditolak karena pengguna salesengineer tidak memiliki izin yang diperlukan dan bukan pemilik tabel peristiwa dalam skema penjualan. Untuk menganalisis tabel peristiwa, Anda harus memberikan izin peran sales_rw untuk ANALYZE menggunakan perintah GRANT. Untuk informasi selengkapnya tentang perintah ANALYZE, lihatMENGANALISA.

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

Untuk menyedot debu tabel peristiwa yang diperbarui, gunakan contoh berikut. Contoh ini akan mengakibatkan kesalahan izin ditolak karena pengguna salesengineer tidak memiliki izin yang diperlukan dan bukan pemilik tabel peristiwa dalam skema penjualan. Untuk menyedot debu tabel peristiwa, Anda harus memberikan izin peran sales_rw ke VACUUM menggunakan perintah GRANT. Untuk informasi selengkapnya tentang perintah VACUUM, lihatVAKUM.

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

Hubungkan ke database sebagai pengguna dbadmin.

Untuk menganalisis tabel peristiwa dalam skema penjualan, gunakan contoh berikut.

ANALYZE sales.events;

Untuk menyedot tabel peristiwa dalam skema penjualan, gunakan contoh berikut.

VACUUM sales.events;

Untuk menganalisis tabel peristiwa dalam skema pemasaran, gunakan contoh berikut.

ANALYZE marketing.events;

Untuk menyedot tabel peristiwa dalam skema pemasaran, gunakan contoh berikut.

VACUUM marketing.events;

Hubungkan ke database sebagai pengguna salesengineer.

Untuk mencoba menghapus semua baris dari tabel peristiwa dalam skema penjualan, gunakan contoh berikut. Contoh ini akan mengakibatkan kesalahan karena pengguna salesengineer tidak memiliki izin yang diperlukan dan bukan pemilik tabel peristiwa dalam skema penjualan. Untuk memotong tabel peristiwa, Anda harus memberikan izin peran sales_rw untuk TRUNCATE menggunakan perintah GRANT. Untuk informasi selengkapnya tentang perintah TRUNCATE, lihatMEMOTONG.

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

Hubungkan ke database sebagai pengguna dbadmin.

Untuk memberikan hak istimewa tabel terpotong ke peran sales_rw, gunakan contoh berikut.

GRANT TRUNCATE TABLE TO role sales_rw;

Hubungkan ke database sebagai pengguna salesengineer menggunakan editor kueri v2.

Untuk membaca 10 peristiwa pertama dari tabel peristiwa dalam skema penjualan, gunakan contoh berikut.

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

Untuk memotong tabel peristiwa dalam skema penjualan, gunakan contoh berikut.

TRUNCATE sales.events;

Untuk membaca data dari tabel peristiwa yang diperbarui dalam skema penjualan, gunakan contoh berikut.

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

Hubungkan ke database sebagai pengguna dbadmin.

Untuk membuat peran baca-saja dan baca-tulis untuk skema pemasaran, gunakan contoh berikut.

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;

Untuk melihat daftar pengguna yang secara eksplisit diberikan peran dalam cluster, gunakan contoh berikut.

SELECT * FROM svv_user_grants;

Untuk melihat daftar peran yang secara eksplisit diberikan peran dalam cluster, gunakan contoh berikut.

SELECT * FROM svv_role_grants;

Hubungkan ke database sebagai salesengineer pengguna.

Untuk melihat 5 entri pertama dalam cat tabel, gunakan contoh berikut.

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

Hubungkan ke database sebagai dbadmin pengguna.

Untuk membuat kebijakan RLS untuk catdesc kolom dalam cat tabel, gunakan contoh berikut.

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

Untuk melampirkan kebijakan RLS ke sales_rw peran, gunakan contoh berikut.

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

Untuk mengubah tabel untuk mengaktifkan RLS, gunakan contoh berikut.

ALTER TABLE sales.cat ROW LEVEL SECURITY ON; 

Hubungkan ke database sebagai salesengineer pengguna.

Untuk mencoba melihat 5 entri pertama dalam cat tabel, gunakan contoh berikut. Perhatikan bahwa hanya entri yang hanya muncul ketika catdesc kolom adaMajor League Baseball.

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

Hubungkan ke database sebagai salesanalyst pengguna.

Untuk mencoba melihat 5 entri pertama dalam cat tabel, gunakan contoh berikut. Perhatikan bahwa tidak ada entri yang muncul karena kebijakan tolak semua default diterapkan.

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

Hubungkan ke database sebagai dbadmin pengguna.

Untuk memberikan izin IGNORE RLS ke sales_ro peran, gunakan contoh berikut. Ini memberi salesanalyst pengguna izin untuk mengabaikan kebijakan RLS karena mereka adalah anggota sales_ro peran.

GRANT IGNORE RLS TO ROLE sales_ro; 

Hubungkan ke database sebagai salesanalyst pengguna.

Untuk melihat 5 entri pertama dalam cat tabel, gunakan contoh berikut.

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

Hubungkan ke database sebagai dbadmin pengguna.

Untuk mencabut izin IGNORE RLS dari sales_ro peran, gunakan contoh berikut.

REVOKE IGNORE RLS FROM ROLE sales_ro;

Hubungkan ke database sebagai salesanalyst pengguna.

Untuk mencoba melihat 5 entri pertama dalam cat tabel, gunakan contoh berikut. Perhatikan bahwa tidak ada entri yang muncul karena kebijakan tolak semua default diterapkan.

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

Hubungkan ke database sebagai dbadmin pengguna.

Untuk melepaskan kebijakan RLS dari cat tabel, gunakan contoh berikut.

DETACH RLS POLICY policy_mlb_engineer ON cat FROM ROLE sales_rw;

Hubungkan ke database sebagai salesanalyst pengguna.

Untuk mencoba melihat 5 entri pertama dalam cat tabel, gunakan contoh berikut. Perhatikan bahwa tidak ada entri yang muncul karena kebijakan tolak semua default diterapkan.

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

Hubungkan ke database sebagai dbadmin pengguna.

Untuk menghapus kebijakan RLS, gunakan contoh berikut.

DROP RLS POLICY policy_mlb_engineer;

Untuk menghapus RLS, gunakan contoh berikut.

ALTER TABLE cat ROW LEVEL SECURITY OFF;