Materialized View Privilege Summary

A list of the system privileges and object permissions required to complete the various materialized view tasks.

Creating a Materialized View

Database Version Materialized View Privileges
SAP Sybase IQ 15.3 and 15.4 Materialized view to be owned by self –
  • Requires RESOURCE authority with SELECT permission on tables in the view definition.
  • Requires CREATE permission on the selected dbspace to create a view owned by you.
Materialized view to be owned by any user – Requires DBA authority.
SAP Sybase IQ 16.0 Materialized view to be owned by self –
  • Requires CREATE MATERIALIZED VIEW system privilege.
  • Also requires one of:
    • CREATE ANY OBJECT system privilege.
    • CREATE object permission on the dbspace where the materialized view is being created.
  • Also requires one of:
    • SELECT ANY TABLE system privilege.
    • SELECT object permission on the underlying tables of the materialized view.

Materialized view to be owned by any user –

  • Requires CREATE object permission on the dbspace where the materialized view is being created.
  • Also requires one of:
    • CREATE ANY MATERIALIZED VIEW system privilege.
    • CREATE ANY OBJECT system privilege.
  • And also requires one of:
    • SELECT ANY TABLE system privilege.
    • SELECT object permission on the underlying tables of the materialized view.

Viewing Materialized View Data in the Execute SQL Window

Database Version Materialized View Privileges
SAP Sybase IQ 15.3 and 15.4

None required. Query executes with user's current permissions.

SAP Sybase IQ 16.0

None required. Query executes with user's current permissions.

Truncating Materialized View Data

Database Version Materialized View Privileges
SAP Sybase IQ 15.3 and 15.4 Requires one of:
  • DBA authority.
  • You own the materialized view.
SAP Sybase IQ 16.0 Requires one of:
  • TRUNCATE ANY TABLE system privilege.
  • ALTER ANY TABLE system privilege.
  • ALTER ANY OBJECT system privilege.
  • TRUNCATE permission on the materialized view.
  • You own the materialized view.

Validating Materialized View Data

Database Version Materialized View Privileges
SAP Sybase IQ 15.3 and 15.4 Requires one of:
  • DBA authority.
  • You own the materialized view.
SAP Sybase IQ 16.0 Requires VALIDATE ANY OBJECT system privilege.

Refreshing Materialized View Data

Database Version Materialized View Privileges
SAP Sybase IQ 15.3 and 15.4 Requires DBA authority.

Also requires both of:

  • INSERT permission on the underlying tables of the materialized view or you own the materialized view.
  • SELECT permission on the underlying tables of the materialized view or you own the underlying tables of the materialized view.
SAP Sybase IQ 16.0 Requires one of:
  • INSERT ANY TABLE system privilege.
  • INSERT permission on the materialized view.
  • You own the materialized view.
Also requires one of:
  • SELECT ANY TABLE system privilege.
  • SELECT permission on the underlying tables of the materialized view.
  • You own the underlying tables of the materialized view.

Recompiling and Enabling Materialized View Data

Database Version Materialized View Privileges
SAP Sybase IQ 15.3 and 15.4 Requires one of:
  • DBA authority.
  • You own the materialized view and have one of the following:
    • SELECT permission on the underlying tables of the materialized view.
    • You own the underlying tables of the view.
SAP Sybase IQ 16.0 Requires one of:
  • ALTER ANY MATERIALIZED VIEW system privilege.
  • ALTER ANY OBJECT system privilege.
  • You own the materialized view.
Also requires one of:
  • SELECT ANY TABLE system privilege.
  • SELECT permission on the underlying tables of the materialized view.
  • You own the underlying tables of the materialized view.

Disabling Materialized View Data

Database Version Materialized View Privileges
SAP Sybase IQ 15.3 and 15.4 Requires one of:
  • DBA authority.
  • You own the materialized view.
SAP Sybase IQ 16.0 Requires one of:
  • ALTER ANY MATERIALIZED VIEW system privilege.
  • ALTER ANY OBJECT system privilege.
  • You own the materialized view.

Deleting a Materialized View

Database Version Materialized View Privileges
SAP Sybase IQ 15.3 and 15.4 Requires DBA authority.
SAP Sybase IQ 16.0 Requires one of:
  • DROP ANY MATERIALIZED VIEW system privilege.
  • DROP ANY OBJECT system privilege.
  • You own the materialized view.

Generating Materialized View DDL Commands

Database Version Materialized View Privileges
SAP Sybase IQ 15.3 and 15.4 None required.
SAP Sybase IQ 16.0 None required.

Setting a Clustered Index on a Materialized View

Database Version Materialized View Privileges
SAP Sybase IQ 15.3 and 15.4 Requires one of:
  • DBA authority.
  • You own the materialized view.
SAP Sybase IQ 16.0 Requires one of:
  • ALTER ANY MATERIALIZED VIEW system privilege.
  • ALTER ANY OBJECT system privilege.
  • You own the materialized view.

Viewing or Modifying Materialized View Properties

Database Version Materialized View Privileges
SAP Sybase IQ 15.3 and 15.4

View any materialized view property page – None required.

Modify materialized view permissions – Requires one of:
  • DBA authority.
  • PERMS ADMIN authority.
  • Administrative rights over the permission.
  • You own the database object.
Modify the SQL page of the materialized view properties – Requires one of:
  • DBA authority.
  • You own the materialized view and have SELECT permission on the underlying tables.
Modify any other materialized view property except those relating to indexes – Requires one of:
  • DBA authority.
  • ALTER permission on the underlying tables.
  • SELECT permission on the underlying tables.
  • You own the materialized view.
For privileges relating to materialized view indexes, see:
SAP Sybase IQ 16.0

View any materialized view property page – None required.

Modify materialized view permissions – Requires one of:
  • MANAGE ANY OBJECT PRIVILEGE system privilege.
  • You have administrative rights (with grant option) to the permission.
  • You own the database object.
Modify a materialized view comment – Requires any one of:
  • CREATE ANY MATERIALIZED VIEW system privilege
  • ALTER ANY MATERIALIZED VIEW system privilege
  • CREATE ANY OBJECT system privilege
  • ALTER ANY OBJECT system privilege
  • COMMENT ANY OBJECT system privilege
  • You own the materialized view.
Modify the SQL page of the materialized view properties – Requires one of:
  • DROP ANY MATERIALIZED VIEW system privilege.
  • DROP ANY OBJECT system privilege.
  • You own the materialized view.
  • Also requires one of:
    • CREATE ANY MATERIALIZED VIEW system privilege.
    • CREATE ANY OBJECT system privilege.
Modify any other materialized view property except those relating to indexes – Requires one of:
  • ALTER ANY MATERIALIZED VIEW system privilege.
  • ALTER ANY OBJECT system privilege.
  • You own the materialized view.
For privileges relating to materialized view indexes, see:
Parent topic: Configuring SAP Sybase IQ for Administration
Related tasks
Adding a System Privilege to a Role-Based User
Adding a System Privilege to a User-Extended Role
Adding a System Privilege to a Standalone Role
Adding a System Privilege to a System Role
Adding an Authority to an Authority-Based User
Adding an Authority to a Group