Database permissions

In order to run TDK successfully, the following minimum users permissions are required. It is recommended to limit how many connections an account can have at the same time. This helps to prevent databases overloading during TDK execution.

Replace ${database}, ${user}, ${password} with names of your database, user and password respectively.

SQL Server

Input database (reading):

use "${database}";
create login "${user}" with password = '${password}';
create user "${user}" for login "${user}";
grant select to "${user}";
grant view definition to "${user}";

Output database (writing):

use "${database}";
create login "${user}" with password = '${password}';
create user "${user}" for login "${user}";
grant
  alter,
  create table,
  references,
  insert,
  update,
  select,
  delete
to "${user}";

Resource Limits (advanced):

The default is 0, which means that the maximum (32,767) user connections are allowed. User connections is a dynamic (self-configuring) option, SQL Server adjust the maximum number of user connections automatically as needed, up to the maximum value allowable. To determine the maximum number of user connections that your system allows, you can use this manual.

PostgreSQL

Input database (reading):

create user "${user}" password '${password}';
grant connect on database "${database}" to "${user}";
/* If you use other schemas in your database, you should also explicitly
   provide access to these schemas.
 */
grant select on all tables in schema "public" to "${user}";

Output database (writing):

create user "${user}" password '${password}';
grant connect on database "${database}" to "${user}";
grant create on database "${database}" to "${user}";

Resource Limits (advanced):

Databases for PostgreSQL sets the maximum number of connections to your PostgreSQL database to 115, where 15 connections are reserved for the superuser to maintain the state and integrity of your database, and 100 connections are available for you and your applications. To determine the maximum number of user connections that your system allows, you can create the user with CONNECTION LIMIT connlimit according to the documentation.

MySQL

Input database (reading):

create user "${user}" identified by "${password}";
grant select, show view on `${database}`.* to "${user}";

Output database (writing):

create user "${user}" identified by "${password}";
grant select, show view, alter, create, create view, delete, drop, index, insert, references, trigger, update on `${database}`.* to "${user}";

Resource Limits (advanced):

The maximum number of simultaneous connections permitted to any given MySQL user account. A value of 0 (the default) means “no limit.” To determine the maximum number of user connections that your system allows, you can use this manual.

Oracle

Input database (reading):

create user ${user} identified by "${password}"
/
grant create session to ${user}
/
grant unlimited tablespace to ${user}
/
begin
-- Use actual owner(s) instead of 'test' here
    for r in (select owner, table_name from all_tables where owner='TEST') loop
        execute immediate 'grant select on '||r.owner||'.'||r.table_name||' to ${user}';
    end loop;
end;
/

Output database (writing):

create user ${user} identified by "${password}"
/
grant create session to ${user}
/
grant unlimited tablespace to ${user}
/
grant
    create any table,
    create any view,
    create any materialized view,
    create any sequence,
    create any index,
    alter any table,
    drop any index,
    drop any table,
    drop any view,
    drop any materialized view,
    drop any sequence,
    select any table,
    select any sequence,
    insert any table,
    update any table,
    delete any table
to ${user}
/

Resource Limits (advanced):

The maximum number of simultaneous connections permitted to any given Oracle user account is unlimited. To determine the maximum number of user connections that your system allows, you can create the profile with SESSIONS_PER_USER according to the documentation.

DB2

DB2 does not have its own users and relies on the operating system. So you have to create an OS user on the server running DB2 before you can grant permissions.

Input database (reading):

begin
declare script varchar(1024);
    declare sqlstate char(5) default '00000';
    declare cur cursor for
        select 'grant select on ' || rtrim(tabschema) || '.' || rtrim(tabname) || ' to ${user};'
        from syscat.tables
        where tabschema not like 'SYS%' and tabschema <> 'NULLID';
    open cur;
    rec_loop: LOOP
        fetch cur into script;
        if sqlstate ='02000' then leave rec_loop; end if;
        execute immediate script;
    end loop;
    close cur;
end;
@
grant execute on function sysproc.db2_get_instance_info to ${user};
@

Output database (writing):

grant dbadm with dataaccess on database to user ${user};
@

Resource Limits (advanced):

The Version 9.5 default for the max_coordagents and max_connections parameters will be AUTOMATIC, with max_coordagents set to 200 and max_connections set to -1 (that is, set to the value of max_coordagents). To determine the maximum number of user connections that your system allows, you can use this manual.