Object Privileges

When a database object is created, it is assigned an owner. The owner is by default the DW user that runs the creation statement.

Currently, Relyt provides the following types of database objects: tables, views, functions (including procedures), schemas. For most kinds of objects, the initial state is that only the owner has full privileges on the object. To allow other DW users to use it, privileges must be granted. Relyt supports the following privileges for each object type.

Object Type	Privileges
Tables, external tables, views	SELECT INSERT UPDATE DELETE TRUNCATE ALL
Columns	SELECT INSERT UPDATE ALL
Databases	CREATE CONNECT TEMPORARY TEMP ALL
Functions	EXECUTE ALL
Schemas	CREATE USAGE ALL

note

You must grant privileges for each object individually. For example, granting ALL on a database to a DW user does not means that the DW user has full access to the objects in the database. The DW user is granted only the database-level privileges (CREATE, CONNECT, TEMPORARY, TEMP) on the database itself.

To grant a DW user privileges on a database object, use the GRANT command. For example, grant DW user [email protected] the UPDATE privilege on table table1:

GRANT UPDATE ON table1 TO "[email protected]";

One more example, grant DW user [email protected] the SELECT and UPDATE privileges to column col1 in table table2:

GRANT SELECT(col1), UPDATE(col1) ON table2 TO "[email protected]";

To revoke privileges, use the REVOKE command. For example, revoke the DELETE privilge on table table1 from DW user [email protected]:

REVOKE DELETE on table1 FROM "[email protected]";

To revoke privileges granted to a dropped DW user, use DROP OWNED, for example:

DROP OWNED BY "[email protected]";