Avaya CMS - Deletion of a Designer Report Owner

Summary

As a sysadmin one of my responsibilities is the management of an Avaya telephone system, a component of which is Avaya Call Management System (CMS). Avaya CMS collects call traffic data and provides a means of reporting against it. In addition to the reports included as standard, CMS also provides the ability to write your own, which in CMS terminology are referred to as Designer Reports.

I recently needed to delete the account of an ex-employee who had authored numerous designer reports. Before doing so, I wanted to understand the impact this would have on all the reports they’d authored, this post details my findings.

I was working with the following CMS product versions:

Frontend: Avaya CMS Supervisor 18.0 on Windows 10 (1809)

Backend: CMS Server R16 on Solaris 10.

List the Designer Reports Owned by the User to be Deleted

Objective

I wanted to produce a list of designer reports where the user I was planning to delete was the owner. I couldn’t find a means of achieving this with Avaya CMS Supervisor, furthermore I failed to find any guidance in the CMS documentation or by searching online, so I decided to examine the database. You may wonder how I knew my way around the database, I didn’t. I just pieced things together by performing tests and analysing the results.

Database - Access

CMS R16 uses an Informix Dynamic Server (IDS) database. I didn’t want to install any additional software on the CMS server or have to open any firewall ports between my workstation and the server in order to access the database. Informix ships with a utility called DB-Access, the purpose of which is to access and manipulate Informix databases. It has a menu-driven text user interface and a command line interface, all you need to use it is an SSH session to the server.

DB-Access itself doesn’t require a root account in order to use it, however it is required in order to query the CMS DB for a list of designer reports owned by a specific user due to the way database-level privileges have been assigned. I’ll return to the subject of privileges later.

So with that said, to use DB-Access:

SSH to the CMS server with a non-root account. TIP: If you’re using PuTTY to SSH, change the following setting so that the backspace key works as expected: Change Settings > Terminal > Keyboard > The Backspace key: Control-H

Switch user to a root account: su root

Set all the relevant environment variables required by DB-Access, this is achieved by sourcing the following file: . /opt/informix/bin/setenv

Use the following syntax to execute a query with DB-Access in command line mode:

dbaccess cms @ cms_ol - <<! SELECT * FROM users ; !

Database - Privileges

I’ll attempt to explain how privileges have been assigned in the cms database and why it’s necessary to use a root account. This is a bit of a tangent from the subject of this post but it’s worth mentioning.

In Informix, you don’t create user accounts, instead you grant privileges to operating system accounts. Each database has a sysusers table in which database-level privileges are recorded and a systabauth table in which table-level privileges are recorded. The username field of the sysusers table and the grantee field of the systabauth table hold the OS usernames.

NB: I won’t be covering assigning/modifying Informix privileges in this post, however I will say that you should do so using GRANT , NOT by modifying the sysusers and systabauth tables directly. Please consult the Informix documentation for details.

Let’s start by ascertaining the database-level privileges with the following query:

dbaccess cms @ cms_ol - <<! SELECT username , usertype FROM cms : sysusers ; !

The result shows that root has a usertype of D which denotes DBA privilege and public (which means any user) has usertype of R which denotes Resource privilege :

username usertype root D public R 2 row(s) retrieved.

This means that root effectively has free-reign of the database. All other users ( public ) have the second most powerful privilege assigned ( Resource ) enabling queries to be executed provided they have the necessary privileges on the target table(s).

For the purpose of the objective, we need to perform a SELECT query against the users and custobjects tables of the cms database. Let’s check the table-level privileges on each table using the following queries:

dbaccess cms @ cms_ol - <<! SELECT * FROM systabauth WHERE tabid = ( SELECT tabid FROM systables WHERE tabname = 'users' ); !

dbaccess cms @ cms_ol - <<! SELECT * FROM systabauth WHERE tabid = ( SELECT tabid FROM systables WHERE tabname = 'custobjects' ); !

Both queries return zero rows indicating that neither table has any table-level privileges assigned.

In conclusion, since there are no table-level privileges on users and custobjects , only a root account can be used to accomplish the objective.

If you work in an environment where multiple administrators work on CMS and they are not permitted to share a single root account then there are a few options, such as:

Create a separate OS account for each administrator and grant it suitable privileges within Informix. This negates the need to grant root access but I prefer to treat the CMS DB as a black box, by which I mean I don’t make modifications, I just consume it using the default configuration.

access but I prefer to treat the CMS DB as a black box, by which I mean I don’t make modifications, I just consume it using the default configuration. In Unix-like systems, users are identified by a user ID (UID), 0 is reserved for use by the root account. Multiple usernames can be associated with the same UID, thus it is possible to create an account for each administrator with a UID of 0, EG:

# Create a new user with a UID of 0: # NB: I'd normally make bash the default shell (useradd -s /bin/bash ...) but # it wasn't present on my Solaris 10 server. useradd -u 0 -o root2 passwd root2 # Set the UID of an existing user to 0: usermod -u 0 -o root2

Database - Query

This query will list all reports owned by thecliguy :

dbaccess cms @ cms_ol - <<! SELECT custobjects . name AS report_name , custobjects . subsystem , custobjects . owner AS owner_id , users . l_name AS owner_username , custobjects . path AS report_path FROM custobjects INNER JOIN users ON custobjects . owner = users . UID WHERE users . l_name = 'thecliguy' !

Example result:

report_name Agent Group Report (modified) subsystem Real-Time owner_id 15 owner_username thecliguy report_path 30 report_name Group Attendance Weekly subsystem Historical owner_id 15 owner_username thecliguy report_path 76 report_name VDN Performance Report Daily subsystem Historical owner_id 15 owner_username thecliguy report_path 77 3 row(s) retrieved.

I think that all the field and alias names are pretty self-explanatory except for report_path . Based on my own observations and from reading various Avaya related forum posts, CMS stores designer reports as files in /cms/cow/reports/designer . Files are named with a number, this is what the value of the report_path field refers to.

Deleting the User

Option 1: Change the Owner of Each Report Prior to Deleting the User

Now that we know which reports are owned by the user to be deleted, prior to deletion we can use Avaya CMS Supervisor to navigate to each report and change the value of the Owner field to the name of another user:

When prompted, select Overwrite the existing report :

NB: At one point I found that CMS Supervisor would produce the following error after selecting Overwrite the existing report :

--------------------------- Avaya CMS Supervisor --------------------------- The operation could not be completed. Error: 28031 - This is not a valid object Location: frmSelectRept:cmdProp_Click --------------------------- OK ---------------------------

This was resulting in report names being prefixed with three tildes ( ~~~ ) and the owner failing to be updated. To resolve this, I just had to close and reopen CMS Supervisor.

Option 2: Delete the User and Allow CMS Supervisor to Change the Owner of Each Report

An alternative to changing the owner of each report is to just delete the user with Avaya CMS Supervisor, it’ll take care of the change of ownership for you. However, there’s a quirk, read on for details…

To delete a user navigate to: Tools > User Permissions > User Data > Find the user you wish to delete and press the delete button.

On pressing delete, two Yes/No dialogue boxes are produced:

User may have files which will be removed, do you still want to delete this user? User has designer reports, do you want to move the reports to your id?

The first is presented when deleting any user. The second is presented only when deleting a user who is the owner of designer reports.

When selecting Yes to the second dialogue, ownership of designer reports is changed to the user performing the deletion. However, as I alluded to earlier there’s a quirk… After deletion, the owner field of each affected report contains the previous owner’s ID instead of the current owner’s name:

Querying the database’s custobjects table shows that ownership has been updated, so it occurred to me that CMS Supervisor was using some other data source. I found that closing CMS Supervisor, deleting its cache file ( %APPDATA%\Avaya\CMS Supervisor R18\Cache\CVS_Cache.tmp ) and then reopening caused the owner to display correctly.

It’s worth noting what happens if you click No to the second dialogue; the reports that were owned by the deleted user are removed from the custobjects table and the files removed from /cms/cow/reports/designer . However, the reports remain visible within CMS Supervisor. Attempting to run such a report results in the error Report does not exist on the server. followed by The operation could not be completed. Error: 28011 - Does not exist. Location: frmSelectRept:RSel_SelReport . To resolve this, close CMS Supervisor, delete the cache file ( %APPDATA%\Avaya\CMS Supervisor R18\Cache\CVS_Cache.tmp ) and reopen CMS Supervisor, after which the reports will no longer be visible.

Resources

To assist with the writing of this post I needed to be able to experiment with Solaris and Informix in a sandboxed environment away from my production CMS server.

Solaris 10 is available to download free of charge from Oracle for non-production use under the OTN licence. Downloads are available in the form of an ISO file and a VM template for Oracle VM VirtualBox.

A Developer Edition of Informix IDS is available to download free of charge from IBM.