Step 2: Create a Master Index Database and User

Note - MySQL is only supported in Java CAPS 6 Update 1.

Before beginning this step, complete Step 1: Analyze the Master Index Database Requirements. After you create the database instance and user, continue to Step 3: Define Master Index Database Indexes if you want to define additional database indexes; otherwise skip to Step 4: Define Master Index External Systems.

For this step you need to create a database in which the master index database instance will be created. Use the tools provided by your database vendor to create the database. Using these tools, you define tablespaces, including their sizes and locations; extents; and dump file, log file, and rollback file sizes and locations. Make sure these issues have been thoroughly analyzed and designed before creating the database.

Once you create the database, you can use standard SQL to create the master index application user for the database. The user you create in this step will be used to create the database structure and to connect to the database through the MIDM and through the application server.

For Oracle, assign the user to the “connect” and “resource” roles for the master index tablespaces. For example:

create user username identified by password;
grant connect, resource to username;
commit;

where username is the login ID of the administrator user and password is the login password of the administrator user.

For SQL Server, assign this user to the “db_owner” role. You need to create the server login, create the user, and then assign the user to the role. For example:

CREATE LOGIN loginname WITH PASSWORD = ’password’, DEFAULT_DATABASE = database; 
CREATE USER username FOR LOGIN loginname; USE database;
EXECUTE sp_addrolemember ’db_owner’, ’ username’ GO

where loginname is the login ID for the administrator user, password is the login password, database is the database name, and username is the owner of the database tables created in the master index database.

Note - SQL Server allows Windows Authentication, where only a user name is required. Java CAPS products require full authentication, including both a user name and password. You need to create a database user specifically for the master index application.

For MySQL, you can grant the user access to all permissions, or you can assign the individual permissions listed below. For example:

CREATE USER 'username'@'server_name’ IDENTIFIED BY 'password'; 
GRANT ALL ON database TO 'username'@'server_name’;
GRANT SELECT, INSERT ON database TO 'username'@'server_name’;

where username is the login ID for the user, server_name is the name of the database server, password is the login password, and database is the database name.

If you prefer to assign individual permissions to the user instead of roles, the following permissions are needed.