Database Permissions

If your data files are stored within a database, or your COBOL environment uses a database to perform any operations, you will require certain database permissions to interact with the database. These permissions will vary depending on the actions being performed and the database provider used.

The following lists show the permissions required for administrative users (users who typically create datastores and databases) and everyday users (users accessing data files within a database or operating within an environment where a database is performing some function). These lists should enable an administrator to create distinct database roles suited for the user type.

Administrative roles

For users who are required to create datastores, ensure that they have permissions to the following operations:

SQL Server and Azure SQL Databases

ALTER TABLE ADD PRIMARY KEY
ALTER TABLE ALTER|DROP COLUMN
CREATE DATABASE
CREATE/ALTER/DROP TABLE
CREATE FUNCTION
CREATE PROCEDURE
CREATE SCHEMA
ALTER TABLE DROP CONSTRAINT
INSERT permissions for tables
EXECUTE permissions for stored procedures and functions

PostgreSQL

ALTER COLUMN
CREATE DATABASE
CREATE/ALTER/DROP TABLE
CREATE/DROP TYPE
CREATE INDEX
CREATE FUNCTION
CREATE SCHEMA
EXECUTE permissions for functions
INSERT permissions for tables
ALTER TABLE ALTER COLUMN SET DATA
ALTER TABLE DROP COLUMN

Db2

ALTER COLUMN
CALL SYSPROC.ADMIN_CMD('REORG TABLE <table-name>')
CREATE DATABASE
CREATE BUFFERPOOL
CREATE TABLESPACE
CREATE SCHEMA
CREATE/ALTER TABLE
CREATE INDEX
CREATE PROCEDURE
CREATE FUNCTION
EXECUTE permissions for stored procedures and functions
INSERT permissions for tables
ALTER TABLE ALTER COLUMN SET DATA

Note: The database creation scripts generated by dbfhadmin -script will grant the appropriate permissions required by MFDBFH to PUBLIC for each of the objects created.

Oracle

ALTER TABLE DROP|ADD CONSTRAINT
CREATE USER
CREATE/ALTER TABLE
CREATE INDEX
CREATE PROCEDURE
CREATE FUNCTION
ALTER TABLE MODIFY <column>
Table INSERT permission
Stored procedure and function EXECUTE permissions

Note: The database creation scripts generated by dbfhadmin -script will grant the appropriate permissions required by MFDBFH to PUBLIC for each of the objects created unless the MFDBFH_GRANT_USER_PERMISSIONS variable is set; in which case, permission is granted to those users/roles specified.

All other users

For users who handle data files that are stored in a database, ensure that they have permissions to the following operations:

SQL Server and Azure SQL Databases

ALTER TABLE ADD PRIMARY KEY
ALTER TABLE ALTER|DROP COLUMN
CREATE/ALTER/DROP TABLE
CREATE FUNCTION
CREATE PROCEDURE
CREATE SEQUENCE
ALTER TABLE DROP CONSTRAINT
INSERT permissions for tables
EXECUTE permissions for stored procedures and functions

PostgreSQL

ALTER COLUMN
CREATE/ALTER/DROP TABLE
CREATE INDEX
CREATE FUNCTION
CREATE SEQUENCE
EXECUTE permissions for functions
SELECT/INSERT/UPDATE/DELETE permissions for tables
ALTER TABLE ALTER COLUMN SET DATA
ALTER TABLE DROP COLUMN

Db2

ALTER COLUMN
CALL SYSPROC.ADMIN_CMD('REORG TABLE <table-name>')
CREATE/ALTER/DROP TABLE
CREATE INDEX
CREATE/DROP PROCEDURE
CREATE SEQUENCE
EXECUTE permissions for stored procedures and functions
SELECT/INSERT/UPDATE/DELETE permissions for tables
ALTER TABLE ALTER COLUMN SET DATA

Note: When MFDBFH dynamically creates database objects, it grants the appropriate permissions it requires to PUBLIC for each one.

Oracle

ALTER TABLE DROP|ADD CONSTRAINT
CREATE/ALTER/DROP TABLE
CREATE INDEX
CREATE/DROP PROCEDURE
CREATE SEQUENCE
ALTER TABLE MODIFY <column>
Table SELECT/INSERT/UPDATE/DELETE permission
Stored procedure and function EXECUTE permissions

Note: When MFDBFH dynamically creates database objects, it grants the appropriate permissions it requires to PUBLIC for each one unless the MFDBFH_GRANT_USER_PERMISSIONS variable is set; in which case, permission is granted to those users/roles specified.