PostgreSQL

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

Cấp đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (Phần 1)
Được viết bởi Minh Hoàng

Series chia sẻ về PostgreSQL – Cơ sở dữ liệu mã nguồn mở tiên tiến nhất thế giới.

Bài viết này sẽ mô tả cách sử dụng câu lệnh PostgreSQL GRANT để cấp các đặc quyền trên các đối tượng cơ sở dữ liệu cho một role. Đặc quyền bao gồm tạo tablesschemas, lấy dữ liệu từ tables và thêm dữ liệu,…

1. Giới thiệu về lệnh thêm quyền PostgreSQL GRANT

1. Giới thiệu về lệnh thêm quyền PostgreSQL GRANT

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:

Quyền hạn Mô tả
SELECT Cho phép lấy dữ liệu của tablesviews, cần thiết để UPDATEDELETE
INSERT Cho phép thêm dữ liệu mới vào bảng
UPDATE Cho phép cập nhật dữ liệu bảng
DELETE Cho phép xóa dữ liệu của bảng
TRUNCATE Cho phép dữ liệu bảng có thể để trống (empty)
REFERENCES Cho phép tạo các ràng buộc khóa ngoại (foreign key) trên bảng và cột (columns)
TRIGGER Cho phép tạo một trigger trên bảng
CREATE Nếu target là cơ sở dữ liệu, bạn có thể tạo một lược đồ (schema). Nếu target là một schema, bạn có thể tạo các đối tượng như bảng
CONNECT Cho phép kết nối với cơ sở dữ liệu được chỉ định
TEMPORARY Cho phép tạo các bảng tạm thời trong cơ sở dữ liệu
EXECUTE Cho phép sử dụng các functions, proceduresoperators
USAGE Nếu target là một lược đồ (schema), cho phép truy cập vào đối tượng. Nếu target khác với mục tiêu đó, quyền hạn theo mục tiêu được cấp
ALL [PRIVILEGES] Cho phép tất cả các quyền có sẵn

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 userSESSION_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 ownersuperuser 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.

2. Thêm quyền cho bảng

2. Thêm quyền cho bảng

Đầ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 SELECTINSERT 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 SELECTINSERT 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:

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

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

Cấp đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (2)

Vì role name nihongo không có quyền SELECT dữ liệu của 2 bảng tblclasstblstudents 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.tblclasspublic.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;

Cấp đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (3)

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

Cấp đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (4)

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

postgres=arwdDxt/postgres+
nihongo=r/postgres

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

Cấp đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (5)

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;

Cấp đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (6)

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 đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (7)

Đã 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;

Cấp đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (8-1)

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

3. Thêm đặc quyền cho các cột trong bảng

3. Thêm đặc quyền cho các cột trong bảng

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;

Cấp đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (8-2)

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:

Cấp đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (9)

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;

Cấp đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (10)

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

Cấp đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (11)

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

name:                     +
nihongo=r/postgres

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;

Cấp đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (12)

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;

Cấp đặc quyền (privileges) cho role bằng lệnh PostgreSQL GRANT (13)

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 đồ đó.

Xem thêm: Cấp đặc quyền (privileges) cho ROLE bằng lệnh PostgreSQL GRANT (Phần 2)

Cảm ơn bạn đã theo dõi. Đừng ngần ngại hãy cùng thảo luận với chúng tôi!

Giới thiệu

Minh Hoàng

Xin chào, tôi là Hoàng Ngọc Minh, hiện đang làm BrSE, tại công ty Toyota, Nhật Bản. Những gì tôi viết trên blog này là những trải nghiệm thực tế tôi đã đúc rút ra được trong cuộc sống, quá trình học tập và làm việc. Các bài viết được biên tập một cách chi tiết, linh hoạt để giúp bạn đọc có thể tiếp cận một cách dễ dàng nhất. Hi vọng nó sẽ có ích hoặc mang lại một góc nhìn khác cho bạn[...]

2 bình luận

Translate »