“Micro Focus Vibe (formerly Novell® Vibe) brings people, projects, and processes together in one secure place to enhance team productivity - no matter where the team is or what devices they use” is how the Micro Focus website introduces Vibe (www.microfocus.com). So the main purpose of Vibe is team collaboration – loose or tight.
To handle all this information and these requests a SQL database is used in the background to store values, states and much more:
- Structural information about workspaces, folders, and entries (for example, their location in the workspace tree)
- Identification information about workspaces, folders, and entries (for example, titles, descriptions, dates of creation/modification, and users associated with creation/modification)
- User profile information (for example, full name, phone number, and e-mail address)
The Vibe database disk space requirements are relatively modest, because the database is not used for storing files. This article will describe how I access the SQL database directly.
Why access this database directly? I see two main reasons: customers have asked me to present values, information or properties of their Vibe environment which Vibe does not deliver by itself, and the second reason is a little bit more selfish – it’s my academic approach. I want to know more about what’s happening in the background and how the pieces fit and work together.
The next paragraphs will illustrate how I tried to solve a puzzle of values, records and tables within SQL. My customer’s request was to deliver a list or an overview of assigned rights - a spreadsheet to show which individual rights are set for users or groups within a Vibe environment; especially which access rights are active for group allusers. There is no report within Vibe to get this – especially for groups.
As mentioned above Vibe is about team collaboration – loose or tight. Some companies use Vibe for intranet purposes, some companies use it for document management including versioning, workflows and even more, because there are no boundaries to customisation.
Independent of its purpose, access rights will arrange and organise who and sometimes when someone - if a workflow is in progress – is allowed to see, change or even delete contents.
Vibe offers features like What’s new, What’s unread, Follow places or people and full text searches. Therefore access rights are essential in a collaboration environment! Everyone should see only those pieces of information which they are intended to.
For example some default rights are set during installation and maintenance – particularly for the group allusers which is a system group that is allowed to read in global workspaces, but also in personal workspaces too, if the administrator does not change the default settings.
That is the reason my customer asked me for a list of rights for allusers. He wants to minimise administrators‘ headaches which already caused this request.
Environment & Tools
My description and experience is based on Vibe 4.0.x and MySQL 5.5. I know Vibe well but I am not an experienced SQL programmer or specialist. To build my statements and scripts I used the internet to get hints and solutions. So please do not argue with me that my SQL statements are not efficient enough or there is a better way to do it. The main purpose for this article is to show how easy it is to get to and read additional information out of Vibe.
I used MySQL Workbench 6.0 on Microsoft Windows to find the right relationships, values and entries. But there are other tools too with a similar purpose (e.g. HeidiSQL - which is not an Austrian product!).
My workbench connects to MySQL via port 3306 . Maybe your MySQL database allows only local connections with remote connections refused. So you have to solve this issue first – the internet will help you. (See Figure 1)
After connecting successfully you will see at least two databases. sitescape is the database we need. A bundle of tables will be listed on the left side after opening Sitescape. It seems that only tables starting with “SS_” are relevant for our task.
Enhancing a table will show which columns are in use for this table. In my example I have selected SS_WorkAreaFunctionMemberships, which contains columns:
- id
- lockVersion
- workAreaId
- WorkAreaType
- zoneId
- functionId
Digging through Database Tables
There are so many tables, so many fields and different id fields. I need knowledge of how these tables are related behind the scene. What is their relationship? I just see numbers (=ids), which do not really help because there are no numbers or ids displayed in my Vibe user interface.
To make investigations easier I created a folder within my personal environment specifying a unique folder name. An unique title makes searches easier! For my final purpose I set access rights for group allusers because I want to get a list of folders and workspaces where allusers has individual access rights.
Now my analysis starts – I dig through the database tables to find out where my unique name appears again. A simple view of table contents can be done with “select * from table-name”. An additional filter allows me to focus on the right record. A table named SS_Forums delivers success – one record shows my unique folder name which is fortunately really unique. My folder’s id is “1006”.
By touching many of these tables and checking out their contents I have found out that SS_WorkAreaFunctionsMemberships is probably my candidate for a central point of information.
Now I change my method of investigation – and start with paperwork. I like drawing – when troubleshooting or for a clearer understanding of processes I use paper. I draw how my brain sees links, connections and relationships. So let’s take out a piece of paper and start drawing!
My assumed candidate will be placed in the middle of the paper. Field workAreaId – value 1006 – points to SS-Forums, where I find my unique name. Another field called functionId leads to table SS-Functions and further on to SS_FunctionOperations; where functionId’s value is “37”.
Next I try to find out who is allowed to execute these functions. SS_ WorkAreaFunctionsMemberships.id is linked to SS_WorkAreaFunctionsMembers.WorkAreaFunctionsMembershipId – both have the value “357”. From SS_WorkAreaFunctionsMembers and its field member-id (=17) I get a connection to my final table SS_principals. SS_principals is the table which contains all users and groups; their names; their ids and a little bit more; i.e. a field “type” tells me if this record describes a group, a user or something else.
Now I have an initial picture:
- SS_WorkAreaFunctionMemberships presents via SS_WorkAreaFunctionMembers and further on via SS_Principals which has assigned rights for this folder or workspace.
- A branch via SS_Functions and SS_FunctionOperations shows which access profile has been activated. If I need more granularity then SS_FunctionsOperations describes for which single rights a profile consists.
- Finally a relation link to SS_Forums delivers the name of a folder or workspace.
Form a SQL Statement
My next and final task is to create a SQL statement. It seems that I will use 5 tables to get my requested results and different id fields link these tables. JOIN … ON id1 = id2.
As I want to get a list of users or groups and their access rights I start with table SS_Principals and ask for entries that are members of (and so on).
After keyword SELECT I place the fields which will be part of my spreadsheet. Here are my selected fields (These capital letters will be explained below):
- P.id – Although I do not need the id it will be part of my spreadsheet.
- P.name – name of user or group
- P.type – so I know if this record describes an individual user or group
- R.name – which access profile (=role) is active
- F.title – name of the folder or workspace
- F.pathname – a path description for this folder or workspace. This is really helpful if there exists more than one entry with the same name, i.e. a Vibe server can contain more than one folder named “2017” because every user can create one in his environment.
To shorten my typing I use a letter as a placeholder for my tables:
- P - SS_Principals
- R - SS_Functions
- F - SS_Forums
The WHERE verb will narrow a search.
- Please show all entries WHERE P.name = “Rimser” (exact value)
- List all entries WHERE P.name like “allu%” (starts with “allu”).
After some trial and error I found my statement. It’s shown in figure 6 below
So what about the output? Well that’s shown in figure 7 after import to a spreadsheet.
What a relief - that’s it. I get what I want and it’s so easy to adapt this for similar purposes!
This spreadsheet shows some of the records after running my script. allusers is of type “group” and has some rights for folder IT ToDo. IT ToDo is located somewhere in my personal workspace. Two access profiles have been set for this folder or workspace. “Visitor” (=__role.visitor) and “FNS1”. __role.visitor is a system based role, but “FNS1” was created by myself.
This output is clear and short. It’s easy to enhance this report and add additional information. e.g. if I want to know of which rights my role “FNS1” consists then I will retrieve requested information out of SS_FunctionOperations. My example above does not explain if IT ToDo is a folder or a workspace. SS_Forums will deliver that type of information.
Nevertheless my SQL statement fulfils my request – it is easy to run a report to see individual rights. In this article I used MySQL Workbench but there are several other ways to run reports too. You can use this command line statement or you can do some customising within Vibe to get reports via JSP.
Conclusion
My customer’s request was to get an overview of access rights for individuals. The statement above delivers what he wants.
My personal academic approach has been satisfied too. My paperwork paved my path and made it possible to see what’s happening behind the scenes in Vibe. I see tables, fields and some of their relationships.
Nevertheless I feel and fear that I have seen only some rooms of a much bigger building. There is a much bigger construction to store Vibe’s data.
I have investigated only five tables but there are many more. I have used only tables starting with “SS_” but there are some other types too. It would be a lot more work to understand the whole data puzzle.
However if you want to customise Vibe then you want to change presentation and behaviour. This can be done with forms, HTML and JSP, but you must not change the sitescape database. Never! So do not change any columns, fields or tables!
This article was first published in OHM Issue 38 (2017/3), p15-18.