Release History
Requirements

Introduction

Quick onboard

Deployment

Data Modeling

Connecting

Write data

Migration

Query

Operations and Maintenance

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

User Privilege Control

YMatrix uses a role-based privilege management approach to simplify the administration of access rights. With roles, when a group of users requires the same set of privileges, you do not need to grant permissions individually to each user. Instead, grant the required privileges to a role and then assign that role to the group of users. Managing privileges through roles enables centralized control over the permissions of multiple users.

A role can be viewed as either a database user or a group of database users. Roles and users are essentially the same entity; the only difference is whether the LOGIN privilege is granted by default when the role is created. A role with the LOGIN privilege is considered a user. Roles can be assigned to users so that the users inherit all privileges of the role. A user can belong to multiple roles and thus hold combined privileges from those roles. Roles can also own database objects (such as tables, schemas, and sequences) and grant privileges on these objects to other roles. Use the GRANT and REVOKE statements to assign specific privileges on data objects to users.

  • ACL Privilege Abbreviations
Privilege Abbreviation Applicable Object Types
SELECT r ("read") LARGE OBJECT, SEQUENCE, TABLE (and table-like objects), table column
INSERT a ("append") TABLE, table column
UPDATE w ("write") LARGE OBJECT, SEQUENCE, TABLE, table column
DELETE d TABLE
TRUNCATE D TABLE
REFERENCES x TABLE, table column
TRIGGER t TABLE
CREATE C DATABASE, SCHEMA, TABLESPACE
CONNECT c DATABASE
TEMPORARY T DATABASE
EXECUTE X FUNCTION, PROCEDURE
USAGE U DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE
  • Summary of Access Privileges
Object Type All Privileges Default PUBLIC Privileges psql Command
DATABASE CTc Tc \l
DOMAIN U U \dD+
FUNCTION or PROCEDURE X X \df+
FOREIGN DATA WRAPPER U none \dew+
FOREIGN SERVER U none \des+
LANGUAGE U U \dL+
LARGE OBJECT rw none
SCHEMA UC none \dn+
SEQUENCE rwU none \dp
TABLE (and table-like objects) arwdDxt none \dp
Table column arwx none \dp
TABLESPACE C none \db+
TYPE U U \dT+

Examples

  • User miriam creates table mytable and executes the following set of GRANT statements:
GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
  • The ACL information for this table is as follows:
\dp mytable
                                 Access privileges
 Schema |  Name   | Type  |   Access privileges   |   Column privileges   | Policies
--------+---------+-------+-----------------------+-----------------------+----------
 public | mytable | table | miriam=arwdDxt/miriam+| col1:                +|
        |         |       | =r/miriam            +|   miriam_rw=rw/miriam |
        |         |       | admin=arw/miriam      |                       |
← Previous
Audit
Next →
MXUI Query Monitoring