Creating SQL Users

Nectari provides scripts to create dedicated SQL users with administrative or limited access. You must run the scripts after the Nectari installation. These users will be dedicated to Nectari for customers who do not wish to give their sa user credentials. One of the advantages is that the SQL user password does not need to be changed, contrary to an sa user that is based on Windows authentication.

Note

Creating dedicated SQL users is highly recommended for security reasons. SQL users should be configured with restricted permissions on the SQL Server. This applies to SQL users who will connect to the Central Point, any SQL user configured in the Manage Environments and Data Sources page, and SQL users connecting to DataSync.

Note

These scripts must be run with a user with administrative permissions on the server.

Follow these general steps:

Additional scenarios:

  • You can grant or deny these users access to specific database schemas or alter their access.

Step 1: Create SQL administrative users

Administrative users will have access to all components:

  • Nectari Configuration database: The Nectari Configuration Database script creates the Nectari user.

  • BI License: The Nectari License Database script creates the BILicense user.

  • NectariCube: The Nectari Data Warehouse script creates the NectariCube user and grants that user permissions for each database.

  • ERP database: The X3 Production or replication database script provides the administrative user with access to the database schemas.

    Note

    Sage X3 is provided as an example only. You need to change the parameters according to the ERP database you are using.

The script applies to components that are installed on the same server. If some components are installed on different servers, parts of the script must be run on specific servers. For example, if Nectari is on an application server, you must run the Nectari Configuration Database and Nectari License Database scripts on that server. If the NectariCube and ERP database are on a database server, you must run the Nectari Data Warehouse and ERP Database scripts on the DB server.

To create an administrative user:

  • Run the Administrative_user.sql script. Refer to the instructions inside the script to provide the proper user names.

Step 2: Create SQL users with minimum permissions

Users with minimum permissions will only have access to these components:

  • NectariCube: The Nectari Data Warehouse script creates the NectariCube user and grants that user permissions for each database.

  • ERP database: The X3 Production or replication database script provides the administrative user with access to the database schemas.

    Note

    Sage X3 is provided as an example only. You need to change the parameters according to the ERP database you are using.

Users with minimum permissions have SELECT access to all tables. You can exclude access to specific tables by using the DENY SELECT parameter.

To create a user with minimum permissions:

  • Run the MinimumPermissions_user.sql script. Refer to the instructions inside the script to provide the proper user names.

Step 3: Add the SQL users in Nectari

After creating the users, you need to add them in Nectari.

To configure the Nectari data warehouse and ERP database users:

  1. In the Administration section , click Env. & Data Sources in the left pane.

  2. Create two environments as described in Environments and Data Sources: Production and Administration.

    • The data sources will be the Cube and your ERP (X3 in our example) in each environment.

    • For the Administration environment, enter the administrative user credentials for the Nectari schema.

    • For the Production environment, enter the credentials of the administrative user and the user with minimum permissions for the Nectari schema.

  3. Save each environment.

To configure the Nectari configuration database and BI License database users:

  1. Connect to your Central Point Configurator.

  2. In the Data Source and License Data Source sections, fill in the Username and Password fields with the administrative user credentials.

  3. Click Update Configuration.

Additional Scenarios

Granting access to a new database schema

In cases where a database schema is added, you must grant the user with minimum permissions access to the new schema.

  • Run the GRANT SELECT on SCHEMA command under the X3 Production or Replication database script for each new schema.

    Example

    We have a new database schema called TEST and we grant access to user produser1.

    GRANT SELECT on SCHEMA :: TEST to produser1

Denying access to a database schema

To deny access to a schema to a user with minimum permissions:

  • Run the DENY SELECT command under the X3 Production or Replication database script for each applicable table.

    Example

    We deny access to three tables for user produser1.

    DENY SELECT on SEED.GACCENTRYD to produser1

    DENY SELECT on SEED.GACACCOUNT to produser1

    DENY SELECT on SEED.GACCDUDATE to produser1

Altering accesses

You can provide additional accesses so that users can edit custom Nectari objects, such as:

  • Modifying Nectari schema objects: Use the GRANT ALTER on SCHEMA command from the X3 production or replication database script .

    Example

    We give full rights to administrative user adminuser1 to alter custom schema objects using the Administrative_user.sql script.

    GRANT ALTER on SCHEMA :: NECTARI_CUSTOM_SCHEMA to adminuser1

  • Modifying table structures on the Cube: Use the ALTER ROLE command from the Nectari Data Warehouse script.

    Example

    We provide Insert, Update and Delete rights to user with minimum permissions produser1 using the MinimumPermissions_user.sql script.

    ALTER ROLE db_datawriter add member produser1