Permission Definition

On OS/400®, database security is managed at the operating system level, not at the database level. When you set up permissions for the database, you determine the degree of access (read, add, delete, etc.) individual users, groups, and authorization lists may have. This operation can easily be done via Operation Navigator.

The privileges must include the following system authorities:

  • *USE to the Create Physical File (CRTPF) command.
  • *EXECUTE and *ADD to the library into which the table is created.
  • *OBJOPR and *OBJMGT to the journal.
  • *CHANGE to the data dictionary if the library into which the table is created is an SQL collection with a data dictionary.

To define a foreign key, the privileges must include the following on the parent table:

  • The REFERENCES privilege or object management authority for the table.
  • The REFERENCES privilege on each column of the specified parent key.
  • Ownership of the table.

The REFERENCES privilege on a table consists of:

  • Being the owner of the table.
  • Having the REFERENCES privilege to the table.
  • Having the system authorities of either *OBJREF or *OBJMGT to the table.

The REFERENCES privilege on a column consists of:

  • Being the owner of the table.
  • Having the REFERENCES privilege to the column.
  • Having the system authority of *OBJREF to the column or the system authority of *OBJMGT to the table.

To EXECUTE a user-defined function, the privilege consists of:

  • Being owner of the user-defined function.
  • Having EXECUTE privilege to the user-defined function.
  • Having the system authorities of *OBJOPR and *EXECUTE to the user-defined function.