The BRIOSECR Table

The BRIOSECR table is the heart of the row‑level security feature. It defines the specific restrictions to be applied to users and the groups (including PUBLIC) to which they belong. These restrictions take the form of join operations (a user cannot access a column in the employee salary table unless it is joined to the employee table), and limits (WHERE clause expressions) to be applied to either the source table (SALARY) or table(s) (EMPLOYEE) to which it is joined. Table 248 lists the columns in the BRIOSECR table.

As suggested earlier, existing security definitions can sometimes be translated into the format described here. A view, stored procedure, or programmatic mechanism can be used to translate and/or populate the information needed in BRIOSECR by the Reporting and Analysis servers. When these methods are used, the servers require the column names and data types defined above. And again, do not attempt to use the sample Interactive Reporting document, row_level_security.bqy, to manage this information.

If a join table is specified and it does not already exist in the data model the user accesses, it will still be added to the final SQL generated to ensure the security restrictions are enforced. This process is iterative. When a table is added and the present user, either directly or by group membership, has restricted access to that added table, those restrictions will also be applied, which may mean additional tables will be added, and those restrictions will also be checked, and so on. Circular references will result in an error if they are defined.

Table 248. Columns in the BRIOSECR Table

Column Name	Column Type	Functional Use
UNIQUE_ID	INT	This column contains an arbitrary numeric value. It should be unique, and it is useful for maintaining the table by whatever means the customer chooses. The servers do not rely upon this column, and the servers never access this column. To that extent, it is an optional column but recommended. (It is required when using the sample Interactive Reporting document, row_level_security.bqy.) When the RDBMS supports it, a unique constraint or unique index should be applied to the table on this column.
USER_GRP	VARCHAR	The name of the user or the name of a group to which a user belongs. If PUBLIC, the restrictions are applied to all users.
SRCDB	VARCHAR, can be null	Used to identify a topic in the Data Model. (In Interactive Reporting, a topic typically corresponds to a table in the database, but it could be a view in the database.) If the physical name property of the topic is of the form name1.name2.name3, this represents name1. Most often, this represents the database in which the topic exists. This field is optional unless required by the connection in use. The most likely circumstance in which to encounter this requirement will be with Sybase or Microsoft SQL Servers where theInteractive Reporting database connection file (the connection definition file) is set for access to multiple databases.
SRCOWNER	VARCHAR, can be null	Used to identify the owner/schema of the topic in the Data Model. This would be name2 in the three-part naming scheme shown above. If the topic property, physical name contains an owner, then it must be used here as well.
SRCTBL	VARCHAR	Used to identify the table/relation identified by the topic in the Data Model. This is name3 in the three-part naming scheme.
SRCCOL	VARCHAR	Used to identify a column in SRCTBL. This is a topic item in Data Model terminology, and is an item that might appear on the Request line in a query built from the Data Model. In the context of the security implementation, the item named here is the object of the restrictions being defined by this row of the security table BRIOSECR. If this column contains an *, all columns in SRCTBL are restricted.
JOINDB	VARCHAR, can be null	If present, defines the database name qualifier of a table/relation that must be joined to SRCTBL.
JOINOWNR	VARCHAR, can be null	If present, defines the schema/owner name qualifier of a table/relation that must be joined to SRCTBL.
JOINTBL	VARCHAR, can be null	If present, names the table/relation that must be joined to SRCTBL.
JOINCOLS	VARCHAR, can be null	If present, names the column name from SRCTBL to be joined to a column from JOINTBL.
JOINCOLJ	VARCHAR, can be null	If present, names the column name in JOINTBL that will be joined (always an equal join) to the column named in JOINCOLS.
CONSTRTT	CHAR(1), can be null	If present, identifies a table/relation to be used for applying a constraint (limit). This is a coded value. If the value in this column is S, the column to be limited is in SRCTBL. If J, a column in JOINTBL is to be limited. If the value in this column is O, it indicates that for the current user/group, the restriction on the source column for the group/user named in column OVRRIDEG is lifted, rendering it ineffective. If this value is NULL, then no additional restriction is defined. If the JOIN* columns are also all NULL, the column is not accessible at all to the user/group. This implements column level security. See the functional use description of CONSTRTV for more information on column level security.
CONSTRTC	VARCHAR, can be null	The column in the table/relation identified by CONSTRTT to which a limit is applied.
CONSTRTO	VARCHAR, can be null	The constraint operator, such as =, !=(not equal), etc. BETWEEN and IN are valid operators. Basically, any valid operator for the database can be supplied.
CONSTRTV	VARCHAR, can be null	The value(s) to be used as a limit. The value(s) properly form a condition that together with the content of CONSTRTC and CONSTRTO columns create valid SQL syntax for a “condition” in a WHERE clause. Subquery expressions, therefore, are allowed. Literal values should be enclosed in single quotes or whatever delimiter is needed by the database for the type of literal being defined. If the operator is BETWEEN, the AND keyword would separate values. If :USER is used in the value, then the user name is the limit value. If :GROUP is used, all groups of which the user is a member are used as the limiting values. Both :USER and :GROUP can be specified, separated by commas. The public group must be named explicitly; it is not supplied by reference to :GROUP. When applying column level security, CONSTRTV provides the SQL expression that will effectively replace the column on the Request line. For example, the value zero (0) might appear to replace a numeric value that is used in theInteractive Reporting documents but should not be accessible by the specified user/group. While any valid SQL expression that can be used in a SELECT list is permitted, pick a value that is acceptable for the likely use. For example, the word NULL is permitted, but note that in some cases, it might not be the appropriate choice, as it could also end up in a GROUP BY clause.
OVRRIDEG	VARCHAR, can be null	The name of a group or user. Used when CONSTRTT is set to O. If the group named in OVRRIDEG has a restriction on the source element, then this restriction is effectively ignored for the user/group named in USER_GRP. SRCDB, SRCOWNER, SRCTBL, and SRCCOL as a collection must be equal between the row specifying the override and the row specifying the conditions to be overridden. (See examples.)
CONSTRNL	CHAR(1)	The value in this column determines whether null values are included in the restriction. When this column contains a ‘Y,’ SQL includes null values. If this column is blank, null values are excluded. The following example shows the SQL generated when the CONSTRNL field is “Y”: The following syntax in SQL is shown as: OR TABLE.COLUMN IS NULL SELECT AL1.ROW_NUM, AL1.INT_COL, AL1.FLOAT_COL, AL1.NUMBER_COL, AL2.CHAR_COL, AL1.VARCHAR_COL, AL1.DATE_COL, AL1.TS_COL, AL2.TIME_COLFROM QS.QS_BRIO AL2 WHERE ((AL1.VARCHAR_COL = 'Quality Assurance' OR AL2.VARCHAR_COL IS NULL) For example this sample shows how to include nulls on the “Quality Assurance” column:Note in the above the instruction: AL2 WHERE ((AL1.VARCHAR_COL = 'Quality Assurance' OR AL2.VARCHAR_COL IS NULL).