Avaya CMS - Deletion of a Designer Report Owner14 Jul 2019 · Comments: · Tags: Avaya, Informix, SQL, Solaris
- List the Designer Reports Owned by the User to be Deleted
- Deleting the User
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
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
- TIP: If you’re using PuTTY to SSH, change the following setting so that the backspace key works as expected:
- Switch user to a root account:
- Set all the relevant environment variables required by DB-Access, this is
achieved by sourcing the following file:
- Use the following syntax to execute a query with DB-Access in command line mode:
Database - Privileges
I’ll attempt to explain how privileges have been assigned in the
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
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
systabauth tables directly. Please consult the Informix
documentation for details.
Let’s start by ascertaining the database-level privileges with the following query:
The result shows that
root has a usertype of
D which denotes
public (which means any user) has usertype of
R which denotes
This means that
root effectively has free-reign of the database. All other
public) have the second most powerful privilege assigned (
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
custobjects tables of the
cms database. Let’s check the
table-level privileges on each table using the following queries:
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
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,
- Create a separate OS account for each administrator and grant it suitable
privileges within Informix. This negates the need to grant
rootaccess 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:
Database - Query
This query will list all reports owned by
I think that all the field and alias names are pretty self-explanatory except
report_path. Based on my own observations and from reading various Avaya
related forum posts, CMS stores designer reports as files in
Files are named with a number, this is what the value of the
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
Overwrite the existing report:
This was resulting in report names being prefixed with three tildes (
the owner failing to be updated. To resolve this, I just had to close and reopen
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.
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
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.
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.