Database permissions summary

Overview of database creation

i2 Explore for iBase database setup follows a two-phase model:

• Phase 1 creates the Information Store database, schemas, logins, users, and roles. This can happen in one of two ways:

  • Automatic — An Administrator user with sysadmin permission supplies credentials when running deploy, either via the command line or the i2 Explore Administration Console when prompted.

  • Manual — An Administrator user with sysadmin permission runs the manual database creation scripts in SQL Server Management Studio before any deploy is invoked. Once complete, deploy detects that Phase 1 is already done and proceeds directly to Phase 2 without requesting Administrator user credentials. For more information, see Manually Creating the Information Store Database.

• Phase 2 populates the database with tables, views, stored procedures, and other schema objects. It runs under the DBA credential and does not require Administrator user credentials.

The database permissions granted to the DBA user and i2analyze user are identical regardless of whether Phase 1 was performed manually or automatically during deploy.

Once the initial deployment is complete, the Administrator user credential is no longer used. There are two distinct lifetime categories for the credentials created:

• DBA login/user (dba_<DB_NАME>): used exclusively during deployment tasks — deploy, upgrade and clean. It is never used at application runtime.

• i2analyze login/user (i2analyze_<DB_NАME>): used at runtime by the Liberty application (i2 Explore for iBase) to access the Information Store database and all three iBase databases. This is the only credential active while the application is serving users.

Administrator user credentials are accepted only via the command line setup.exe (-u / -p flags) or entered in the i2 Explore Administration Console when prompted, and are held in memory for the duration of the deploy task only. They are never persisted.

Note: Names shown in <UPPER_CASE> within angled brackets (for example <DB_NАME>) represent configuration variables. You can set these in <installation_location>/analyze-deployment-tooling/configs/explore-for-ibase/utils/variables.conf or within the Configuration tab of the i2 Explore Administration Console.

SQL Server principals

This section describes the server and database-level logins, users, and roles that the deployment process creates, along with their lifetime and use. All principals are created during initial deployment or upgrade. No user is ever given a permission that includes the WITH GRANT OPTION, and the DBA user does not need to grant permissions to other users. The Administrator user is the only principal that grants permissions, and it does so only during deployment and upgrade.

Server-level logins

The following table lists the logins at the server level.

Information Store database (<DB_NАME>)

Users and roles that apply to the Information Store database.

iBase data database (<IBASE_DB_NАME>)

Users and roles that apply to the iBase data database.

iBase security database (<IBASE_SEC_DB_NАME>)

Users and roles that apply to the iBase security database.

iBase log database (<IBASE_DB_NАME>_Log)

Users and roles that apply to the iBase log database.

Note: i2analyze_ibase is used solely for the cross-database iBase access. It maps to the i2analyze_<DB_NАME> server login. This is distinct from i2analyze_<DB_NАME> (the Information Store database user), which also maps to the same login but operates within the Information Store database only.

Permission detail

DBA Role (dba_<DB_NАME>_role) — Database-level DDL

The Administrator user grants these permissions in the Information Store database during Phase 1 deployment. They enable the DBA user to create all objects required during Information Store population.

VIEW DEFINITION is required for the DBA user to query sys.database_permissions, sys.database_principals, and sys.schemas when checking the permission state of other principals.

DBA Role (dba_<DB_NАME>_role) — Schema-level Grants

The Administrator user applies these grants to all Information Store schemas during Phase 1 deployment.

GRANT ALTER ON SCHEMA::<schema> TO dba_<DB_NАME>_role;
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES ON SCHEMA::<schema> TO dba_<DB_NАME>_role;

In addition, the Administrator user transfers ownership for the schemas that require it:

-- Enables object-level EXECUTE grants via AS clause (Custom_Search, IBase_Audit_Logger)
ALTER AUTHORIZATION ON SCHEMA::IS_Data TO dba_<DB_NАME>_role;

-- Enables ALTER SCHEMA … TRANSFER in backup/restore scripts (I010/I030)
ALTER AUTHORIZATION ON SCHEMA::IS_Core TO dba_<DB_NАME>_role;
ALTER AUTHORIZATION ON SCHEMA::IS_Meta TO dba_<DB_NАME>_role;
ALTER AUTHORIZATION ON SCHEMA::IS_Vq TO dba_<DB_NАME>_role;
ALTER AUTHORIZATION ON SCHEMA::ibase_keep TO dba_<DB_NАME>_role;

i2analyze Role (i2analyze_<DB_NАME>_role) — Schema-level Grants

The Administrator user grants these permissions in the Information Store database during Phase 1 deployment. The grants give the role access to read from and write to tables, execute stored procedures, and create tables and views within the Information Store schemas. The Administrator user applies the grants directly with its own authority, without using the AS clause.

i2analyze User (i2analyze_<DB_NАME>) — Object-level Grants

The deployment process applies these grants in the Information Store database during Phase 2 deployment.

These grants give the i2analyze user direct EXECUTE access on the two stored procedures in IS_Data that the Liberty application calls at runtime. The AS dba_<DB_NАME>_role clause is required because the DBA role owns IS_Data — SQL Server requires the grantor to either own the securable or hold explicit per-object GRANT OPTION; schema ownership satisfies this requirement without requiring CONTROL or sysadmin.

GRANT EXECUTE ON IS_Data.Custom_Search TO i2analyze_<DB_NАME> AS dba_<DB_NАME>_role;
GRANT EXECUTE ON IS_Data.IBase_Audit_Logger TO i2analyze_<DB_NАME> AS dba_<DB_NАME>_role;

When Administrator user credentials are available at deploy time, these grants are executed as the Administrator user. When the Administrator user is not available, they are executed as DBA (which can act via the AS clause because it is a member of the role that owns IS_Data).