GENERAL RULE: For the AnthillPro upgrade you should only need SELECT, INSERT, UPDATE and DELETE for the anthill user. Granting DBA permissions is not necessary and actually can cause problems on install.

Installation w/MySQL

The instructions present in the documentation for MySQL database can be used for installing, but listed below are the commands that need to be performed on the database side:

mysql> create database [database-name];
Query OK, 1 row affected (0.00 sec)

.
.
.

mysql> grant all on [database-name].* to '[dbuser]'@'%' identified by '[dbpass]'
with grant option;
Query OK, 0 rows affected (0.03 sec)

This will grant all permissions for the specified user to connect to the specified database name.

Known Issue w/MySQL 5.5x Versions

Newer versions of MySQL[1] are known to have some issues with the default grants listed above. In that case, you have to manually specify the user for the grant (along with machine name):

mysql> create database [database-name];
Query OK, 1 row affected (0.00 sec)

.
.
.

mysql> grant all on [database-name].* to '[dbuser]'@'[machine-name]' identified by '[dbpass]'
with grant option;
Query OK, 0 rows affected (0.03 sec)

Installation w/Oracle XE

Connect as SYSTEM, and then create a new user. Grant RESOURCE, CONNECT, CREATE SESSION, and CREATE TABLE on that user (you should now be able to access this connection through SQL-DEVELOPER). Now, you should be able to install anthillpro (NOT as DBA user) with the new user.

See the talk page for further details.

NOTE: Do NOT use GRANT ALL PRIVILEGES as this will cause AmbiguousTableNameExceptions!

Installation w/SQLServer

Make sure you are using the correct JAR file for the Java version you are using:

• Java 5
  • sqljdbc.jar
• Java 6/7
  • sqljdbc4.jar

NOTE: Please remove any existing JAR files in the lib\ext directory that are not the JAR file listed above! The anthill installer will include these files during the installation and if both files are present in the directory there is a great chance the wrong one will get loaded!

Be sure to grant a schema for a user (default is anthill3)

Make sure you grant all permissions available to the user being used for anthill:

USE [databasename]
GRANT ALL TO [username]

Also ensure the correct roles are assigned to the database user:

• [schema]
• db_accessadmin
• db_backupoperator
• db_datareader
• db_datawriter
• db_ddladmin

Imported Databases Note

Imported databases might have a schema & user associated with them already. To change the default schema that is being used (usually dbo on imports), you can run the following in a new query:

USE [DatabaseName]
SELECT 'ALTER SCHEMA anthill3 TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo'

This will provide a list of queries that you can run next in the SQL window:

ALTER SCHEMA anthill3 TRANSFER dbo.HI_LO_SEQ
.
.
.