Database permissions

In order to run TDK successfully, the following minimum users permissions are required.

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}";

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}";

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}";

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}
/

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};
@