Required permissions for database user accounts

Depending on the used database specific permissions are necessary. These are described in the following sections.

Oracle databases

For DBA layers:

CREATE USER <dbuser> IDENTIFIED BY "<password>";
GRANT DBA TO <dbuser>;

For standard layers:

CREATE USER <dbuser> IDENTIFIED BY "<password>";
GRANT CONNECT TO <dbuser>;
GRANT RESOURCE TO <dbuser>;

MySQL databases

For DBA layers:

# mysqladmin --default-character-set=utf8 create <dbname>
# mysql
mysql> CREATE USER <dbuser> IDENTIFIED BY "<password>";
mysql> GRANT ALL PRIVILEGES ON *.* TO <dbuser>;
mysql> GRANT GRANT OPTION ON *.* TO <dbuser>;

For standard layers:

# mysqladmin --default-character-set=utf8 create <dbname>
# mysql
mysql> CREATE USER <dbuser> IDENTIFIED BY "<password>";
mysql> GRANT ALL PRIVILEGES ON <dbname>.* TO <dbuser>;

Specifying UTF-8 character coding does not make sense until MySQL Version 5 and higher.

The InnoDB storage engine must be enabled on the MySQL server!

Advisable MySQL server parameter values for production systems:

[mysqld]
set-variable=max_allowed_packet=4M
key_buffer_size=20M
sort_buffer_size=1M
query_cache_size=14M
innodb_buffer_pool_size=128M

PostgreSQL

For DBA layers:

createdb -E UTF8 myDBname "my DB description text"
createuser -D -A -P -E myDBuser
psql -d myDBname -c "grant create on database myDBname to myDBuser;"

For default layers:

createdb -E UTF8 myDBname "my DB description text"
createuser –D –A -P -E myDBuser

All createuser queries can be answered with "No" as, apart from those assigned to DBA layers via grant create, extended user privileges are not necessary either for DBA layers or for default layers.

The password authentication (type MD5) must then be entered in the /etc/postgres/pg_hba.conf file for the given user on the database used. The following must be called to make the change known to the database server:

pg_ctl reload

IBM DB2

Creating the database (DBA layers and default layers):

db2 create database myDB using codeset utf-8 territory us pagesize 32 k

db2 update db cfg for myDB using applheapsz 1024

db2 connect to myDB

db2 create schema myUser

The last line is only necessary if the default schema “myUser” of the database “myDB” is not yet available.

“myUser” is the JDBC user name which in the case of DB2 is equal to the name of the instance, i.e. for example "db2inst1". If necessary, a different schema name can be used.

The following permissions are necessary for the DB2 user account:

• For DBA layers: DBADM
• For default layers: CONNECT, CREATETAB, BINDADD, IMPLICITSCHEMA

MS SQL server

If no SCHEMA was defined in the layer definition, then the DB user will require not only the “public” and “db_owner” permissions in the database set under “jdbc.URL”, but also the server role “System Administrators”. With these permissions, the DB user can create new table owners (same SCHEMA), e.g., when importing projects.

(see configuration example; specific parameters).