Security Module – Main concepts and use

Hi Friends,
We wrote this article to try to send some light on Security Module (10g for now) once several emails asked us for do it.
I hope that it can be useful!
The creation of users is relatively simple. Just open the Security module, go to the tab “Users” (Users) clicking the right mouse button and choose “Add User”

After opening the window only enough to fill the required information.
IMPORTANT: IF YOU CHECKED THE CHECK BOX OF “SUPERVISOR” CREATED YOU WILL HAVE NO RESTRICTION ON USE OF ENVIRONMENTAL ODI. This option enables the user CALLED “superuser”
At the end of user creation, you must add this to the desired profiles. I will use as an example, a developer can access any object in the repository development.
To do so, simply drag (drag and drop) the desired profiles to the newly created user:

IMPORTANT: THE PROFILE “CONNECT” IS MANDATORY FOR ALL USERS
Completed these steps the user is able to connect the repository with rights that were signed.

Granting privileges on projects and their components.

Granting privileges to a specific project can only be assigned to users have “Supervisor” and also with the profile of the “NG” (not generic).
In our example we will use a user named “Developer” which has only the profiles of “Connect” and “Designer NG

To determine if access should open side by side on screen, both modules, and Security Developer and then drag the desired design for the user who is entitled to access it as:

It will be shown a confirmation window
After confirming opens a window where rights of association have the following concepts:
a. The mark “Checked” means that the user can perform that action in any repository.

1. The mark of “Denied” means that the user can not use that method:

1. It is the mark of “Interrogation” means that the user can only use that method in the specified repository:

Established these conditions must then use them in the security window to set the user access to each object of that project:

Note that the methods “Edit Project” and “Duplicate Project” will only be available in the Repository “REPWORK.”
Only the method “Edit in new Window” will be allowed to all repositories
All other methods are denied (this is the default, should be configured as needed.)
We can see that after connecting the User “Developer” he has no rights other than those set:

It is interesting to inform you that the “rights” controlled by the security window can be understood as the options that appear when you right click on an object.
Other relevant information and that in this case example, there were signed on Direct Folders , Packages, Interfaces, etc.. And with that, the project does not bring any of your folders and if there is an attempt to create, an error such as:

The configuration of users without the standards should be made at the level of each method on that object.
These are the main tasks and concepts on Security Module.
Thanks for visit us!

OBIEE 10G/11G - Event Pooling table (Event Table) to purge the cache after ETL process

About

The use of an Oracle BI Server event polling table (event table) is a way to notify the Oracle BI Server that one or more physical tables have been updated and then that the query cache entries are stale.

Each row that is added to an event table describes a single update event, such as an update occurring to a Product table.

The Oracle BI Server cache system reads rows from, or polls, the event table, extracts the physical table information from the rows, and purges stale cache entries that reference those physical tables.

The event table is a physical table that resides on a database accessible to the Oracle BI Server. Regardless of where it resides—in its own database, or in a database with other tables—it requires a fixed schema.

It is normally exposed only in the Physical layer of the Administration Tool, where it is identified in the Physical Table dialog box as being an Oracle BI Server event table.

It does require the event table to be populated each time a database table is updated. Also, because there is a polling interval in which the cache is not completely up to date, there is always the potential for stale data in the cache.

A typical method of updating the event table is to include SQL INSERT statements in the extraction and load scripts or programs that populate the databases. The INSERT statements add one row to the event table each time a physical table is modified.

After this process is in place and the event table is configured in the Oracle BI repository, cache invalidation occurs automatically. As long as the scripts that update the event table are accurately recording changes to the tables, stale cache entries are purged automatically at the specified polling intervals.

Articles Related

• OBIEE 10G/11G - Setting a Query Logging Level
• OBIEE - Purging / Disabling the Query Cache

Polling Table Structure

You can set up a physical event polling table on each physical database to monitor changes in the database. The event table should be updated every time a table in the database changes.

The event table needs to have the structure shown below. The column names for the event table are suggested; you can use any names you want. However, the order of the columns has to be the same in the physical layer of the repository (then by alphabetic ascendant order)

Column Name by alphabetic ascendant order	Data Type	Null	Description	Advise
CatalogName	CHAR or VARCHAR	Yes	The name of the catalog where the physical table that was updated resides.	Populate the CatalogName column only if the event table does not reside in the same database as the physical tables that were updated. Otherwise, set it to the null value.
DatabaseName	CHAR or VARCHAR	Yes	The name of the database where the physical table that was updated resides.	Populate the DatabaseName column only if the event table does not reside in the same database as the physical tables that were updated. Otherwise, set it to the null value.
Other	CHAR or VARCHAR	Yes	Reserved for future enhancements.	This column must be set to a null value.
SchemaName	CHAR or VARCHAR	Yes	The name of the schema where the physical table that was updated resides.	Populate the SchemaName column only if the event table does not reside in the same database as the physical tables being updated. Otherwise, set it to the null value.
TableName	CHAR or VARCHAR	No	The name of the physical table that was updated.	The name has to match the name defined for the table in the Physical layer of the Administration Tool.
UpdateTime	DATETIME	No	The time when the update to the event table occurs. This needs to be a key (unique) value that increases for each row added to the event table.	To make sure a unique and increasing value, specify the current timestamp as a default value for the column. For example, specify DEFAULT CURRENT_TIMESTAMP for Oracle 8i.
UpdateType	INTEGER	No	Specify a value of 1 in the update script to indicate a standard update.	Other values are reserved for future use.

Step by Step for the Oracle database

Create the user

CREATE user OBIEE_REPO IDENTIFIED BY OBIEE_REPO;
GRANT connect,resource TO OBIEE_REPO;

Create the table

In 10g: Obi_Home\bi\server\Schema\SAEPT.Oracle.sql

--------------------------------------
--  Create the Event Polling Table. --
--------------------------------------
CREATE TABLE S_NQ_EPT (
  UPDATE_TYPE    DECIMAL(10,0)  DEFAULT 1       NOT NULL,
  UPDATE_TS      DATE           DEFAULT SYSDATE NOT NULL,
  DATABASE_NAME  VARCHAR2(120)                      NULL,
  CATALOG_NAME   VARCHAR2(120)                      NULL,
  SCHEMA_NAME    VARCHAR2(120)                      NULL,
  TABLE_NAME     VARCHAR2(120)                  NOT NULL,
  OTHER_RESERVED VARCHAR2(120)  DEFAULT NULL        NULL 
) ;

In 11g, the table is present in the BIPLATFORM metadata repository.

Import the table with ODBC (not with OCI)

Import the table with ODBC and change the call interface connection pool to OCI.

If you don't have an Oracle ODBC connection, you can simply suppress the table after importation with OCI and copy paste on the schema OBIEE_REPO the following UDML statmement:

DECLARE TABLE "ORCL".."OBIEE_REPO"."S_NQ_EPT" AS "S_NQ_EPT" NO INTERSECTION PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."UPDATE_TYPE" AS "UPDATE_TYPE" TYPE "DOUBLE" 
        PRECISION 10 SCALE 0  NOT NULLABLE PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."UPDATE_TS" AS "UPDATE_TS" TYPE "TIMESTAMP" 
        PRECISION 19 SCALE 0  NOT NULLABLE PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."DATABASE_NAME" AS "DATABASE_NAME" TYPE "VARCHAR" 
        PRECISION 120 SCALE 0  NULLABLE PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."CATALOG_NAME" AS "CATALOG_NAME" TYPE "VARCHAR" 
        PRECISION 120 SCALE 0  NULLABLE PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."SCHEMA_NAME" AS "SCHEMA_NAME" TYPE "VARCHAR" 
        PRECISION 120 SCALE 0  NULLABLE PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."TABLE_NAME" AS "TABLE_NAME" TYPE "VARCHAR" 
        PRECISION 120 SCALE 0  NOT NULLABLE PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."OTHER_RESERVED" AS "OTHER_RESERVED" TYPE "VARCHAR" 
        PRECISION 120 SCALE 0  NULLABLE PRIVILEGES ( READ);

When you import the table with OCI, the UDML metadata are not the same. They have different syntax for the definition of a column. You can remark in the two below statement that the OCI metadata has an extra EXTERNAL clause that you don't find in the ODBC metadata.

Declaration of the column CATALOGNAME after import with OCI:

DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."CATALOGNAME" AS "CATALOGNAME" EXTERNAL "obiee_repo" 
TYPE "VARCHAR" PRECISION 40 SCALE 0  NULLABLE  PRIVILEGES ( READ);

Declaration of the column CATALOGNAME after import with ODBC:

DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."CATALOGNAME" AS "CATALOGNAME" 
TYPE "VARCHAR" PRECISION 40 SCALE 0  NULLABLE PRIVILEGES ( READ);

Define the table as an event table

You can restart the Oracle BI Server service to be sure that the table is now seen as a good event table. If you have any error in the NQServer.log during the start, correct them.

Test

• Create a report with a product attribute to seed the cache.
• Insert a row to give to OBIEE the instruction to delete the cache from all SQL query cache which contain the product table.

INSERT
INTO
  S_NQ_EPT
  (
    update_type,
    update_ts,
    database_name,
    catalog_name,
    schema_name,
    table_name,
    other_reserved
  )
  VALUES
  (
    1,
    sysdate,
    'orcl SH',
    NULL,
    'SH',
    'PRODUCTS',
    NULL
  )

• Wait the polling interval frequency and verify that the cache entry is deleted and that you can find the below trace in the NQQuery.log file.

+++Administrator:fffe0000:fffe001c:----2010/06/16 00:45:33

-------------------- Sending query to database named ORCL (id: <<13628>>):

select T4660.UPDATE_TYPE as c1,
     T4660.UPDATE_TS as c2,
     T4660.DATABASE_NAME as c3,
     T4660.CATALOG_NAME as c4,
     T4660.SCHEMA_NAME as c5,
     T4660.TABLE_NAME as c6
from 
     S_NQ_EPT T4660
where  ( T4660.OTHER_RESERVED in ('') or T4660.OTHER_RESERVED is null ) 
minus
select T4660.UPDATE_TYPE as c1,
     T4660.UPDATE_TS as c2,
     T4660.DATABASE_NAME as c3,
     T4660.CATALOG_NAME as c4,
     T4660.SCHEMA_NAME as c5,
     T4660.TABLE_NAME as c6
from 
     S_NQ_EPT T4660
where  ( T4660.OTHER_RESERVED = 'oracle10g' ) 


+++Administrator:fffe0000:fffe001c:----2010/06/16 00:45:33

-------------------- Sending query to database named ORCL (id: <<13671>>):

insert into 
     S_NQ_EPT("UPDATE_TYPE", "UPDATE_TS", "DATABASE_NAME", "CATALOG_NAME", "SCHEMA_NAME", "TABLE_NAME", "OTHER_RESERVED") 
values (1, TIMESTAMP '2010-06-16 00:44:53', 'orcl SH', '', 'SH', 'PRODUCTS', 'oracle10g')


+++Administrator:fffe0000:fffe0003:----2010/06/16 00:45:33

-------------------- Cache Purge of query:
SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/users/administrator/cache/answers_with_product';
SELECT Calendar."Calendar Year" saw_0, Products."Prod Category" saw_1, "Sales Facts"."Amount Sold" 
saw_2 FROM SH ORDER BY saw_0, saw_1


+++Administrator:fffe0000:fffe001c:----2010/06/16 00:45:33

-------------------- Sending query to database named ORCL (id: <<13672>>):

select T4660.UPDATE_TIME as c1
from 
      S_NQ_EPT T4660
where  ( T4660.OTHER_RESERVED = 'oracle10g' ) 
group by T4660.UPDATE_TS
having count(T4660.UPDATE_TS) = 1


+++Administrator:fffe0000:fffe001c:----2010/06/16 00:45:33

-------------------- Sending query to database named ORCL (id: <<13716>>):

delete from 
      S_NQ_EPT where  S_NQ_EPT.UPDATE_TS = TIMESTAMP '2010-06-16 00:44:53'

Support

The cache polling event table has an incorrect schema

In the NQServer.log file

[56001] The cache polling event table S_NQ_EPT has an incorrect schema.

You must import the event table via ODBC 3.5. When you use OCI, the UDML of the table has a difference. You can then change the call interface to OCI.

The physical table in a cache polled row does not exist

In the NQServer.log file

[55001] The physical table ORCL::SH:PRODUCTS in a cache polled row does not exist.

Verify the location of your table.

For instance, in my case, the good location was “orcl SH::SH:PRODUCTS” as you can see in the picture below.

The cache polling SELECT query failed for table

When you get the following trace in NQQuery.log, it's because:

• your event table was deleted
• or that the name in the physical layer doesn't match any more the table in the database.

2010-06-15 07:52:28  [55003] The cache polling SELECT query failed for table S_NQ_EPT.
2010-06-15 07:52:28  [nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist
at OCI call OCIStmtExecute. 
                     [nQSError: 17010] SQL statement preparation failed.
2010-06-15 07:52:28  [55005] The cache polling delete statement failed for table S_NQ_EPT.

faulting module NQSCache.dll

Oracle BI Server can literally crash when you use the example script that is given in the documentation (with an OTHER column). You can find this information in the even viewer of Windows.

Description: Faulting application NQSServer.exe, version 10.1.3.4, faulting module NQSCache.dll, 
version 10.1.3.4, fault address 0x00016b43.  

Solution: change the structure of the table by using the script in the schema directory (such as above in the document)