Creating Oracle Users and Tablespaces

0_RevokePermissions.sql

Copy

/* Script to revoke permissions on existing users.
** 
** # Description
** The purpose of this script is to revoke the current permissions for a given user.
** 
** # Permissions
** 
** # How to use it
** - The script needs to be run from a user with administrative permissions on the server
** - Fill the variables after the comment `Change the values`
**   * NECTARI_USER: Name of the NECTARI user
**   * ENABLE_LOGING: Enable the script to print some logs
** - Run the script
*/

set serveroutput on;

DECLARE
    /*************************/
    /* Variables declaration */
    /*************************/
    NECTARI_USER                    VARCHAR2(1000);
    ENABLE_LOGING               BOOLEAN;
    -- Other variables
    v_NECTARI_USER_CHECK            NUMBER;

BEGIN
    /*********************/
    /* Change the values */
    /*********************/
    -- User information
    NECTARI_USER                       := 'NECTARI_SEED';
    -- Other
    ENABLE_LOGING                  := true;
        
    -- Enabling logs
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;
    
    /***********************************************************/
    /*                        Variables                        */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  NECTARI Username =>  ' || NECTARI_USER);
    dbms_output.new_line;
    
    /***********************************************************/
    /*                   TableSpace / Users                    */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/*    Permissions     */');
    dbms_output.put_line('/**********************/');
    -- Check if the user NECTARI Objects exists
    SELECT COUNT(1) INTO v_NECTARI_USER_CHECK FROM DBA_USERS WHERE USERNAME = NECTARI_USER;
    
    -- Creating user NECTARI Objects
    IF ( v_NECTARI_USER_CHECK <> 0 ) THEN
        dbms_output.put_line('/*******************************/');
        dbms_output.put_line('/*    Revoking Permissions     */');
        dbms_output.put_line('/*******************************/');
        
        dbms_output.put_line('  Revoking granted roles ...');
        for r in ( select * from dba_role_privs
                   where grantee = NECTARI_USER )
        loop
            EXECUTE IMMEDIATE 'revoke ' || r.granted_role ||' from ' || r.grantee;
            dbms_output.put_line('    Revoked: revoke ' || r.granted_role ||' from ' || r.grantee ||';');
        end loop;
        
        dbms_output.put_line('  Revoking granted system privileges ...');
        
        for r in ( select * from dba_sys_privs
                   where grantee = NECTARI_USER )
        loop
            EXECUTE IMMEDIATE 'revoke ' || r.privilege ||' from ' || r.grantee;
            dbms_output.put_line('    Revoked: revoke ' || r.privilege ||' from ' || r.grantee ||';');
        end loop;
        
        dbms_output.put_line('  Revoking granted access privileges ...');
        
        for r in ( select * from dba_tab_privs
                   where grantee = NECTARI_USER )
        loop
            EXECUTE IMMEDIATE 'revoke ' || r.privilege || ' on ' || r.owner||'.'||r.table_name ||' from ' || r.grantee;
            dbms_output.put_line('    Revoked: revoke ' || r.privilege || ' on ' || r.owner||'.'||r.table_name ||' from ' || r.grantee ||';');
        end loop;
    ELSE
        dbms_output.put_line('  The UserName "' || NECTARI_USER || ' doesn''t exists');
    END IF;
END;

1_ConfigDB.sql

Copy

/* Script to create users for NECTARI.
** 
** # Description
** The purpose of this script is to create an user specific for the NECTARI application.
** The script will create an Oracle user with an associated TableSpace (or can use an existing one)
** This script should be run for NECTARI and BILICENSE users
** 
** # Permissions
** The NECTARI user will have all rights on his own schema only
** This script will remove all existing permissions on the existing user
** 
** # How to use it
** - The script needs to be run from a user with administrative permissions on the server
** - Fill the variables after the comment `Change the values`
**   * NECTARI_USER: Name of the NECTARI user
**   * NECTARI_USER_PWD: Password for the user
**   * NECTARI_TABLE_SPACE: Name of the main table space for the user
**   * NECTARI_TABLE_SPACE_LOG: Enable the logging for the main table space
**   * NECTARI_TABLE_SPACE_COMP: Enable the compression for the main table space (Compression has to be enabled server wide)
**   * NECTARI_TMP_TABLE_SPACE: Name of the temporary table space
**   * ENABLE_LOGING: Enable the script to print some logs
**   * REVOKE_EXIST_PERM: If the user already exists, define if we want to remove the permissions already granted
** - Run the script
*/

set serveroutput on;

DECLARE
    /*************************/
    /* Variables declaration */
    /*************************/
    NECTARI_USER                        VARCHAR2(1000);
    NECTARI_USER_PWD                    VARCHAR2(1000);
    NECTARI_TABLE_SPACE                 VARCHAR2(1000);
    NECTARI_TABLE_SPACE_LOG             BOOLEAN;
    NECTARI_TABLE_SPACE_COMP            BOOLEAN;
    NECTARI_TMP_TABLE_SPACE             VARCHAR2(1000);
    ENABLE_LOGING                   BOOLEAN;
    REVOKE_EXIST_PERM               BOOLEAN;
    -- Other variables
    v_CUSTOM_TABLE_SPACE_PATH       VARCHAR2(1000);
    v_NECTARI_USER_CHECK                NUMBER;
    v_NECTARI_TABLE_SPACE_LOG           VARCHAR2(1000);
    v_NECTARI_TABLE_SPACE_COMP          VARCHAR2(1000);
    TablespaceExistsExcep            EXCEPTION;
    FileExistsExcep                    EXCEPTION;
    PRAGMA EXCEPTION_INIT(TablespaceExistsExcep,-1543);
    PRAGMA EXCEPTION_INIT(FileExistsExcep,-1119);
BEGIN
    /*********************/
    /* Change the values */
    /*********************/
    -- User information
    NECTARI_USER                    := 'NECTARI';
    NECTARI_USER_PWD                := 'NECTARI_PWD';
     -- TableSpace information
    NECTARI_TABLE_SPACE             := 'NECTARI';
    NECTARI_TABLE_SPACE_LOG         := true;
    NECTARI_TABLE_SPACE_COMP        := false;
    
    NECTARI_TMP_TABLE_SPACE         := 'NECTARI_TMP';
    -- Other
    ENABLE_LOGING               := true;
    REVOKE_EXIST_PERM           := false;
    
    -- Getting custom datafile path
    SELECT VALUE INTO v_CUSTOM_TABLE_SPACE_PATH FROM v$parameter WHERE NAME ='db_create_file_dest' ;
    IF v_CUSTOM_TABLE_SPACE_PATH is not null THEN
        v_CUSTOM_TABLE_SPACE_PATH := v_CUSTOM_TABLE_SPACE_PATH || '/';
    END IF;
        
    -- Enabling logs
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;
    
    -- Preparing setting for the table spaces
    -- NECTARI
    IF ( NECTARI_TABLE_SPACE_LOG = true ) THEN
        v_NECTARI_TABLE_SPACE_LOG := '';
    ELSE
        v_NECTARI_TABLE_SPACE_LOG := ' NOLOGGING ';
    END IF;
    
    IF ( NECTARI_TABLE_SPACE_COMP = true ) THEN
        v_NECTARI_TABLE_SPACE_COMP := ' DEFAULT COMPRESS ';
    ELSE
        v_NECTARI_TABLE_SPACE_COMP := '';
    END IF;
    
    /***********************************************************/
    /*                        Variables                        */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  NECTARI Username =>  ' || NECTARI_USER);
    dbms_output.put_line('  NECTARI Password => ' || NECTARI_USER_PWD);
    dbms_output.put_line('  /* Table space informations */');
    dbms_output.put_line('    NECTARI TableSpace');
    dbms_output.put_line('      Name =>  ' || NECTARI_TABLE_SPACE);
    dbms_output.put_line('      Loging =>  ' || sys.diutil.bool_to_int(NECTARI_TABLE_SPACE_LOG));
    dbms_output.put_line('      Compression =>  ' || sys.diutil.bool_to_int(NECTARI_TABLE_SPACE_COMP));
    dbms_output.put_line('    NECTARI TMP TableSpace');
    dbms_output.put_line('      Name =>  ' || NECTARI_TMP_TABLE_SPACE);
    
    IF v_CUSTOM_TABLE_SPACE_PATH is not null THEN
        dbms_output.put_line('    Custom datafile path detected =>  ' || v_CUSTOM_TABLE_SPACE_PATH);
    END IF;
    dbms_output.new_line;
    
    /***********************************************************/
    /*                   TableSpace / Users                    */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* TableSpace / Users */');
    dbms_output.put_line('/**********************/');
    -- Check if the user NECTARI exists
    SELECT COUNT(1) INTO v_NECTARI_USER_CHECK FROM DBA_USERS WHERE USERNAME = NECTARI_USER;
    
    -- Main TableSpace
    IF NECTARI_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE TABLESPACE '
                                  || NECTARI_TABLE_SPACE
                                  || ' DATAFILE '''
                                  || v_CUSTOM_TABLE_SPACE_PATH || NECTARI_TABLE_SPACE
                                  || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M'
                                  || v_NECTARI_TABLE_SPACE_COMP
                                  || v_NECTARI_TABLE_SPACE_LOG;
            dbms_output.put_line('  TableSpace "' || NECTARI_TABLE_SPACE || '" created');
        EXCEPTION
            WHEN TablespaceExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || NECTARI_TABLE_SPACE || '" already exists');
                NULL;
            WHEN FileExistsExcep THEN
                dbms_output.put_line('  The file associated to the TableSpace "' || NECTARI_TABLE_SPACE || '" already exists');
                NULL;
        END;
    ELSE
        dbms_output.put_line('  The NECTARI_TABLE_SPACE variable was not filled so tablespace will not be created');
    END IF;
    
    -- Temp TableSpace
    IF NECTARI_TMP_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLESPACE '
                                  || NECTARI_TMP_TABLE_SPACE
                                  || ' TEMPFILE '''
                                  || v_CUSTOM_TABLE_SPACE_PATH || NECTARI_TMP_TABLE_SPACE
                                  || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M';                                
            dbms_output.put_line('  TableSpace "' || NECTARI_TMP_TABLE_SPACE || '" created');
        EXCEPTION
            WHEN TablespaceExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || NECTARI_TMP_TABLE_SPACE || '" already exists');
            NULL;
            WHEN FileExistsExcep THEN
                dbms_output.put_line('  The file associated to the TableSpace "' || NECTARI_TMP_TABLE_SPACE || '" already exists');
            NULL;
        END;
    ELSE
        dbms_output.put_line('  The NECTARI_TMP_TABLE_SPACE variable was not filled so tablespace will not be created');
    END IF;
    
    -- Creating user NECTARI
    IF ( v_NECTARI_USER_CHECK = 0 ) THEN
        EXECUTE IMMEDIATE 'CREATE USER '
                          || NECTARI_USER
                          || ' IDENTIFIED BY '
                          || NECTARI_USER_PWD
                          || '  DEFAULT TABLESPACE '
                          || NECTARI_TABLE_SPACE
                          || ' TEMPORARY TABLESPACE '
                          || NECTARI_TMP_TABLE_SPACE
                          || ' QUOTA UNLIMITED ON '
                          || NECTARI_TABLE_SPACE;
        dbms_output.put_line('  UserName "' || NECTARI_USER || ' created');
    ELSE
        dbms_output.put_line('  The UserName "' || NECTARI_USER || ' already exists');

        IF ( REVOKE_EXIST_PERM = true ) THEN
            dbms_output.put_line('/*******************************/');
            dbms_output.put_line('/*    Revoking Permissions     */');
            dbms_output.put_line('/*******************************/');
            
            dbms_output.put_line('  Revoking granted roles ...');
            for r in ( select * from dba_role_privs
                    where grantee = NECTARI_USER )
            loop
                EXECUTE IMMEDIATE 'revoke ' || r.granted_role ||' from ' || r.grantee;
                dbms_output.put_line('    Revoked: revoke ' || r.granted_role ||' from ' || r.grantee ||';');
            end loop;
            
            dbms_output.put_line('  Revoking granted system privileges ...');
            
            for r in ( select * from dba_sys_privs
                    where grantee = NECTARI_USER )
            loop
                EXECUTE IMMEDIATE 'revoke ' || r.privilege ||' from ' || r.grantee;
                dbms_output.put_line('    Revoked: revoke ' || r.privilege ||' from ' || r.grantee ||';');
            end loop;
            
            dbms_output.put_line('  Revoking granted access privileges ...');
            
            for r in ( select * from dba_tab_privs
                    where grantee = NECTARI_USER )
            loop
                EXECUTE IMMEDIATE 'revoke ' || r.privilege || ' on ' || r.owner||'.'||r.table_name ||' from ' || r.grantee;
                dbms_output.put_line('    Revoked: revoke ' || r.privilege || ' on ' || r.owner||'.'||r.table_name ||' from ' || r.grantee ||';');
            end loop;
        ELSE
            dbms_output.put_line('  Current permissions of "' || NECTARI_USER || ' won''t be impacted');
        END IF;
        
    END IF;
    
    /***********************************************************/
    /*                       Permissions                       */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/*    Permissions     */');
    dbms_output.put_line('/**********************/');
    
    -- Grant General Authorization for NECTARI
    EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CONNECT, CREATE TYPE, CREATE VIEW, CREATE SYNONYM, CREATE TRIGGER, RESOURCE TO '
    || NECTARI_USER;
    dbms_output.put_line('  Username '|| NECTARI_USER || ' has been granted the following permissions on his own shemas:');
    dbms_output.put_line('    - CREATE PROCEDURE');
    dbms_output.put_line('    - CREATE SESSION');
    dbms_output.put_line('    - CREATE TABLE');
    dbms_output.put_line('    - CONNECT');
    dbms_output.put_line('    - CREATE TYPE');
    dbms_output.put_line('    - CREATE VIEW');
    dbms_output.put_line('    - CREATE SYNONYM');
    dbms_output.put_line('    - CREATE TRIGGER');
END;

2_DataDB.sql

Copy

/* Script to create users for NECTARI Cube.
** 
** # Description
** The purpose of this script is to create an user specific for the NECTARI application.
** The script will create an Oracle user with an associated TableSpace (or can use an existing one)
** The script will also give some permissions on other schemas (users)
** 
** # Permissions
** The NECTARI_SEED user will have all rights on his own schema
** The NECTARI_SEED user will have the right to read all tables from the specified schema in the variable `SCHEMA_LIST`
** The NECTARI_SEED user will have the right to read all views from the specified schema in the variable `SCHEMA_LIST`
** The NECTARI_SEED user will have the right to create any trigger across the database
** It will be possible to specify some exclusions tables/views to not give access, see more info in the How to use it section
** This script will remove all existing permissions on the existing user
** 
** # How to use it
** - The script needs to be run from a user with administrative permissions on the server
** - Fill the variables after the comment `Change the values`
**   * NECTARI_CUSTOM_USER: Name of the NECTARI Cube user
**   * NECTARI_CUSTOM_USER_PWD: Password for the user
**   * NECTARI_CUSTOM_TABLE_SPACE: Name of the main table space for the user
**   * NECTARI_CUSTOM_TABLE_SPACE_LOG: Enable the logging for the main table space
**   * NECTARI_CUSTOM_TABLE_SPACE_COMP: Enable the compression for the main table space (Compression has to be enabled server wide)
**   * NECTARI_CUSTOM_TMP_TABLE_SPACE: Name of the temporary table space
**   * ENABLE_LOGING: Enable the script to print some logs
**   * REVOKE_EXIST_PERM: If the user already exists, define if we want to remove the permissions already granted
**
**   The following parameters are for the permissions
**   * SCHEMA_LIST: List of the schemas (users) tables/views that our user will be able to READ (SELECT) only
**   * EXCLUSION_TB_LIST: List of the tables to exclude for the grant select. Format is "SCHEMA"."TABLE"
**   * EXCLUSION_VW_LIST: List of the views to exclude for the grant select. Format is "SCHEMA"."VIEW"
** - Run the script
**
** # Things to know
** !!! In Oracle, to give the READ access on another schema, we need to do it table by table.
**     => By doing so the grants are point in time and if new tables are added in the future, the script will need to be re-run (it will only update the table grants).
** !!! In Oracle, to give the CREATE TABLE/TRIGGER permission on another schema, the user needs to have a server wide permission so it can create them in amy schema.
*/

set serveroutput on;

DECLARE
    /*************************/
    /* Variables declaration */
    /*************************/
    NECTARI_CUSTOM_USER                 VARCHAR2(1000);
    NECTARI_CUSTOM_USER_PWD             VARCHAR2(1000);
    NECTARI_CUSTOM_TABLE_SPACE          VARCHAR2(1000);
    NECTARI_CUSTOM_TABLE_SPACE_LOG      BOOLEAN;
    NECTARI_CUSTOM_TABLE_SPACE_COMP     BOOLEAN;
    NECTARI_CUSTOM_TMP_TABLE_SPACE      VARCHAR2(1000);
    SCHEMA_LIST                     dbms_sql.varchar2_table;
    EXCLUSION_TB_LIST               dbms_sql.varchar2_table;
    EXCLUSION_VW_LIST               dbms_sql.varchar2_table;
    ENABLE_LOGING                   BOOLEAN;
    REVOKE_EXIST_PERM               BOOLEAN;
    -- Other variables
    v_CUSTOM_TABLE_SPACE_PATH       VARCHAR2(1000);
    v_NECTARI_CUSTOM_USER_CHECK         NUMBER;
    v_NECTARI_CUSTOM_TABLE_SPACE_LOG    VARCHAR2(1000);
    v_NECTARI_CUSTOM_TABLE_SPACE_COMP   VARCHAR2(1000);
    v_NECTARI_CUSTOM_TMP_TS_COMP        VARCHAR2(1000);
    TablespaceExistsExcep            EXCEPTION;
    FileExistsExcep                    EXCEPTION;
    PRAGMA EXCEPTION_INIT(TablespaceExistsExcep,-1543);
    PRAGMA EXCEPTION_INIT(FileExistsExcep,-1119);
BEGIN
    /*********************/
    /* Change the values */
    /*********************/
    -- User information
    NECTARI_CUSTOM_USER                     := 'NECTARI_SEED';
    NECTARI_CUSTOM_USER_PWD                 := 'NECTARI_SEED_PWD';
     -- TableSpace information
    NECTARI_CUSTOM_TABLE_SPACE              := 'NECTARI_SEED';
    NECTARI_CUSTOM_TABLE_SPACE_LOG          := true;
    NECTARI_CUSTOM_TABLE_SPACE_COMP         := false;
    
    NECTARI_CUSTOM_TMP_TABLE_SPACE          := 'NECTARI_SEED_TMP';
    -- List of schemas
    SCHEMA_LIST(1)                      := 'X3FOLDER';
    -- Exclusion list
    --EXCLUSION_TB_LIST(1)                := '"X3FOLDER"."TABLE"';
    --EXCLUSION_VW_LIST(1)                := '"X3FOLDER"."VIEW"';
    -- Other
    ENABLE_LOGING                       := true;
    REVOKE_EXIST_PERM                   := false;
    
    -- Getting custom datafile path
    SELECT VALUE INTO v_CUSTOM_TABLE_SPACE_PATH FROM v$parameter WHERE NAME ='db_create_file_dest' ;
    IF v_CUSTOM_TABLE_SPACE_PATH is not null THEN
        v_CUSTOM_TABLE_SPACE_PATH := v_CUSTOM_TABLE_SPACE_PATH || '/';
    END IF;
        
    -- Enabling logs
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;
    
    -- Preparing setting for the table spaces
    -- NECTARI Objetcs
    IF ( NECTARI_CUSTOM_TABLE_SPACE_LOG = true ) THEN
        v_NECTARI_CUSTOM_TABLE_SPACE_LOG := '';
    ELSE
        v_NECTARI_CUSTOM_TABLE_SPACE_LOG := ' NOLOGGING ';
    END IF;
    
    IF ( NECTARI_CUSTOM_TABLE_SPACE_COMP = true ) THEN
        v_NECTARI_CUSTOM_TABLE_SPACE_COMP := ' DEFAULT COMPRESS ';
    ELSE
        v_NECTARI_CUSTOM_TABLE_SPACE_COMP := '';
    END IF;
    
    /***********************************************************/
    /*                        Variables                        */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  NECTARI Objects Username =>  ' || NECTARI_CUSTOM_USER);
    dbms_output.put_line('  NECTARI Objects Password => ' || NECTARI_CUSTOM_USER_PWD);
    dbms_output.put_line('  /* Table space informations */');
    dbms_output.put_line('    NECTARI Objects TableSpace');
    dbms_output.put_line('      Name =>  ' || NECTARI_CUSTOM_TABLE_SPACE);
    dbms_output.put_line('      Loging =>  ' || sys.diutil.bool_to_int(NECTARI_CUSTOM_TABLE_SPACE_LOG));
    dbms_output.put_line('      Compression =>  ' || sys.diutil.bool_to_int(NECTARI_CUSTOM_TABLE_SPACE_COMP));
    dbms_output.put_line('    NECTARI Objects TMP TableSpace');
    dbms_output.put_line('      Name =>  ' || NECTARI_CUSTOM_TMP_TABLE_SPACE);
    IF v_CUSTOM_TABLE_SPACE_PATH is not null THEN
        dbms_output.put_line('    Custom datafile path detected =>  ' || v_CUSTOM_TABLE_SPACE_PATH);
    END IF;
    FOR i IN SCHEMA_LIST.FIRST .. SCHEMA_LIST.LAST
    LOOP
        dbms_output.put_line('  Schemas =>  ' || SCHEMA_LIST(i));
    END LOOP;
    IF EXCLUSION_TB_LIST.count > 0 THEN
        FOR i IN EXCLUSION_TB_LIST.FIRST .. EXCLUSION_TB_LIST.LAST
        LOOP
            dbms_output.put_line('  Exclusion tables =>  ' || EXCLUSION_TB_LIST(i));
        END LOOP;
    END IF;
    IF EXCLUSION_VW_LIST.count > 0 THEN
        FOR i IN EXCLUSION_VW_LIST.FIRST .. EXCLUSION_VW_LIST.LAST
        LOOP
            dbms_output.put_line('  Exclusion views =>  ' || EXCLUSION_VW_LIST(i));
        END LOOP;
    END IF;
    dbms_output.new_line;
    
    /***********************************************************/
    /*                   TableSpace / Users                    */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* TableSpace / Users */');
    dbms_output.put_line('/**********************/');
    -- Check if the user NECTARI Objects exists
    SELECT COUNT(1) INTO v_NECTARI_CUSTOM_USER_CHECK FROM DBA_USERS WHERE USERNAME = NECTARI_CUSTOM_USER;
    
    -- Main TableSpace
    IF NECTARI_CUSTOM_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE TABLESPACE '
                                  || NECTARI_CUSTOM_TABLE_SPACE
                                  || ' DATAFILE '''
                                  || v_CUSTOM_TABLE_SPACE_PATH || NECTARI_CUSTOM_TABLE_SPACE
                                  || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M'
                                  || v_NECTARI_CUSTOM_TABLE_SPACE_COMP
                                  || v_NECTARI_CUSTOM_TABLE_SPACE_LOG;
            dbms_output.put_line('  TableSpace "' || NECTARI_CUSTOM_TABLE_SPACE || '" created');
        EXCEPTION
            WHEN TablespaceExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || NECTARI_CUSTOM_TABLE_SPACE || '" already exists');
            NULL;
            WHEN FileExistsExcep THEN
                dbms_output.put_line('  The file associated to the TableSpace "' || NECTARI_CUSTOM_TABLE_SPACE || '" already exists');
            NULL;
        END;
    ELSE
        dbms_output.put_line('  The NECTARI_CUSTOM_TABLE_SPACE variable was not filled so tablespace will not be created');
    END IF;
    
    -- Temp TableSpace
    IF NECTARI_CUSTOM_TMP_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLESPACE '
                                  || NECTARI_CUSTOM_TMP_TABLE_SPACE
                                  || ' TEMPFILE '''
                                  || v_CUSTOM_TABLE_SPACE_PATH || NECTARI_CUSTOM_TMP_TABLE_SPACE
                                  || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M';
            dbms_output.put_line('  TableSpace "' || NECTARI_CUSTOM_TMP_TABLE_SPACE || '" created');
        EXCEPTION
            WHEN TablespaceExistsExcep THEN
                dbms_output.put_line('  The TableSpace "' || NECTARI_CUSTOM_TMP_TABLE_SPACE || '" already exists');
            NULL;
            WHEN FileExistsExcep THEN
                dbms_output.put_line('  The file associated to the TableSpace "' || NECTARI_CUSTOM_TMP_TABLE_SPACE || '" already exists');
            NULL;
        END;
    ELSE
        dbms_output.put_line('  The NECTARI_CUSTOM_TMP_TABLE_SPACE variable was not filled so tablespace will not be created');
    END IF;
    
    -- Creating user NECTARI Objects
    IF ( v_NECTARI_CUSTOM_USER_CHECK = 0 ) THEN
        EXECUTE IMMEDIATE 'CREATE USER '
                          || NECTARI_CUSTOM_USER
                          || ' IDENTIFIED BY '
                          || NECTARI_CUSTOM_USER_PWD
                          || '  DEFAULT TABLESPACE '
                          || NECTARI_CUSTOM_TABLE_SPACE
                          || ' TEMPORARY TABLESPACE '
                          || NECTARI_CUSTOM_TMP_TABLE_SPACE
                          || ' QUOTA UNLIMITED ON '
                          || NECTARI_CUSTOM_TABLE_SPACE;
        dbms_output.put_line('  UserName "' || NECTARI_CUSTOM_USER || ' created');
    ELSE
        dbms_output.put_line('  The UserName "' || NECTARI_CUSTOM_USER || ' already exists');

        IF ( REVOKE_EXIST_PERM = true ) THEN
            dbms_output.put_line('/*******************************/');
            dbms_output.put_line('/*    Revoking Permissions     */');
            dbms_output.put_line('/*******************************/');
            
            dbms_output.put_line('  Revoking granted roles ...');
            for r in ( select * from dba_role_privs
                    where grantee = NECTARI_CUSTOM_USER )
            loop
                EXECUTE IMMEDIATE 'revoke ' || r.granted_role ||' from ' || r.grantee;
                dbms_output.put_line('    Revoked: revoke ' || r.granted_role ||' from ' || r.grantee ||';');
            end loop;
            
            dbms_output.put_line('  Revoking granted system privileges ...');
            
            for r in ( select * from dba_sys_privs
                    where grantee = NECTARI_CUSTOM_USER )
            loop
                EXECUTE IMMEDIATE 'revoke ' || r.privilege ||' from ' || r.grantee;
                dbms_output.put_line('    Revoked: revoke ' || r.privilege ||' from ' || r.grantee ||';');
            end loop;
            
            dbms_output.put_line('  Revoking granted access privileges ...');
            
            for r in ( select * from dba_tab_privs
                    where grantee = NECTARI_CUSTOM_USER )
            loop
                EXECUTE IMMEDIATE 'revoke ' || r.privilege || ' on ' || r.owner||'.'||r.table_name ||' from ' || r.grantee;
                dbms_output.put_line('    Revoked: revoke ' || r.privilege || ' on ' || r.owner||'.'||r.table_name ||' from ' || r.grantee ||';');
            end loop;
        ELSE
            dbms_output.put_line('  Current permissions of "' || NECTARI_CUSTOM_USER || ' won''t be impacted');
        END IF;
    END IF;
    
    /***********************************************************/
    /*                       Permissions                       */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/*    Permissions     */');
    dbms_output.put_line('/**********************/');
    
    -- Grant General Authorization for NECTARI Objects
    EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CONNECT, CREATE TYPE, CREATE VIEW, CREATE SYNONYM, CREATE TRIGGER, RESOURCE TO '
    || NECTARI_CUSTOM_USER;
    dbms_output.put_line('  Username '|| NECTARI_CUSTOM_USER || ' has been granted the following permissions on his own shemas:');
    dbms_output.put_line('    - CREATE PROCEDURE');
    dbms_output.put_line('    - CREATE SESSION');
    dbms_output.put_line('    - CREATE TABLE');
    dbms_output.put_line('    - CONNECT');
    dbms_output.put_line('    - CREATE TYPE');
    dbms_output.put_line('    - CREATE VIEW');
    dbms_output.put_line('    - CREATE SYNONYM');
    dbms_output.put_line('    - CREATE TRIGGER');
    
    -- Grant Trigger Authorization for NECTARI Objects
    EXECUTE IMMEDIATE 'GRANT CREATE ANY TRIGGER TO ' || NECTARI_CUSTOM_USER;
    dbms_output.put_line('  Username '|| NECTARI_CUSTOM_USER || ' has been granted the CREATE ANY TRIGGER permissions');
    dbms_output.put_line('    !!! The CREATE ANY TRIGGER applies server wide !!!');
    
    -- Grant READ permission on schemas for NECTARI Objects users
    FOR i IN SCHEMA_LIST.FIRST .. SCHEMA_LIST.LAST
    LOOP
        dbms_output.put_line('  Iterating on tables for schema ' || SCHEMA_LIST(i));
        FOR x IN (
            SELECT table_name FROM dba_all_tables WHERE owner = SCHEMA_LIST(i)
        ) LOOP
            EXECUTE IMMEDIATE 'GRANT SELECT ON "'
                              || SCHEMA_LIST(i)
                              || '"."'
                              || x.table_name
                              || '" TO '
                              || NECTARI_CUSTOM_USER;
            dbms_output.put_line('    Granting SELECT permissions on '
                                 || SCHEMA_LIST(i)
                                 || '.'
                                 || x.table_name);
        END LOOP;
        dbms_output.put_line('  Iterating on views for schema ' || SCHEMA_LIST(i));
        FOR x IN (
            SELECT
                view_name
            FROM
                dba_views
            WHERE
                    owner = SCHEMA_LIST(i)
                AND view_name <> 'LEGGTEYGRP'
        ) LOOP
            EXECUTE IMMEDIATE 'GRANT SELECT ON "'
                              || SCHEMA_LIST(i)
                              || '"."'
                              || x.view_name
                              || '" TO '
                              || NECTARI_CUSTOM_USER;
            dbms_output.put_line('  Granting SELECT permissions on '
                                 || SCHEMA_LIST(i)
                                 || '.'
                                 || x.view_name);
        END LOOP;
    END LOOP;
    
    -- Revoking permissions
    dbms_output.put_line('  Revoking permissions');
    IF EXCLUSION_TB_LIST.count > 0 THEN
        FOR i IN EXCLUSION_TB_LIST.FIRST .. EXCLUSION_TB_LIST.LAST
        LOOP
            EXECUTE IMMEDIATE 'REVOKE SELECT ON '
                                  || EXCLUSION_TB_LIST(i)
                                  || ' FROM '
                                  || NECTARI_CUSTOM_USER;
            dbms_output.put_line('    Revoking select access for table ' || EXCLUSION_TB_LIST(i));
        END LOOP;
    END IF;
    IF EXCLUSION_VW_LIST.count > 0 THEN
        FOR i IN EXCLUSION_VW_LIST.FIRST .. EXCLUSION_VW_LIST.LAST
        LOOP
            EXECUTE IMMEDIATE 'REVOKE SELECT ON '
                                  || EXCLUSION_TB_LIST(i)
                                  || ' FROM '
                                  || NECTARI_CUSTOM_USER;
            dbms_output.put_line('    Revoking select access for view ' || EXCLUSION_VW_LIST(i));
        END LOOP;
    END IF;
END;

3_AddNewDataFile.sql

Copy

/* Script to add a new datafile to an existing table space.
** 
** # Description
** The purpose of this script is to add a new datafile to a table space to avoid storage issue.
** 
** # How to use it
** - The script needs to be run from a user with administrative permissions on the server
** - Fill the variables after the comment `Change the values`
**   * TABLE_SPACE: Name of the table space to update
**   * DATA_FILE_NAME: Name of the data file to add
**   * ENABLE_LOGING: Enable the script to print some logs
** - Run the script
**
** # Things to know
** !!! You can change the size, maxsize, etc. directly in the script to accomodate your need
*/

set serveroutput on;

DECLARE
    /*************************/
    /* Variables declaration */
    /*************************/
    TABLE_SPACE             VARCHAR2(1000);
    DATA_FILE_NAME          VARCHAR2(1000);
    ENABLE_LOGING           BOOLEAN;
    -- Other variables
    v_CUSTOM_TABLE_SPACE_PATH       VARCHAR2(1000);
    TablespaceExistsExcep    EXCEPTION;
    FileExistsExcep            EXCEPTION;
    PRAGMA EXCEPTION_INIT(TablespaceExistsExcep,-1543);
    PRAGMA EXCEPTION_INIT(FileExistsExcep,-1537);
BEGIN
    /*********************/
    /* Change the values */
    /*********************/
    TABLE_SPACE         := 'NECTARI_SEED';
    DATA_FILE_NAME      := 'NECTARI_SEED_NEW2';
    ENABLE_LOGING       := true;
    
    -- Enabling logs
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;
    
    -- Getting custom datafile path
    SELECT VALUE INTO v_CUSTOM_TABLE_SPACE_PATH FROM v$parameter WHERE NAME ='db_create_file_dest' ;
    IF v_CUSTOM_TABLE_SPACE_PATH is not null THEN
        v_CUSTOM_TABLE_SPACE_PATH := v_CUSTOM_TABLE_SPACE_PATH || '/';
    END IF;
    
    /***********************************************************/
    /*                        Variables                        */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  Table space name =>  ' || TABLE_SPACE);
    dbms_output.put_line('  Datafile name =>  ' || DATA_FILE_NAME);
    IF v_CUSTOM_TABLE_SPACE_PATH is not null THEN
        dbms_output.put_line('    Custom datafile path detected =>  ' || v_CUSTOM_TABLE_SPACE_PATH);
    END IF;
    
    /***********************************************************/
    /*                 TableSpace / Datafile                   */
    /***********************************************************/
    dbms_output.put_line('/*************************/');
    dbms_output.put_line('/* TableSpace / Datafile */');
    dbms_output.put_line('/*************************/');
    IF TABLE_SPACE is not null THEN
        IF DATA_FILE_NAME is not null THEN
            BEGIN
                EXECUTE IMMEDIATE 'ALTER TABLESPACE '
                                      || TABLE_SPACE
                                      || ' ADD DATAFILE '''
                                      || v_CUSTOM_TABLE_SPACE_PATH || DATA_FILE_NAME
                                      || '.DBF'' SIZE 1024M AUTOEXTEND ON MAXSIZE 32767M';
                dbms_output.put_line('  TableSpace "' || TABLE_SPACE || '" updated to use also the datafile ' || v_CUSTOM_TABLE_SPACE_PATH || DATA_FILE_NAME || '.DBF');
            EXCEPTION
                WHEN TablespaceExistsExcep THEN
                    dbms_output.put_line('  The TableSpace "' || TABLE_SPACE || '" already exists');
                NULL;
                WHEN FileExistsExcep THEN
                    dbms_output.put_line('  The date file "' || DATA_FILE_NAME || '" already exists');
                NULL;
            END;
        ELSE
            dbms_output.put_line('  The DATA_FILE_NAME variable was not filled so databfile will not be created');
        END IF;
    ELSE
        dbms_output.put_line('  The TABLE_SPACE variable was not filled so tablespace will not be updated');
    END IF;
END;

9_UninstallDB.sql

Copy

/* Script to uninstall users.
** 
** # Description
** The purpose of this script is to properly uninstall a NECTARI user.
** 
** # How to use it
** - The script needs to be run from a user with administrative permissions on the server
** - Fill the variables after the comment `Change the values`
**   * NECTARI_USER: Name of the NECTARI Cube user
**   * ENABLE_LOGING: Enable the script to print some logs
** - Run the script
*/

set serveroutput on;

DECLARE
    /*************************/
    /* Variables declaration */
    /*************************/
    NECTARI_USER                   VARCHAR2(1000);
    NECTARI_TABLE_SPACE            VARCHAR2(1000);
    NECTARI_TMP_TABLE_SPACE        VARCHAR2(1000);
    ENABLE_LOGING              BOOLEAN;
    -- Other variables
    v_NECTARI_USER_CHECK           NUMBER;
    TablespaceDOESNTExistsExcep       EXCEPTION;
    PRAGMA EXCEPTION_INIT(TablespaceDOESNTExistsExcep,-959);
BEGIN
    /*********************/
    /* Change the values */
    /*********************/
    -- User information
    NECTARI_USER                    := 'NECTARI';
    -- TableSpace information
    NECTARI_TABLE_SPACE             := 'NECTARI';
    NECTARI_TMP_TABLE_SPACE         := 'NECTARI_TMP';
    -- Other
    ENABLE_LOGING               := true;
    
    -- Enabling logs
    IF ( ENABLE_LOGING = true ) THEN
        dbms_output.enable(1000000);
    ELSE
        dbms_output.disable;
    END IF;

    /***********************************************************/
    /*                        Variables                        */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* Variables summary  */');
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('  NECTARI Objects Username =>  ' || NECTARI_USER);
    dbms_output.put_line('  /* Table space informations */');
    dbms_output.put_line('    NECTARI TableSpace');
    dbms_output.put_line('      Name =>  ' || NECTARI_TABLE_SPACE);
    dbms_output.put_line('    NECTARI TMP TableSpace');
    dbms_output.put_line('      Name =>  ' || NECTARI_TMP_TABLE_SPACE);
    dbms_output.new_line;
    
    /***********************************************************/
    /*                   TableSpace / Users                    */
    /***********************************************************/
    dbms_output.put_line('/**********************/');
    dbms_output.put_line('/* TableSpace / Users */');
    dbms_output.put_line('/**********************/');
    -- Check if the user NECTARI Objects exists
    SELECT COUNT(1) INTO v_NECTARI_USER_CHECK FROM DBA_USERS WHERE USERNAME = NECTARI_USER;
    
    -- User
    IF ( v_NECTARI_USER_CHECK = 1 ) THEN
        -- Killing sessions
        FOR r IN (select sid,serial# from v$session where username = NECTARI_USER)
        LOOP
            EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || r.sid  || ',' 
                || r.serial# || ''' IMMEDIATE';
            dbms_output.put_line('  Killing session with serial "' || r.serial# || ' for user ' || NECTARI_USER);
        END LOOP;
        -- Can be usefull to manually kill the session
        --SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session where username=NECTARI_USER;
        EXECUTE IMMEDIATE 'DROP USER ' || NECTARI_USER || ' CASCADE';
        dbms_output.put_line('  UserName "' || NECTARI_USER || ' dropped');
    ELSE
        dbms_output.put_line('  The UserName "' || NECTARI_USER || ' doesn''t exists');
    END IF;
    
    -- Main TableSpace
    IF NECTARI_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLESPACE '
                                || NECTARI_TABLE_SPACE
                                || ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
                dbms_output.put_line('  TableSpace "' || NECTARI_TABLE_SPACE || '" dropped');
            EXCEPTION
                WHEN TablespaceDOESNTExistsExcep THEN
                    dbms_output.put_line('  The TableSpace "' || NECTARI_TABLE_SPACE || '" doesn''t exists');
                    NULL;
            END;
        ELSE
            dbms_output.put_line('  The NECTARI_TABLE_SPACE variable was not filled so tablespace will not be dropped');
    END IF;
    
    -- Temp TableSpace
    IF NECTARI_TMP_TABLE_SPACE is not null THEN
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLESPACE '
                                || NECTARI_TMP_TABLE_SPACE
                                || ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
                dbms_output.put_line('  TableSpace "' || NECTARI_TMP_TABLE_SPACE || '" dropped');
            EXCEPTION
                WHEN TablespaceDOESNTExistsExcep THEN
                    dbms_output.put_line('  The TableSpace "' || NECTARI_TMP_TABLE_SPACE || '" doesn''t exists');
                    NULL;
            END;
        ELSE
            dbms_output.put_line('  The NECTARI_TABLE_SPACE variable was not filled so tablespace will not be dropped');
    END IF;
END;

Oracle Component Definitions

SID: Oracle System ID. It uniquely identifies a database (for example: ORCL).
User: In Oracle, users and schemas are essentially the same thing. A user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.
Tablespace: An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data. Each tablespace consists of one or more files called datafiles, which are physical structures that conform to the operating system on which Oracle is running.

Note
Nectari supports Oracle tablespace functionalities such as DEFAULT ROW STORE COMPRESS ADVANCED. However, many Oracle tablespace functionalities (including the one previously mentioned) are additional features that must be purchased separately from Oracle. Please contact your Oracle DBA if you would like to learn more about purchasing licenses for additional tablespace functionalities.
Temp Tablespace: A temporary tablespace, as its name implies, stores the temporary data that only exists during the database session. Oracle uses temporary tablespaces to improve the concurrency of multiple sort operations which do not fit in memory. The TEMP tablespace can be shared by multiple users.
Datafile: Datafiles are physical files of the operating system that store the data of all logical structures in the database. They must be explicitly created for each tablespace. Oracle assigns each datafile two file numbers, an absolute file number and a relative file number, that are used to uniquely identify it. Multiple datafiles can be added to the same tablespace (regular or temp).
Block Size: Size of the block for the data. The size of the block can be different by tablespace but it is usually the same. The size of the block will have a direct impact on the maximum size of the datafile.

Storage

Block size: The default block size is often 8 Kb so the datafile that can have a maximum of 32 GB.

To get the current block size of the database, run the following SQL command:

select value from v$parameter where name = 'db_block_size';

Block size	Maximum datafile size	Maximum database size
2 KB	8 GB	512 TB
4 KB	16 GB	1 PB
8 KB	32 GB	2 PB
16 KB	64 GB	4 PB
32 KB	128 GB	8 PB

Tablespace and datafile: By default, the tablespace contains only one data file. Based on the block size, the default size of a tablespace will only be 32 GB. You need to add more datafiles to a tablespace to store more data (regular or temp tablespace). The maximum number of datafiles that can be added is 65 536 files by database, hence the maximum database size mentioned above (for example: 32 GB x 65 536 = 2 PB). The maximum number of datafiles in a tablespace is 1022 (32 TB for an 8 Kb block size).

To add more datafiles to a tablespace, run the following SQL command (adjust this command with your own name and desired sizes):

ALTER TABLESPACE YOUR_TABLE_SPACE ADD DATAFILE YOURNAME.DBF' size 1024m autoextend on maxsize 32767m;

To get the list of datafiles attached to a tablespace, run the following SQL command:

SELECT TABLESPACE_NAME,FILE_NAME From DBA_DATA_FILES;
Temp tablespace: The temp tablespace can grow quite big once OLAP Manager has loaded the data (full load). However, the used space remains stable for subsequent loads because it will not be allocated anymore.

Oracle uses temporary tablespaces to improve the concurrency of multiple sort operations that do not fit in memory.

To see the space allocation of a temporary tablespace, run the following command:

SELECT * FROM dba_temp_free_space;

To add more datafiles to an existing tablespace, run the following SQL (adjust this command with your own name and desired sizes):

ALTER TABLESPACE YOUR_TABLE_SPACE ADD TEMPFILE 'YOURNAME.DBF' size 1024m autoextend on maxsize 32767m ;

Logging and compression

Logging: In Oracle logging is activated by default on tablespace creation. In the case of the Nectari data warehouse, logging is not required because we simply reload the data warehouse instead of using the undo/redo feature. Since logging has a direct impact on the temp tablespace size, it is recommended to deactivate it whenever possible.

To create a new tablespace without logging, run this command:

create tablespace your_table_space datafile 'your_name.DBF' size 1024m autoextend on maxsize 32767m NOLOGGING;

To remove logging for an existing tablespace:

ALTER TABLESPACE NectariCUBE3 NOLOGGING;

Compression: There are multiple ways of compressing data in Oracle, but the one we are describing here for Nectari is Advanced Row Compression. Advanced Row Compression has many benefits. One significant advantage is Oracle’s ability to read compressed blocks (data and indexes) directly in memory, without uncompressing the blocks. This improves performance thanks to the reduction in I/O, and the reduction in system calls related to the I/O operations. Furthermore, the buffer cache becomes more efficient by storing data without having to add memory.

To create a new tablespace with compression and without logging, run this command:

create tablespace your_table_space datafile 'your_name.DBF' size 1024m autoextend on maxsize 32767m DEFAULT ROW STORE COMPRESS ADVANCED NOLOGGING;

The table below describes a logging and compression test on a cube where the source database had more than 190 million rows. The generated master slice generated resulted in 111 million rows and 3 other slices were over 94 million rows. All scenarios clearly show that logging must be deactivated. It is strongly recommended to use compression since the time to load was not much different and the storage was greatly reduced.

Block size	Compression	Logging	Loading time	Temp size	Data size	Total
8 Kb	Yes	Yes	1h28	24.7	68.8	93.5
8 Kb	No	Yes	1h16	83.2	106	189.2
8 Kb	Yes	No	1h28	25.2	70.2	95.4
8 Kb	No	No	1h12	28	108	136
16 Kb	No	No	1h12	25.2	106	131.2

Security

User and access: Users are created with no access to Nectari by default. You need to create a specific Nectari user in the datasource configuration to avoid tablespace limitations.

To create a user and attach the created tablespace, run this command:

CREATE USER YOUR_USER IDENTIFIED BY YOUR_PASSWORD DEFAULT TABLESPACE YOUR_TABLESPACE

TEMPORARY TABLESPACE YOUR_TEMP_TABLESPACE_TMP QUOTA UNLIMITED ON YOUR_TABLESPACE;

After creating the Nectari user, you need to grant the following minimum access to that user:

GRANT create procedure, create session, create table, connect,

create type, create view, create synonym, create trigger, resource TO YOUR_USER;

Note
For any Oracle datasource the Nectari user must be put as custom schema in the data source definition. This will allow the tracking table or custom objects to be created in this specific user instead of the source. Triggers are the only exception to this rule.

You also need to grant the Nectari user access to all Oracle users (databases) that you will want to read as a source. The level of access will depend on what you want to do (read only, create OLAP Cube, write back, etc.).

To give the Nectari user access to a source, run this command (change SOURCE_USER and YOUR_USER):

begin

for x in ( SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = SOURCE_USER' )

loop

BEGIN

execute immediate 'GRANT SELECT ON SOURCE_USER.”' || x.TABLE_NAME || '” TO YOUR_USER';

END;

end loop;

end;

begin

for x in ( select VIEW_NAME from DBA_VIEWS WHERE OWNER = ' SOURCE_USER ' )

loop

BEGIN

execute immediate 'grant select on SOURCE_USER. ”' || x.VIEW_NAME || '” to YOUR_USER ';

END;

end loop;

end;
Triggers: Triggers must be created directly in the configuration of Nectari users in OLAP Manager OLAP Manager with the proper security level. This is required to avoid the case where the ERP will insert rows in a table with a trigger that will not have the proper access to the tracking tables.

To add the proper authorization level to the user:

grant create any trigger to YOUR_USER;

The data source is configured with the Nectari schema and the Nectari user. You will put SEED in the source schema and NectariCube in the Nectari schema.

When you build the OLAP Cube, the tracking table and the trigger will be created in the Nectari user, and no table will be created in the source user. The trigger will thus have automatically access to the tracking table.

When a record is inserted in the source user (ex : SEED.GACCENTRYD) from a source user (ex: SEED), the data will be inserted and the trigger will be executed with the authorization of Nectari user since it resides in the source user.

Creating Oracle Users and Tablespaces

Step 1: Revoke an existing user's permissions

Step 2: Create the Nectari user and schema

Step 3: Create the BILicense user and schema

Step 4: Create the NectariCube user

Step 5: Install the Nectari packages

Additional Scenarios

Adding space to the Oracle database

Removing the Oracle users and tablespaces

Additional Oracle Information