Mengamankan dan mengontrol akses ke data aplikasi menggunakan tampilan aman berparameter

Menyiapkan lingkungan Anda

Untuk bersiap menjalankan kueri pada tampilan aman berparameter, Anda harus terlebih dahulu menyiapkan tampilan berparameter, database dan peran database, ekstensi parameterized_view, dan skema aplikasi.

Menyiapkan tampilan berparameter dan database

1. Minta tim AlloyDB untuk mengaktifkan flag parameterized_views.enabled database, yang memuat library ekstensi yang diperlukan.

2. Buat database bernama database untuk data aplikasi dan tampilan berparameter. Untuk mengetahui informasi selengkapnya, lihat Membuat database.

Membuat peran database, ekstensi, dan skema aplikasi

1. Dengan menggunakan AlloyDB Studio atau psql, hubungkan ke database sebagai pengguna postgres atau sebagai pengguna dengan hak istimewa superuser AlloyDB.

   psql database -U postgres
   

   Untuk mengetahui informasi selengkapnya, lihat Tentang pengelolaan pengguna database di AlloyDB.

2. Buat ekstensi parameterized_views di database.

   -- Requires parameterized_views.enabled set to true
   CREATE EXTENSION parameterized_views;
   

   Saat ekstensi dibuat, sistem juga membuat skema bernama parameterized_views sehingga API dimuat dalam namespace skema tersebut dan API tersebut tidak berkonflik dengan API yang ada.

3. Buat peran administratif AlloyDB, yang memiliki dan mengelola database.

   CREATE ROLE admin_user WITH LOGIN PASSWORD '...';
    GRANT ALL PRIVILEGES ON DATABASE database TO admin_user;
   

   Untuk informasi selengkapnya, lihat CREATE USER.

4. Buat peran database baru untuk menjalankan kueri terhadap tampilan aman yang diparameterkan. Ini adalah peran AlloyDB yang digunakan aplikasi untuk terhubung dan login ke database untuk menjalankan kueri dengan akses terbatas ke fungsi atau objek publik ke set minimum yang diperlukan.

   CREATE ROLE psv_user WITH LOGIN PASSWORD '...';
   

   Untuk informasi selengkapnya, lihat CREATE USER.

5. Hubungkan sebagai pengguna administratif.

   SET role TO admin_user;
   

6. Buat skema yang berisi tabel.

   CREATE SCHEMA schema;
   

7. Buat tabel dan masukkan data.

   CREATE TABLE schema.checked_items(bag_id INT,timestamp TIMESTAMP, loc_code CHAR(3), scan_type CHAR(1), location TEXT, customer_id INT);
   
   INSERT INTO schema.checked_items (bag_id, timestamp, loc_code, scan_type, location, customer_id) VALUES
   (101, '2023-10-26 10:00:00', 'ABC', 'I', 'Warehouse A', 123),
   (102, '2023-10-26 10:15:30', 'DEF', 'O', 'Loading Dock B', 456),
   (103, '2023-10-26 10:30:45', 'GHI', 'I', 'Conveyor Belt 1', 789),
   (104, '2023-10-26 11:00:00', 'JKL', 'O', 'Shipping Area C', 101),
   (105, '2023-10-26 11:45:15', 'MNO', 'I', 'Sorting Station D', 202),
   (106, '2023-10-26 12:00:00', 'PQR', 'O', 'Truck Bay E', 303);
   

Membuat tampilan berparameter yang aman dan menyiapkan hak akses

Untuk membuat tampilan berparameter yang aman dan menyiapkan hak akses yang sesuai untuk tabel dan tampilan dasar, ikuti langkah-langkah berikut:

1. Menggunakan AlloyDB Studio atau psql, hubungkan ke database sebagai admin_user.

   psql database -U admin_user
   

2. Untuk memberikan akses terbatas ke tampilan, buat tampilan aman berparameter:

   CREATE VIEW schema.secure_checked_items WITH (security_barrier) AS
   SELECT bag_id, timestamp, location
   FROM schema.checked_items t
   WHERE customer_id = $@app_end_userid;
   

3. Memberikan akses ke tampilan.

   GRANT SELECT ON schema.secure_checked_items TO psv_user;
   

4. Untuk mengakses tampilan, berikan akses ke skema.

   GRANT USAGE ON SCHEMA schema TO psv_user;
   

5. Mencabut akses langsung ke tabel dasar.

   REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;
   

Memverifikasi keamanan data

Untuk memverifikasi bahwa tampilan aman berparameter membatasi akses ke tampilan yang ditentukan, jalankan perintah berikut sebagai psv_user. Ini adalah peran database AlloyDB yang digunakan aplikasi untuk terhubung dan login ke database untuk menjalankan kueri.

1. Menghubungkan sebagai pengguna tampilan aman berparameter.

   psql database -U psv_user
   

2. Pastikan tabel dasar tidak dapat diakses.

   SELECT * FROM schema.checked_items;
   ERROR:  permission denied for table checked_items
   

3. Akses tampilan aman berparameter menggunakan fungsi execute_parameterized_query:

   SELECT * FROM parameterized_views.execute_parameterized_query(
     query => 'SELECT * from schema.secure_checked_items',
     param_names => ARRAY ['app_end_userid'],
     param_values => ARRAY ['303']
   );
   

4. Untuk meningkatkan keamanan kueri yang dibuat dengan natural language, integrasikan tampilan aman berparameter Anda menggunakan natural language AlloyDB AI.