Database privileges

Database privileges

In order for a messaging engine to use its data store, the database user ID that the messaging engine uses must have sufficient privilege to enable the messaging engine to access the data store tables. If we want the messaging engine to create the data store tables automatically, the messaging engine user ID requires additional privileges.

The following table describes the database privileges that the messaging engine user ID requires to access the data store and create the data store tables.

Database management system Minimum privilege required for the messaging engine to use the data store tables Additional privilege required for the messaging engine to create the data store tables
DB2 The messaging engine user ID needs SELECT, INSERT, UPDATE, and DELETE privileges on the tables. The messaging engine user ID needs CREATETAB authority on the database and USE privilege on the table space as well as CREATEIN privilege on the schema.
Oracle The messaging engine user ID needs at least SESSION privilege to connect to the database. If the same user ID owns both the data store schema and the messaging engine that is connecting to the database, the messaging engine has sufficient privilege to manipulate the tables. Otherwise, the messaging engine needs SELECT, INSERT, UPDATE and DELETE object privileges on the tables that comprise the data store, and DROP ANY TABLE system privilege to enable use of the TRUNCATE TABLE statement. The messaging engine user ID requires sufficient privilege to create relational tables and indexes in the data store schema. The messaging engine also requires a space quota in the default tablespace of the owner of that schema.
SQL Server Configure the SQL Server for SQL Server and Windows authentication. This allows authentication to be based on an SQL server login ID and password. The messaging engine user ID can be the owner of the tables, or be a member of a group that has sufficient authority to issue TRUNCATE TABLE statements. The messaging engine user ID needs CREATE TABLE statement privilege.
Sybase The messaging engine user ID can be the owner of the tables, or can be a member of a group that has sufficient authority to issue TRUNCATE TABLE statements. The messaging engine user ID needs CREATE TABLE permission.
Informix The messaging engine user ID must have CONNECT privilege on the database. It must also have SELECT, INSERT, UPDATE and DELETE authority on the tables. The messaging engine user ID must have RESOURCE privilege on the database.
Derby If user authentication is enabled, we must authorize the messaging engine user ID to access the database.
Remember: The default database generated by the messaging engine has no security mechanisms enabled.
You need no additional privileges.

If we do not grant TRUNCATE TABLE authority to the database user ID, we can force the messaging engine to delete rows individually instead of truncating the table. To force the messaging engine to delete rows individually, set sib.msgstore.jdbcUseDeleteInsteadOfTruncateAtStartup to true as a custom property of the messaging engine.

If we use DELETE instead of TRUNCATE, startup is slower when there are many non-persistent messages in the datastore.

Database management system	Minimum privilege required for the messaging engine to use the data store tables	Additional privilege required for the messaging engine to create the data store tables
DB2	The messaging engine user ID needs SELECT, INSERT, UPDATE, and DELETE privileges on the tables.	The messaging engine user ID needs CREATETAB authority on the database and USE privilege on the table space as well as CREATEIN privilege on the schema.
Oracle	The messaging engine user ID needs at least SESSION privilege to connect to the database. If the same user ID owns both the data store schema and the messaging engine that is connecting to the database, the messaging engine has sufficient privilege to manipulate the tables. Otherwise, the messaging engine needs SELECT, INSERT, UPDATE and DELETE object privileges on the tables that comprise the data store, and DROP ANY TABLE system privilege to enable use of the TRUNCATE TABLE statement.	The messaging engine user ID requires sufficient privilege to create relational tables and indexes in the data store schema. The messaging engine also requires a space quota in the default tablespace of the owner of that schema.
SQL Server	Configure the SQL Server for SQL Server and Windows authentication. This allows authentication to be based on an SQL server login ID and password. The messaging engine user ID can be the owner of the tables, or be a member of a group that has sufficient authority to issue TRUNCATE TABLE statements.	The messaging engine user ID needs CREATE TABLE statement privilege.
Sybase	The messaging engine user ID can be the owner of the tables, or can be a member of a group that has sufficient authority to issue TRUNCATE TABLE statements.	The messaging engine user ID needs CREATE TABLE permission.
Informix	The messaging engine user ID must have CONNECT privilege on the database. It must also have SELECT, INSERT, UPDATE and DELETE authority on the tables.	The messaging engine user ID must have RESOURCE privilege on the database.
Derby	If user authentication is enabled, we must authorize the messaging engine user ID to access the database. Remember: The default database generated by the messaging engine has no security mechanisms enabled.	You need no additional privileges.