Types of privileges
There are two types of privileges that you can grant:
- Administrator
- Administrator privileges control creation of objects and system administration.
- Object
- Object privileges control access to specific database objects.
Some administrator privileges are global in scope, regardless of the current database. For example, the database, user, group, system, and hardware administrator privileges are global in scope. All other administrative privileges can be either global or local depending on the current database.
The following table describes the administrative
privileges.
| Privilege | Description |
|---|---|
| Backup | Allows user to create backups. The user can run the command nzbackup. |
| [Create] Aggregate | Allows the user to create user-defined aggregates (UDAs). Permission to operate on existing UDAs is controlled by object privileges. |
| [Create] Database | Allows the user to create databases. Permission to operate on existing databases is controlled by object privileges. |
| [Create] External Table | Allows the user to create external tables. Permission to operate on existing tables is controlled by object privileges. |
| [Create] Function | Allows the user to create user-defined functions (UDFs). Permission to operate on existing UDFs is controlled by object privileges. |
| [Create] Group | Allows the user to create groups. Permission to operate on existing groups is controlled by object privileges. |
| [Create] Index | For system use only. Users cannot create indexes. |
| [Create] Library | Allows the user to create shared libraries. Permission to operate on existing shared libraries is controlled by object privileges. |
| [Create] Materialized View | Allows the user to create materialized views. |
| [Create] Procedure | Allows the user to create stored procedures. Permission to operate on existing stored procedures is controlled by object privileges. |
| [Create] Schema | Allows the user to create schemas. Permission to operate on existing schemas is controlled by object privileges. |
| [Create] Sequence | Allows the user to create database sequences. |
| [Create] Synonym | Allows the user to create synonyms. |
| [Create] Table | Allows the user to create tables. Permission to operate on existing tables is controlled by object privileges. |
| [Create] Temp Table | Allows the user to create temporary tables. Permission to operate on existing tables is controlled by object privileges. |
| [Create] User | Allows the user to create users. Permission to operate on existing users is controlled by object privileges. |
| [Create] View | Allows the user to create views. Permission to operate on existing views is controlled by object privileges. |
| [Manage] Hardware | Allows the user to do the following hardware-related operations: view hardware status, manage SPUs, manage topology and mirroring, and run diagnostic tests. The user can run these commands: nzhw and nzds. |
| [Manage] Security | Allows the user to run commands and operations that relate to the following advanced security options such as: managing and configuring history databases; managing multi-level security objects and specifying security for users and groups; managing database key stores and keys and key stores for the digital signing of audit data. |
| [Manage] System | Allows the user to do the following management operations: start/stop/pause/resume the system, abort sessions, view the distribution map, system statistics, and logs. The user can use these commands: nzsystem, nzstate, nzstats, and nzsession. |
| Restore | Allows the user to restore the system. The user can run the nzrestore command. |
| Unfence | Allows the user to create or alter a user-defined function or aggregate to run in unfenced mode. |
Object privileges can also be local or global in scope.
The procedure to define global object privileges is different from
defining local object privileges. Another difference is that global
object privileges are broader and not particular to a specific object,
but instead to a class of objects. The following table describes the
object privileges.
| Privilege | Description |
|---|---|
| Abort | Allows the user to abort sessions. Applies to groups and users. |
| Alter | Allows the user to modify object attributes. Applies to all objects. |
| Delete | Allows the user to delete table rows. Applies only to tables. |
| Drop | Allows the user to drop objects. Applies to all object types. |
| Execute | Allows the user to run user-defined functions, user-defined aggregates, or stored procedures. |
| GenStats | Allows the user to generate statistics on tables or databases. The user can run the GENERATE STATISTICS command. |
| Groom | Allows the user to reclaim disk space for deleted or outdated
rows, and reorganize a table by the organizing keys, or to migrate
data for tables that have multiple stored versions. Note: Grooming
a table is done as a user, not an administrator, so to run GROOM TABLE
requires that you have object privileges on that table as well.
|
| Insert | Allows the user to insert rows into a table. Applies only to tables. |
| List | Allows the user to display an object name, either in a list or in another manner. Applies to all objects. |
| Select | Allows the user to select (or query) rows within a table. Applies to tables and views. |
| Truncate | Allows the user to delete all rows from a table. Applies only to tables. |
| Update | Allows the user to modify table rows. Applies to tables only. |