This guide describes how to configure one step database authentication in Oracle and MySQL. Each IJC schema user has an equivalent database username and password and this guide explains how to set IJC up so you can use this synonymously as the IJC username for all your users. The advantages of this approach are:

Ability to provide finer grain control over what each user can do than can be provided at the IJC level.
Can potentially use existing security mechanisms that you have in place e.g. Oracle row level security.
Potentially allows better auditing of changes.

However, there are some corresponding disadvantages:

More complex to set up.
Needs RDBMS accounts for every user.
IJC will not understand restrictions defined at the database level. So, for instance whilst IJC thinks a user can update a particular value and allows it to be updated, the database might forbid this and throw an error. Whilst your data is safe, the user experience will be compromised.

The way Oracle and MySQL handle users and schemas is quite different, so although the end effect is the same the steps needed to set this up are bit different.

Normal connection using DB and IJC usernames

Just as reminder, after a security policy is created, normal IJC authentication is a 2 stage process:

First of all, create a new project. In your new project you can create 3 new schemas, one for each supported RDBMS.
Initially, you will notice that in Derby you are automatically connected to the schema as the IJC user 'admin' with no password prompt.
Initially, you will notice that in the case of Oracle and MySQL you are automatically connected to the schema the as IJC user 'admin' with no password prompt. You will need to provide a previously created database level user/connection for both these RDBMS.
For each schema, create a security policy of type 'Username/Password using IJC database'.
For each schema, disconnect and reconnect and create the security tables required by clicking the 'Create Tables' button on the user management tab. You will see the following behaviour.
You will noticed that in Derby you are now prompted for the IJC user 'admin' password which defaults to 'admin'.
You will noticed that in the case of Oracle and MySQL you are now prompted for the IJC user 'admin' password which defaults to 'admin'. You will need to provide a previously created database level user/connection for both these RDBMS.
For each schema or RDBMS you can now configure any number of further schema level users all of which share a single database user/connection. In the case of Oracle and MySQL each new user will be required to login to the schema and also the provide the login for the database connection. Normally, database connections are shared between all schema users and typically these are provided by an administrator as above. The users will connect to a single database user shared between all IJC users, hence a 'two step' connection is required.

One step connection using single usernames

Alternatively, for Oracle and MySQL (Derby is always single step since it is embedded in IJC) using database-based authentication differs from this in that each schema user has their own database username and password i.e. they do not share a single database user as described above. The following guide now provides the steps needed for a simple, but useful implementation of the 'one step' security mechanism. In both examples we assume to have a super-user called 'master' (not the Oracle System user) who has full or administrative access rights, and another user 'slave' who has much more limited access rights. In reality you will probably have many 'slave' users and each one may have slightly different access rights, so you may need to adapt slightly according to your exact needs.

In this scenario, the database user used in the first step is named exactly the same as the IJC username and is used automatically by IJC. Thus, there is no need for a second stage of authentication. To achieve this:

The RDBMS has to be configured with the correct accounts and permissions.
IJC has to be configured with the equivalent users.
IJC security configuration has to be set to use 'database-based' authentication.

Need to modify IJC_* and JChem tables

Even if your 'slave' user is only to have read-only access to your data files, some additional privileges are needed for some tables. This is because IJC stores its configuration information in the database, so that, for instance, when a user logs in, or creates or updates a form, or wishes to save a list or query then this information is written to some of the IJC_* metadata tables in the database. In addition extra privileges are needed for some of the JChem tables that control how updates are handled.

Steps for Oracle:

Set up master Oracle user with CONNECT, CREATE SESSION and RESOURCE privileges. We assume this user is called 'master'.
Start IJC and use the 'New schema' wizard to connect to Oracle using 'master' as the DB username. Choose to remember DB username but not password. Connect as owner / Single user mode.
Create everything needed in IJC as usual. Create data trees, Import files, create forms etc...
Set up security. Choose the 'Username/password using IJC database' as the security template. Disconnect and connect as database user master and IJC user admin. Do not remember the IJC username or password.
Edit the security again, go to the 'User management' tab and create the tables.
Add a user of which username must be the same as database username and give all roles. This user will be your super-user. The username must be the same as the database username, but the password can be different or not specified.
Disconnect and reconnect again. Specify 'master' as the DB username and supply the appropriate password. Specify 'master' as the IJC username and the appropriate password if defined.
Check that everything works for this user and you have full privileges in IJC (admin's views will not be visible).
Edit the IJC security configuration again and this time choose 'Username/password using database accounts'.
Disconnect and reconnect again. Specify 'master' as the DB username and the appropriate password. You should not be prompted for an IJC username as the database username is now used as the IJC username.
You can compare too the 2 stage login process above and how this differs.
Go to Oracle (e.g. using SQLPlus, or the IJC Database Explorer) and create a new slave user. Set up slave Oracle user with CONNECT and CREATE SESSION privileges. We will call this user 'slave'.
Grant appropriate privileges for that user to the tables in the MASTER schema. See the table below for exact details. A 'dynamic SQL' script can also be used for this purpose which will help.
In IJC edit the IJC security and add a new user called 'slave' and specify a random password (this is not used but it is best to specify something random here). Note that the IJC username must be the same as the Oracle username, but the passwords can (and should) be different). Assign the slave user the role ROLE_USER for read only access or you can assign ROLE_EDIT_DATA if that user is allowed to update the table.
Disconnect and reconnect again. Specify 'slave' as the DB username and the appropriate password. You should be connected and have whatever roles were defined for the 'slave' IJC user.
Repeat the last 4 steps for any other Oracle slaves you need!

Steps for MySQL

These steps assume basic knowledge of how to administer MySQL. You can do all this from the mysql command-line tool, or use graphical tools like MySQL Administrator or MySQL Workbench.

Create a schema within MySQL for the data. Lets assume this schema is called 'ijcdata'.

Depending on the history of MySQL you might be refer to this as a catalog. In MySQL the terms schema and catalog are a bit fluid, and usually interchangeable, but do not assume that they correspond exactly to the Oracle term schema.

CREATE SCHEMA ijcdata;

# Create a MySQL user 'master' and grant all rights to the 'ijcdata' schema CREATE USER 'master'@'localhost' IDENTIFIED BY 'master'; GRANT ALL PRIVILEGES ON ijcdata.* TO 'master'@'localhost' WITH GRANT OPTION;
Start IJC and use the 'New schema' wizard to connect to MySQL using 'master' as the DB username and master as the schema you are connecting to. e.g your JDBC URL will look like this:

jdbc:mysql://ip:3306/ijcdata

Initially do not remember the username or password, but when you are prompted the second time choose to remember just the username.
Create everything needed in IJC as usual. Import files, create forms etc.
Set up security. Choose the 'Username/password using IJC database' as the security template.
When done disconnect and reconnect using the master as the DB user and 'admin' IJC user. Do not remember the IJC username or password.
Edit the security again, go to the 'User management' tab and create the tables.
Add a user called 'master' and give all roles. This user will be your super-user. The username must be the same as the database username, but the password can be different.
Disconnect and reconnect again. Specify 'master' as the DB username and the appropriate password. Specify 'master' as the IJC username and the appropriate password.
Check that everything works for this user and you have full privileges in IJC (admin's views will not be visible).
Edit the IJC security configuration again and this time choose 'Username/password using database accounts'.
Disconnect and reconnect again. Specify 'master' as the DB username and the appropriate password. You should not be prompted for an IJC username as the database username is now used as the IJC username.
Check again that everything works for this user and you have full privileges in IJC.
Go to MySQL (e.g. using MySQL Workbench, or the IJC Database Explorer) and create the new user. We will call this user 'slave'. CREATE USER 'slave'@'localhost' IDENTIFIED BY 'slave'; GRANT ALL PRIVILEGES ON ijcdata.* TO 'slave'@'localhost';

# Grant appropriate privileges for that user to the tables in the MASTER schema. See below for details.

# In IJC edit the IJC security and add a new user called 'slave' and specify a random password (this is not used but it is best to specify something random here). Note that the IJC username must be the same as the MySQL username, but the passwords can (and should) be different). Assign the slave user the role ROLE_USER for read only access or you can assign ROLE_EDIT_DATA if that user is allowed to update the table. !http://chemaxon.com/images/ijctest/Slave.png! !http://chemaxon.com/images/ijctest/Slave2.png!

# Disconnect and reconnect again. Specify 'slave' as the DB username and the appropriate password. You should be connected and have whatever roles were defined for the 'slave' IJC user.

# Repeat the last 4 steps for any other MySQL users you need

h3. Oracle privileges

More about the privileges needed for Oracle can be found here:

* [Supported databases |IJCtest_docs_admin_supported_dbs]

* [Setting up cartridge tables for use in IJC |IJCtest_docs_admin_tips_carttable_index]

Set the privileges listed in the table below, using SQLPlus or a similar database tool. You will need to issue commands like this in master for each slave user:

SELECT 'GRANT SELECT ON master.' || object_name || ' TO slave;' from user_objects where object_type = 'TABLE';

Assuming you have used the standard object nomenclature, you can connect as each slave user and run this script at SQLPlus.
- script link

MySQL privileges

More about the privileges needed for MySQL can be found in here:
Supported databases
Set the privileges listed in the table below, using X or a similar database tool. You will need to issue commands like this for each slave user:

grant select, insert, update, delete on MASTER.JCHEMPROPERTIES to slave;

Assuming you have used the standard object nomenclature, you can connect as each slave user and run this script at X.
– script link

Tables	Privileges
JCHEMPROPERTIESJCHEMPROPERTIES_CRIJC_VIEWSIJC_CUSTOM_ITEMSIJC_ITEM_USER	SELECT, INSERT, UPDATE, DELETE Note that your JChem property table might have a different name.
IJC_USER	SELECT, INSERT, UPDATE
IJC_INVOCATION_LOGIJC_CHANGE_LOG	SELECT, INSERT
IJC_AUTHORITIESIJC_SCHEMA_AUTHORITIESIJC_SECURITY_AUTHORITIESIJC_CHANGE_LOGIJC_ITEM_INFO IJC_SECURITY_INFOIJC_SECURITY_USERS	SELECT
IJC_SCHEMA	SELECT if user is not allowed to modify the IJC data model (ROLE_USER, or ROLE_EDIT_DATA) or SELECT, INSERT, UPDATE and DELETE if they are to have ROLE_EDIT_SCHEMA
Each JChem Base table	SELECT if user is not allowed to modify the data in the table (ROLE_USER) or SELECT, INSERT, UPDATE and DELETE as appropriate if they are (ROLE_EDIT_DATA).
*_UL table for each JChem Base table	SELECT, INSERT, UPDATE and DELETE
Each non-structure table	SELECT if user is not allowed to modify the data in the table (ROLE_USER) or SELECT, INSERT, UPDATE and DELETE as appropriate if they are (ROLE_EDIT_DATA).
Cartridge tables	SELECT if user is not allowed to modify the data in the table (ROLE_USER) or SELECT, INSERT, UPDATE and DELETE as appropriate if they are (ROLE_EDIT_DATA). You must also grant privileges to the corresponding _JCX, JCX_UL,
Cartridge tables's _JCX table	SELECT if user is not allowed to modify the data in the table (ROLE_USER) or SELECT, INSERT, UPDATE and DELETE as appropriate if they are (ROLE_EDIT_DATA).
Cartridge tables's _JCX_UL table	SELECT if user is not allowed to modify the data in the table (ROLE_USER) or SELECT, INSERT, UPDATE and DELETE as appropriate if they are (ROLE_EDIT_DATA).
Cartridge tables's _JSU table	SELECT if user is not allowed to modify the data in the table (ROLE_USER) or SELECT, INSERT, UPDATE and DELETE as appropriate if they are (ROLE_EDIT_DATA).

You must also grant SELECT privilege to any sequences that are used. Typically there is one of these for each standard table, and two for each JChem table.

 SELECT 'GRANT SELECT ON master.' || object_name || ' TO slave;' from user_objects where object_type = 'SEQUENCE';

Problems?

If you find problems with particular database versions then please report them to the IJC forum .

Using database authentication