Admin: Appendix C - Database Plugins Required Permissions

SELECT, INSERT, DELETE, UPDATE

EXECUTE

NEXT

CREATE_TABLE

SELECT_SYSCAT

ACCESS

SELECT, INSERT, DELETE, UPDATE

Only the owner can perform select, insert, delete, or update operations on objects it owns

EXECUTE

Only the owner of a database procedure can execute the procedure

NEXT

Allows the grantee to execute the NEXT VALUE and CURRENT VALUE functions on the specified sequences. To grant the NEXT privilege on sequences, the grantor must either own the sequence or have NEXT privilege WITH GRANT OPTION for the sequence.

Only the owner of a database sequence can execute the next value and current value operators on the sequence

CREATE_TABLE

Any user can create tables (create_table)

SELECT_SYSCAT

Allows a session to query system catalogs to determine schema information. When connected to the master database (iidbdb), this includes the master database catalogs such as iiuser and iidatabase. SELECT_SYSCAT can be granted to user, group, role or public, and can only be issued when connected to the iidbdb database.

This privilege restricts user queries against the core DBMS

catalogs containing schema information, such as iirelation and iiattribute. Standard system catalogs such as iitables can still be queried.

ACCESS

Allows the specified authorization IDs to connect to the specified database

SELECT

Grants privilege to select data from a table or view using a SELECT statement. The SELECT privilege is also required to reference existing column values for UPDATE or DELETE operations

INSERT

Grants privilege to load data into a table using an INSERT statement or a COPY statement

UPDATE

Grants privilege to update a table column using an UPDATE statement. UPDATE operations also require the SELECT privilege, because they must reference table columns to determine which rows to update, or to compute new values for columns

DELETE

Grants privilege to delete a data row from a table. DELETE operations also require the SELECT privilege, because they must reference table columns to determine which rows to delete

CREATE

For schemas, CREATE allows users to create objects within a schema. To rename an object, the user must have the CREATE privilege and own the object to be renamed

EXECUTE ON ALL

FUNCTIONS IN SCHEMA

Grants the specified privileges on all functions in the referenced schema

SELECT

Allows SELECT from any column of the specified table, view, or sequence. Also allows the use of COPY TO. For sequences, this privilege also allows the use of the currval function

INSERT

Allows INSERT of a new row into the specified table. Also allows COPY FROM

UPDATE

Allows UPDATE of any column of the specified table. SELECT ... FOR UPDATE and SELECT ... FOR SHARE also require this privilege (as well as the SELECT privilege). For sequences, this privilege allows the use of the nextval and setval functions

DELETE

Allows DELETE of a row from the specified table.

CREATE

For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema.

For tablespaces, allows tables and indexes to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace. (Note that revoking this privilege will not alter the placement of existing objects.)

CONNECT

Allows the user to connect to the specified database. This privilege is checked at connection startup (in addition to checking any restrictions imposed by pg_hba.conf)

EXECUTE

Allows the use of the specified function and the use of any operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions. (This syntax works for aggregate functions, as well.)

USAGE

For procedural languages, allows the use of the specified language for the creation of functions in that language. This is the only type of privilege that is applicable to procedural languages.

For schemas, allows access to objects contained in the specified schema (assuming that the objects’ own privilege requirements are also met). Essentially this allows the grantee to look up objects within the schema.

For sequences, this privilege allows the use of the currval and nextval functions

ALTER

Change the structure of an existing table using the ALTER TABLE statement

CREATE

Create a table using the CREATE TABLE statement. You can grant the CREATE privilege on tables that do not yet exist

CREATE VIEW

Create a view using the CREATE_VIEW statement

DELETE

Remove rows from a table using the DELETE statement

DROP

Drop a table using the DROP TABLE statement or a view using the DROP VIEW statement

INDEX

Create an index on a table using the CREATE INDEX statement. Without the INDEX privilege, you can still create indexes when creating a table using the CREATE TABLE statement if you have the CREATE privilege, and you can create indexes using the ALTER TABLE statement if you have the ALTER privilege

INSERT

Add rows to a table using the INSERT statement. The INSERT privilege can also be set on individual columns; see Column Privileges below for details

SELECT

Read data from a table using the SELECT statement. The SELECT privilege can also be set on individual columns; see Column Privileges below for details

SHOW VIEW

Show the CREATE VIEW statement to create a view using the SHOW CREATE VIEW statement

UPDATE

Update existing rows in a table using the UPDATE statement. UPDATE statements usually include a WHERE clause to update only certain rows. You must have SELECT privileges on the table or the appropriate columns for the WHERE clause. The UPDATE privilege can also be set on individual columns; see Column Privileges below for details

ALTER ROUTINE

Change the characteristics of a stored procedure using the ALTER PROCEDURE statement

EXECUTE

For stored procedure, execute a stored procedure using the CALL statement. The privilege to call a procedure may allow you to perform actions you wouldn't otherwise be able to do, such as insert rows into a table. For function, use a stored function. You need SELECT privileges for any tables or columns accessed by the function

CREATE ROUTINE

Create Stored Programs using the CREATE PROCEDURE and CREATE FUNCTION statements

CREATE

Enables creation of new databases and tables or indexes

DROP

Enables you to drop (remove) existing databases, tables, and views. The DROP privilege is required in order to use the statement ALTER TABLE ... DROP PARTITION on a partitioned table. The DROP privilege is also required for TRUNCATE TABLE. If you grant the DROP privilege for the mysql database to a user, that user can drop the database in which the MySQL access privileges are stored

ALTER

Enables use of ALTER TABLE to change the structure of tables.

ALTER TABLE also requires the CREATE and INSERT privileges.

Renaming a table requires ALTER and DROP on the old table,

CREATE, and INSERT on the new table

DELETE

Enables rows to be deleted from tables in a database

INDEX

Enables you to create or drop (remove) indexes. INDEX applies to existing tables. If you have the CREATE privilege for a table, you can include index definitions in the CREATE TABLE statement

INSERT

Enables rows to be inserted into tables in a database. INSERT is also required for the ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE table-maintenance statements

SELECT

Enables you to select rows from tables in a database. SELECT statements require the SELECT privilege only if they actually retrieve rows from a table. Some SELECT statements do not access tables and can be executed without permission for any database

UPDATE

Enables rows to be updated in tables in a database

ALTER ROUTINE

This privilege is needed to alter or drop stored routines (procedures and functions)

CREATE ROUTINE

This privilege is needed to create stored routines (procedures and functions)

EXECUTE

This privilege is required to execute stored routines (procedures and functions)

Note: For full access to the sys schema, a user must have  SELECT privilege on all sys tables and views

ALTER

Allows the user to modify object attributes. Applies to the following objects: SCHEMA, SEQUENCE, TABLE, FUNCTION, AGGREGATE, VIEW

DELETE

Allows the user to delete table rows. Applies only to tables

DROP

Allows the user to drop the objects: SCHEMA, SEQUENCE, TABLE, FUNCTION, AGGREGATE, VIEW

GROOM

Allows the user to do general housekeeping and cleanup operations on tables by using the GROOM TABLE command. The GROOM TABLE command runs reclaim operations to remove deleted rows and also reorganizes tables that are based on the clustered base table's organizing keys

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 the following objects: DATABASE, SCHEMA, SEQUENCE, TABLE, FUNCTION, AGGREGATE, VIEW

SELECT

Allows the user to select (or query) rows within a table. Applies to tables and views

UPDATE

Allows the user to modify table rows, such as changing field values or changing the next value of a sequence. Applies to tables only

EXECUTE

Allows the user to execute UDFs and UDAs in SQL queries

CREATE SESSION

Enables a user to create a connection to the database

CREATE ANY PROCEDURE

Enables a user to create a PL/SQL procedure, function or package owned by any user in the database

CREATE ANY VIEW

Enables a user to create a view owned by any user in the database

CREATE ANY TABLE

Enables a user to create a table owned by any user in the database

CREATE ANY INDEX

Enables a user to create an index on any table or materialized view in the database

ALTER SESSION

Set or modify any of the conditions or parameters that affect your connection to the database

ALTER ANY PROCEDURE

Enables a user to alter any PL/SQL procedure, function or package in the database

ALTER ANY TABLE

Enables a user to alter any table in the database

DELETE ANY TABLE

Enables a user to delete from any table in the database

SELECT ANY TABLE

Enables a user to select from any table, view, materialized view, or synonym in the database

SELECT ANY SEQUENCE

Enables a user to select from any sequence or synonym on a sequence in the database

UPDATE ANY TABLE

Enables a user to update any table or synonym in the database

INSERT ANY TABLE

Enables a user to insert into any table in the database. It also enables the user to insert into any table using the synonym, public or private, to that table

EXECUTE ANY PROCEDURE

Enables a user to execute any PL/SQL procedure, function or package in the database

DROP ANY PROCEDURE

Enables a user to drop any PL/SQL procedure, function or package in the database

DROP ANY SEQUENCE

Enables a user to drop any sequence in the database

DROP ANY TABLE

Enables a user to drop any table in the database

DROP ANY VIEW

Enables a user to drop any view in the database

DROP ANY INDEX

Enables a user to drop any index in the database

SELECT

Allows SELECT from any column, or the specific columns listed, of the specified table, view, or sequence. Also allows the use of COPY TO. This privilege is also needed to reference existing column values in UPDATE or DELETE. For sequences, this privilege also allows the use of the currval function. For large objects, this privilege allows the object to be read

INSERT

Allows INSERT of a new row into the specified table. If specific columns are listed, only those columns may be assigned to in the INSERT command (other columns will therefore receive default values). Also allows COPY FROM

UPDATE

Allows UPDATE of any column, or the specific columns listed, of the specified table. (In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns.) SELECT ... FOR UPDATE and SELECT ... FOR SHARE also require this privilege on at least one column, in addition to the SELECT privilege. For sequences, this privilege allows the use of the nextval and setval functions. For large objects, this privilege allows writing or truncating the object

DELETE

Allows DELETE of a row from the specified table. (In practice, any nontrivial DELETE command will require SELECT privilege as well, since it must reference table columns to determine which rows to delete.)

CREATE

For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema.

For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created

that have the tablespace as their default tablespace. (Note that revoking this privilege will not alter the placement of existing objects.)

CONNECT

Allows the user to connect to the specified database. This privilege is checked at connection startup (in addition to checking any restrictions imposed by pg_hba.conf)

EXECUTE

Allows the use of the specified function and the use of any operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions. (This syntax works for aggregate functions, as well.)

USAGE

For procedural languages, allows the use of the specified language for the creation of functions in that language. This is the only type of privilege that is applicable to procedural languages.

For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema. Without this permission, it is still possible to see the object names, e.g. by querying the system tables. Also, after revoking this permission, existing backends might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access. For sequences, this privilege allows the use of the currval and nextval functions

SELECT

To grant permission to perform SELECT statements or

SelectExpressions on a table or view. If a column list is specified with the SELECT privilege, the permission is valid on only those columns. If no column list is specified, then the privilege is valid on all of the columns in the table.

For queries that do not select a specific column from the tables involved in a SELECT statement or SelectExpression (for example, queries that use COUNT(*)), the user must have at least one column-level SELECT privilege or table-level SELECT privilege

INSERT

To grant permission to insert rows into the specified table

DELETE

To grant permission to delete rows from the specified table

UPDATE

To grant permission to use the UPDATE statement on the specified table. If a column list is specified, the permission applies only to the specified columns. To update a row using a statement that includes a WHERE clause, you must have the SELECT privilege on the columns in the row that you want to update

USAGE

In order to use a sequence generator, you must have the USAGE privilege on it

EXECUTE

Enables granting access to function or procedure

CREATE ANY INDEX

Allows to create an index on a specified table, or pair of tables

CREATE ANY OBJECT

Allows to create any database objects

CREATE ANY PROCEDURE

Allows to create a procedure

CREATE ANY SEQUENCE

Allows to create a sequence generator

CREATE ANY TABLE

Allows to create a table

CREATE ANY VIEW

Allows to create a view

ALTER ANY INDEX

Allows to modify indexes

ALTER ANY OBJECT

Allows to modify any objects

ALTER ANY SEQUENCE

Allows alter to a sequence object

ALTER ANY PROCEDURE

Allows alter to a procedure object

ALTER ANY TABLE

Allows to modify tables

ALTER ANY VIEW

Allows to modify views

DROP ANY INDEX

Allows to delete any explicitly created index

DROP ANY OBJECT

Allows to delete any database objects

DROP ANY PROCEDURE

Allows to delete procedure

DROP ANY SEQUENCE

Allows to delete sequence generator

DROP ANY TABLE

Allows to delete a table

DROP ANY VIEW

Allows to delete a view

SELECT ANY TABLE

Allows to retrieve information from the database

DELETE ANY TABLE

Allows to Delete all the rows from the named table that satisfy the search condition

INSERT ANY TABLE

Allows to insert a single row or a selection of rows, from elsewhere in the current database, into the table

UPDATE ANY TABLE

Allows to modify existing rows of a single table, or a view that contains only one table

USE ANY SEQUENCE

Allows to use sequence generator

EXECUTE ANY PROCEDURE

Allows to execute a procedure

CONNECT

Allows the user to connect to a database

EXECUTE

The type of privilege to grant the procedure

CREATE

Allows the user read access to the schema and the right to create tables and views within the schema

USAGE

Allows the user access to the objects contained within the schema. 

This allows the user to look up objects within the schema. Note that the user must also be granted access to the individual objects

SELECT

For Sequence, allows the right to use both the CURRVAL() and NEXTVAL() functions on the specified sequence.

For Table, allows the user to SELECT from any column of the specified table. For View, grants a user or role SELECT operations to a view, and any resources referenced within it

INSERT

Allows the user to INSERT tuples into the specified table and to use the COPY command to load the table. Note: COPY FROM STDIN is allowed to any user granted the INSERT privilege, while

COPY FROM <file> is an admin-only operation

UPDATE

Allows the user to UPDATE tuples in the specified table

DELETE

Allows DELETE of a row from the specified table

CREATE TABLE, CREATE

VIEW, CREATE

PROCEDURE

CREATE granted for the object type for the specified space

DROP TABLE, DROP VIEW, DROP PROCEDURE

DROP granted for the object type for the specified space

INDEX

Allows access to CREATE and DROP INDEX and COLLECT and DROP STATISTICS statements

EXECUTE PROCEDURE

Allows access the corresponding CALL statement. 

This privilege applies to all stored procedures in the specified space. For the grantee to use the privilege on the procedure, the owner of the stored procedure must have the appropriate privileges on the objects referenced by the stored procedure

INSERT, DELETE, SELECT, UPDATE

Privilege applies to all tables or views in the specified database. UPDATE applies to table or column of the table. For a grantee to use the granted privileges on a view, the owner of a view must have appropriate privileges on the underlying tables of the view

REORG

Grants the privilege to use the REORG utility to reorganize table spaces and indexes

EXECUTE

For procedure, this must only allow to grant access to the following procedures:

• SYSPROC.ADMIN_CMD procedure

• "{rpi_schema}"."RPIExecDDL"

ALTERIN

Grants the privilege to alter stored procedures and user-defined

functions, or specify a comment for distinct types, cast functions that are generated for distinct types, sequences, stored procedures, triggers, and user-defined functions in the designated schemas

CREATEIN

Grants the privilege to create distinct types, sequences, stored procedures, triggers, and user-defined functions in the designated schemas

DROPIN

Grants the privilege to drop distinct types, sequences, stored procedures, triggers, and user-defined functions in the designated schemas

USAGE

Grants the USAGE privilege to use a sequence. This privilege is needed when the NEXT VALUE or PREVIOUS VALUE expression is invoked for a sequence name

ALTER

Grants the privilege to alter the specified table or create a trigger on the specified table. ALTER cannot be used if the statement identifies an auxiliary table or a view

DELETE

Grants the privilege to delete rows in the specified table or view. DELETE cannot be granted on an auxiliary table

INDEX

Grants the privilege to create an index on the specified table. INDEX cannot be granted on a view

INSERT

Grants the privilege to insert rows into the specified table or view. INSERT cannot be granted on an auxiliary table

SELECT

Grants the privilege to create a view or read data from the specified table or view. SELECT cannot be granted on an auxiliary table

UPDATE

Grants the privilege to update rows in the specified table or view. UPDATE cannot be granted on an auxiliary table

dbmanager

Server-level role for creating databases

loginmanager

Server-level security role for creating logins

CONNECT ON DATABASE, ALTER ON DATABASE

Permissions that apply to servers

CREATE PROCEDURE, 

CREATE TABLE, CREATE VIEW

Permissions that apply only to databases

ALTER,DELETE,EXECUTE,INSERT,SELECT,UPDATE

Permissions that apply to databases, schemas, and objects

SYSADMIN

The default role to use