Skip to main content

​
Overview

In Oxla, ownership defines the relationship where objects such as databases, tables and schemas belong to a specific role. Keep the following principles in mind regarding ownership:
  • Indexes do not have explicit owners; the owner of the table also owns its indexes
  • Ownership is required to DROP an object
  • For grants validation, the owner implicitly has all privileges on the resource:
    • For table: SELECT, INSERT, UPDATE, DELETE
    • For schema: USAGE, CREATE
This section explains how to check and change ownership and clarifies the differences between ownership and role privileges.

​
Checking Ownership

To check ownerships in Oxla, a superuser can execute the following query: 
SELECT * FROM oxla_internal.oxla_object_owner;
Example output: 
 id | database | schema |   object_name   | object_type
----+----------+--------+-----------------+-------------
  5 | oxla     | public | data_types_demo | TABLE
Here’s the breakdown of the above output:
  • id: role ID
  • database: database name
  • schema: schema name (empty if object_type is DATABASE)
  • object_name: object name (empty if object_type is SCHEMA or DATABASE)
  • object_type: type of the object

​
Changing Ownership

To change ownership, use the following syntax: 
ALTER [ TABLE | SCHEMA | DATABASE ] OBJECT_NAME OWNER TO ROLE_NAME;
where:
  • OBJECT_NAME: name of the object, whose ownership they want to change
  • ROLE_NAME: name of the role that will become the new owner of the specified object, or keyword CURRENT_ROLE/CURRENT_USER

​
Ownership vs Role Privileges

Unlike PostgreSQL, Oxla treats ownership and grants as independent. While owners implicitly have all privileges on their resources, these privileges:
  • Are not visible in oxla_internal.oxla_role_ns_grants or oxla_internal.oxla_role_table_grants
  • Cannot be revoked
GRANT or REVOKE operations can still be performed on object owner - they will result in creating or removing entries in oxla_internal.oxla_role_..._grants tables, which are independent of data stored in oxla_internal.oxla_object_owner. These grants do not matter anything as long as the user is the owner of a given resource, but they will take effect when the owner is changed.

​
Examples

Here are a few examples that demonstrate the behaviours described above, assuming there is a table1 and user1 role with USAGE grant in public schema:
  • After the following operations user1 will no longer be the owner of table1, but will have SELECT grant on that table. 
    ALTER TABLE table1 OWNER TO user1;
GRANT SELECT ON table1 TO user1;
ALTER TABLE table1 OWNER TO oxla;
  • After the following operations user1 will still be able to SELECT from table1 because of ownership, however REVOKE does not change anything. 
    ALTER TABLE table1 OWNER TO user1;
REVOKE SELECT ON table1 FROM user1;
  • After the following operations user1 will not have access to table1, however the owner has been changed and grant has been revoked. 
    ALTER TABLE table1 OWNER TO user1;
GRANT SELECT ON table1 TO user1;
REVOKE SELECT ON table1 FROM user1;
ALTER TABLE table1 OWNER TO oxla;