Cấp đặc quyền (privileges) cho ROLE bằng lệnh PostgreSQL GRANT (Phần 1)

Bạn có thể thêm đặc quyền cho một role bằng lệnh GRANT.

Một số định dạng có sẵn cho từng mục đích khác nhau như sau:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT role_name [, ...] TO role_specification [, ...]
    [ WITH ADMIN OPTION ]
    [ GRANTED BY role_specification ]

where role_specification can be:

    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_USER
  | SESSION_USER

Có nhiều, nhưng định dạng cơ bản là:

GRANT permission ON target TO role_name;

Trong đó:

■ permission (quyền hạn) khác nhau tùy thuộc vào target được một mô tả ngắn gọn như bảng (★) sau:

■ target là một bảng, cột của bảng, cơ sở dữ liệu, lược đồ,…

■ role_name chỉ định tên role. Bạn cũng có thể chỉ định CURRENT_USER cho current user và SESSION_USER cho current session user. Nếu PUBLIC được chỉ định, các đặc quyền sẽ được thêm vào tất cả các roles hiện tại và tất cả các roles trong tương lai được thêm vào.

Các đặc quyền có thể được thêm vào các roles khác bởi owner và superuser của đối tượng được đề cập. Ngoài ra, nếu bạn thêm WITH GRANT OPTION khi thực thi lệnh GRANT thì role được đặc quyền có thể thêm cùng một đặc quyền cho role khác (tuy nhiên, không thể thêm nó khi PUBLIC được chỉ định).

Bây giờ cùng xem cách sử dụng lệnh GRANT một cách cụ thể hơn qua các mục trình bày tiếp theo.

Đầu tiên là thêm đặc quyền cho bảng:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

Ví dụ sau sẽ thêm các đặc quyền permission SELECT và INSERT trên target là bảng table_name cho role_name:

GRANT SELECT, INSERT ON table_name TO role_name;

Trường hợp muốn thêm tất cả các quyền như đã liệt kê ở bảng (★) thì dùng lệnh:

GRANT ALL [PRIVILEGES] ON table_name TO role_name;

Thêm quyền SELECT trên toàn bộ tables của lược đồ schema_name cho role_name:

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO role_name;

Thêm các đặc quyền SELECT và INSERT trên bảng table_name cho role_name và để cấp các đặc quyền tương tự cho những users khác:

GRANT SELECT, INSERT ON table_name TO role_name WITH GRANT OPTION;

Bây giờ sẽ đi vào ví dụ thực tế xem thế nào. Let’s Go!

Chúng ta sử dụng role name là nihongo kết nối vào cơ sở dữ liệu sampledata có một bảng tblclass trong lược đồ myschema và một bảng tblstudents trong lược đồ public như sau:

Và để xem điều gì xảy ra nếu chúng ta thực hiện SELECT dữ liệu của 2 bảng tblclass và tblstudents trước khi thêm đặc quyền SELECT trên 2 bảng này cho role name nihongo!??

SELECT * FROM tblstudents;
SELECT * FROM myschema.tblclass;

Vì role name nihongo không có quyền SELECT dữ liệu của 2 bảng tblclass và tblstudents nên việc lấy dữ liệu thất bại và hiển thị thông báo lỗi “ERROR: Access to table tblstudents is denied”

Bây giờ thêm quyền SELECT trên 2 bảng myschema.tblclass và public.tblstudents cho role name nihongo. Thực hiện các lệnh sau khi kết nối như một superuser, chẳng hạn là postgres.

GRANT SELECT ON tblstudents TO nihongo;
GRANT SELECT ON myschema.tblclass TO nihongo;

Quyền SELECT đã được thêm vào. Chạy lệnh \dp để xác nhận.

Các giá trị sau được đặt cho các đặc quyền truy cập của mỗi bảng:

Ý nghĩa là: role postgres đã thêm quyền r (SELECT) cho role nihongo.

Cách đọc các giá trị như sau :

rolename=xxxx -- Đặc quyền được gán cho rolename
=xxxx -- Đặc quyền được gán cho PUBLIC

            r -- SELECT（Read）
            w -- UPDATE（Write）
            a -- INSERT（Append）
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- Tất cả các đặc quyền (Dùng cho bảng, còn đối tượng khác thì sẽ khác)
            * -- Grant options for last permission

        /yyyy -- Role đã trao đặc quyền này

Bây giờ, sẽ SELECT lại dữ liệu từ bảng tblstudents sau khi kết nối với PostgreSQL với role là nihongo:

SELECT * FROM tblstudents;

Chúng ta đã có thể lấy dữ liệu từ bảng tblstudents.

Tương tự, hãy thử lấy dữ liệu từ bảng myschema.tblclass:

SELECT * FROM myschema.tblclass;

Lần này, lỗi “ERROR: Access to schema myschema is denied” đã được hiển thị và dữ liệu không thể được truy xuất. Nếu đây là lược đồ public, truy cập vào các đối tượng như bảng trong lược đồ được cho phép theo mặc định, nhưng nếu lược đồ không phải là public, thì phải cần thêm quyền USAGE.

Xem thêm: Đường dẫn tìm kiếm schema (lược đồ)

Bây giờ thêm quyền USAGE để được phép truy cập đối tượng của lược đồ myschema cho role name nihongo. Thực hiện lệnh sau khi kết nối như một superuser, chẳng hạn là postgres.

GRANT USAGE ON SCHEMA myschema TO nihongo;

Đã cấp quyền thành công để truy cập vào các đối tượng trong lược đồ myshema.

Thực hiện SELECT lại dữ liệu từ bảng tblclass:

SELECT * FROM myschema.tblclass;

Chúng ta đã có thể lấy dữ liệu từ bảng myschema.tblclass

Tiếp theo là khi bạn muốn thêm quyền cho cột của bảng. Định dạng lệnh:

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

Trước khi đi vào ví dụ minh họa, chúng ta sẽ clear, xóa đi đặc quyền SELECT đã cấp trên public.tblstudents cho role name nihongo đã cấp ở mục 2. Thêm quyền cho bảng bằng lệnh REVOKE sau khi kết nối như một superuser, chẳng hạn là postgres.

REVOKE SELECT ON tblstudents FROM nihongo;

Chi tiết về lệnh REVOKE: Xoá đặc quyền (privileges) khỏi ROLE bằng lệnh PostgreSQL REVOKE

Ví dụ: thêm đặc quyền SELECT trên cột column_name của bảng table_name cho role_name

GRANT SELECT (column_name) ON table_name TO role_name;

Khi sử dụng metacommand psql để kiểm tra các quyền trên bảng tblstudents và cột của nó, chúng ta thấy rằng role name nihongo không có quyền nào được thêm vào cho cột của bảng:

Bây giờ thêm đặc quyền SELECT trên cột name của bảng tblstudents cho role nihongo.

Thực hiện lệnh sau khi kết nối như một superuser, chẳng hạn là postgres.

GRANT SELECT (name) ON tblstudents TO nihongo;

Quyền SELECT đã được thêm vào. Chạy lệnh \dp để xác nhận.

Giá trị sau được đặt trong đặc quyền truy cập của cột:

Cách đọc giá trị cài đặt giống như ở trên và role postgres đã thêm quyền r (SELECT) cho role nihongo.

Bây giờ, thử truy vấn dữ liệu từ bảng tblstudents bằng lệnh SELECT sau khi kết nối với PostgreSQL bằng role nihongo:

SELECT * FROM tblstudents;

Vì ở chúng ta đã clear, xóa đi đặc quyền SELECT đã cấp trên public.tblstudents cho role name nihongo, nên việc lấy dữ liệu thất bại và hiển thị thông báo lỗi “ERROR: Access to table tblstudents is denied”

Nhưng riêng với cột name của bảng tblstudents, vì đã được cấp quyền truy vấn SELECT nên hoàn toàn có thể truy vấn lấy dữ liệu được bình thường cho dù không có quyền SELECT bảng tblstudents

SELECT name FROM tblstudents;

Thêm nữa là, cũng giống như ở phần 2. Thêm quyền cho bảng để lấy dữ liệu từ một cột của bảng được tạo trong một lược đồ khác với lược đồ public, cần có đặc quyền USAGE cho lược đồ đó.