Vertica Analytics Platform Version 10.0.x Documentation

Ownership and Implicit Privileges

All users have implicit privileges on the objects that they own. On creating an object, its owner automatically is granted all privileges associated with the object's type (see Database Object Privileges). Regardless of object type, the following privileges are inseparable from ownership and cannot be revoked, not even by the owner:

  • Authority to grant all object privileges to other users, and revoke them
  • ALTER (where applicable) and DROP
  • Extension of privilege granting authority on their objects to other users, and revoking that authority

Object owners can revoke all non-implicit, or ordinary, privileges from themselves. For example, on creating a table, its owner is automatically granted all implicit and ordinary privileges:

Implicit table privileges Ordinary table privileges
ALTER
DROP
DELETE
INSERT
REFERENCES
SELECT
TRUNCATE
UPDATE

If user Joan creates table t1, she can revoke ordinary privileges UPDATE and INSERT from herself, which effectively makes this table read-only:

=> \c - Joan
You are now connected as user "Joan".
=> CREATE TABLE t1 (a int);
CREATE TABLE
=> INSERT INTO t1 VALUES (1);
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT
=> REVOKE UPDATE, INSERT ON TABLE t1 FROM Joan;
REVOKE PRIVILEGE
=> INSERT INTO t1 VALUES (3);
ERROR 4367:  Permission denied for relation t1
=> SELECT * FROM t1;
 a
---
 1
(1 row)

Joan can subsequently restore UPDATE and INSERT privileges to herself:

=> GRANT UPDATE, INSERT on TABLE t1 TO Joan;
GRANT PRIVILEGE
dbadmin=> INSERT INTO t1 VALUES (3);
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT
dbadmin=> SELECT * FROM t1;
 a
---
 1
 3
(2 rows)