How to check the privileges using ALL_PRIVILEGES table in TIBCO Data Virtualization by bit unmasking?

How to check the privileges using ALL_PRIVILEGES table in TIBCO Data Virtualization by bit unmasking?

book

Article ID: KB0070046

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization All Supported Versions

Description

TIBCO Data Virtualization stores all assigned privileges in the ALL_PRIVILEGES table. However, the privileges stored in this table are bitmasked and cannot be read easily. To extract the privileges of any resource from this table in a readable format, bit unmasking is required.

Below is an example of the resource named orders_pkgQuery, which has the privileges of READ, WRITE, and EXECUTE:
 
User-added image

If we check the ALL_PRIVILEGES table then we will see the PRIVILEGE value in bit masking. More details about this can be read from this article -> https://support.tibco.com/s/article/000053071

User-added image

Environment

All Supported Environments

Resolution

To unmask the value and retrieve the privileges, use the following query:
 SELECT      PRIVILEGE,     CASE WHEN PRIVILEGE & 128 > 0 THEN 'GRANT ' ELSE '' END ||     CASE WHEN PRIVILEGE & 64 > 0 THEN 'DELETE ' ELSE '' END ||     CASE WHEN PRIVILEGE & 32 > 0 THEN 'INSERT ' ELSE '' END ||     CASE WHEN PRIVILEGE & 16 > 0 THEN 'UPDATE ' ELSE '' END ||     CASE WHEN PRIVILEGE & 8 > 0 THEN 'SELECT ' ELSE '' END ||     CASE WHEN PRIVILEGE & 4 > 0 THEN 'EXECUTE ' ELSE '' END ||     CASE WHEN PRIVILEGE & 2 > 0 THEN 'WRITE ' ELSE '' END ||     CASE WHEN PRIVILEGE & 1 > 0 THEN 'READ ' ELSE '' END AS Privilege_List FROM /services/databases/system/ALL_PRIVILEGES WHERE resource_name = 'orders_pkgQuery'; --any filter can be used here
  User-added image

Issue/Introduction

This article will summarize how to unmask the privileges from the ALL_PRIVILEGES table in TIBCO Data Virtualization.