SonarC Administration

Creating Users for GUI and Tool Access

The administrator creates users using the Users / Roles Management Console or using the shell. The console provides a GUI interface and is the preferred method.

In addition to general access control, a special role called jsonStudioAccess provides control over who may edit and build queries using JSON Studio. To allow a user these abilities add the jsonStudioAccess role on the admin database to a user. This role is part of the default SonarGUser role, but not part of the default SonarGROUser role.

The shell can be used either from the SonarC host or from any host depending on how the SonarC was hardened. To create a user from the SonarC host ssh to the host and login using the admin credentials that were set during system install, e.g.:

ubuntu@ip-172-30-0-82:~$ mongo localhost:27117/admin -uadmin -p
MongoDB shell version: 2.6.1
Enter password:
connecting to: localhost:27117/admin

If you are allowed to connect from remote use:

ubuntu@ip-172-30-0-82:~$ mongo <SonarC hostname>:27117/admin -uadmin -p

MongoDB shell version: 2.6.1

Enter password:

connecting to: localhost:27117/admin

Then create the user:

> db.createUser({user: "<username>", pwd: "<password>", roles: ["sonarGUser"]})
Successfully added user: { "user" : "...", "roles" : [ "sonarGUser" ] }

Changing Passwords

A user may change their passwords from within JSON Studio or by using the shell/cli. Using the Studio is preferred. After logging in use the “Connected as” drop-down to select “Change Password”. The shell can be used either from the SonarC host or from any host depending on how the SonarC was hardened. To create a user from the SonarC host ssh to the host and login using admin credentials, e.g.:

ubuntu@ip-172-30-0-82:~$ mongo localhost:27117/admin -u<your username> -p
MongoDB shell version: 2.6.1
Enter password:
connecting to: localhost:27117/admin

Then run:

> db.changeUserPassword("<username>", "<new password>")

Creating Users for SQL Access

Use a mysql client to login to SonarSQL using the SonarSQL root account you created when installing the software. When logged in as root, you can create new users in your MySQL client using:

CREATE USER '<user>' IDENTIFIED BY PASSWORD '<password>'

Resetting admin password

If you forget the admin password for SonarW you will need to reset it:

  • Shut down SonarW (e.g. sudo service sonard stop)

  • Edit /etc/sonarw.conf and change to auth=false

  • Start up SonarW (e.g. sudo service sonard start)

  • Login to SonarW with no password:

    ubuntu@ip-172-30-0-82:~$ mongo localhost:27117/admin
    MongoDB shell version: 2.6.1
    connecting to: localhost:27117/admin
    
  • Change the password:

    > db.changeUserPassword("admin", "<new password>");
    
  • Shut down SonarW (e.g. sudo service sonard stop)

  • Edit /etc/sonarw.conf and change to auth=true

  • Start up SonarW (e.g. sudo service sonard start)

To reset/change the root password for SonarSQL you need to ssh to the SonarC host and run:

sudo sonarsql-setup setup

Restart the sonarsql service after the changes.

Unlocking secAdmin User

Any user, including secAdmin, will be locked upon entering an incorrect password at the SonarG login screen 5 times. While other users can be unlocked through the GUI, by a secAdmin user, in the ‘User Management” page, secAdmin itself must be unlocked through the Mongo Shell.

locked users are specified in the locked_users collection on the admin database:

> use admin
> db.locked_users.find()

Deleting the user’s document from this collection will unlock the user, hence in order to unlock secAdmin run the next Command:

> db.locked_users.remove({"username":"secAdmin"})

Data Level Security (DLS)

The default SonarC setup is based on users and roles for security. Users may have custom privileges on which data set they can use. More advanced setup is possible that controls what users can see using data level security rules. DLS applies filters so that even though two users may have the same roles and access the same data/report, they will get different data based on the filtering policy. This is transparent to the user and the user does now know that the filter is being applied.

There are three default DLS modes supported out-of-the box in SonarC, selected in the user/role editor: - None - means no DLS rules are applying. This is the default. - Based on Source - allows you to map which data a user sees based on which collector the data came from. - Based on Server IP - allows you to map which data a user sees based on the IP address the data relates to - for example, the IP address of the database server being monitored.

In SonarC explicit rules need to be built depending on which data is sourced.

When DLS is enabled you can apply DLS to all users or you can select a set of users for which DLS filtering occurs while all other users will not have DLS filters applied to their access.

Additionally, you can specify any DLS rules and create more refined implementations using the DLS capabilities of SonarW. Consult your SonarC account manager for how to build a DLS-based implementation.

When deploying SonarC using each of the two built-in DLS modes, you need to supply definitions for which user can see what using collections that need to be populated in SonarC. These collections can be populated through incoming CSV file, through dispatcher pulls from LDAP, through dispatcher pulls from an RDBMS, or through a push into SonarC using your own interface.

When you use DLS based on source you need to populate a collection named dls_source in sonargd. Each document in this collection looks like:

{"u" : "jane", "SonarG Source" : "gibm32" }

When you use DLS based on IP you need to populate a collection named dls_ip in sonargd. Each document in this collection looks like:

{"u" : "jane", "Server_IP" : "10.10.1.2" }

When you use source-based DLS the following data collections in sonargd are filtered:

"buff_usage",
"classifier",
"databases_discovered",
"datasources",
"discovered_instances",
"dm_extraction_log",
"exception",
"full_sql",
"grdm",
"grdmcomplete",
"grdmrec",
"installed_patches",
"instance" ,
"outliers_list",
"outliers_summary",
"policy_violations",
"session",
"stap_status",
"va"

SAGE data is not filtered in this case.

When you use IP-based DLS the following data collections in sonargd are filtered:

"databases_discovered",
"exception",
"full_sql",
"instance" ,
"outliers_list",
"outliers_summary",
"policy_violations",
"session"

and the following data collections in SAGE are filtered:

"ae_dt" (signature)
"ae_dt_queries" (query signatures)
"ae_pr" (profile)
"ae_pr_new" (profile by time)
"db360" (DB 360 data)

Be aware that when using DLS, reports that are scheduled and delivered through the dispatcher propagate the user that has scheduled the report. I.e. the dispatcher runs the reports on behalf of the user that scheduled the report and assumes that user’s permissions. This is called the proxy user. This also means that in DLS environments the user that is used for dispatching must have the setUser role (this is not assigned by default and needs to be added during the implementation).

Also be aware that if you delete a user that has jobs already scheduled, the dispatcher will keep using that proxy user. If your DLS configuration is active for ALL users then these jobs will return results that are still filtered. But if you specified that only named users are filtered then these jobs will now run unfiltered. It is best that if you delete users when using DLS to make sure they do not still have scheduled jobs.

Application Level Security

You control what applications and data services users can access using the Application Security tab in the Users/Roles console.

By default all users have access to the SonarC application and data services. This is controlled through the assignment of application roles to the “*” user. Any application role assigned to * is assigned to all users in the system.

Application roles include named application roles, “*” (providing all grants), “SonarG” (providing access to the SonarC application) and “jsonStudioAccess”. You can create as many roles as needed and assign them directly to users. Each application role has a list of grants. A grant is for a dashboard or a data service (report, CSV download, etc). A grant name can have a specific name (such as a query name), * or a regular expression pattern.

Support

SonarC support may ask you to provide various logs using the “sonarg-collect-info” utility. This utility collects all the necessary logs and creates an encrypted tarball that can be loaded to your FTP account on ftp.jsonar.com. To run the utility:

$ sudo sonarg-collect-info

and provide the admin password.

You can also generate a non-encrypted file and then encrypt is separately using:

$ sudo sonarg-collect-info --no-encrypt

to get an unencrypted file. Examine it, then run:

$ sudo sonarg-encrypt <file>

You can use this last utility to encrypt any file which needs to be shared with SonarC personnel privately as it uses a SonarC public key.

Additionally, and report (existing or added through a new pipeline) can be dispatched via email to support by selecting the Session type in the scheduler. The system will produce a CSV with the results, encrypt it and send to the email you provide.

Synchronizing SonarC & LDAP

There are 2 synchronization options available between SonarC & LDAP: -Synchronize Data between LDAP and a collection in SonarW -Synchronize users

Note: data synchronization is mandatory when you set ldap synchronization, users synchronization is optional.

The synchronization is handled by the SonarDispatcher.

Data synchronization:

  • The process will import new and update existing documents in the target collection.
  • In order to delete the “d=” variable must be set. The delete statement is executed independently from the LDAP query, therefore it cannot use any fields or values retrieved by it.
  • The delete is executed prior to any update to the collection.

Example: {“ou”: “sonar1”} (will delete all documents adhering to this condition)

Users synchronization will:

  • Remove any existing users if they no longer belong to any of the mapped groups
  • Add new users
  • Update existing users if they change group association

Configuring the synchronization

First you need to configure the LDAP information to be used.

  • Edit the sonardispatcher.conf file (/opt/sonarfinder/sonarFinder/dispatcher.conf)

  • Go to the [ldap] section and set the next variables:

    useradmin_username=
    useradmin_password=
    useradmin_uri=
    #(change CN, DC to match your LDAP settings)
    ldap_username=CN=Administrator,CN=Users,DC=sonar1,DC=local
    #(change to match your LDAP settings)
    ldap_password=
    #(change to the actual LDAP IP)
    ldap_address=192.168.1.0
    #(change OU, DC to match  your LDAP settings)
    ldap_base=OU=jsonar-ou,DC=sonar1,DC=local
    #(Change to match the required LDAP query)
    ldap_query=(objectClass=user)
    
    # Support CN, sAMAccountName and userPrincipalName
    ldap_identify_field=
    max_query_cycles=1000000
    
    sonar_db=MyDB (Change to your target DB)
    sonar_coll=ldap_test (Change to your target Collection)
    #(Change to user with write permission to “sonar_db.sonar_coll”)
    sonar_username=
    sonar_password=
    sonar_keyfile=
    sonar_authdb=admin (Change to the relevant authentication DB)
    # JSON query from Sonar
    q={"cn": "$$cn"}
    u={"cn": "$$cn", "manager": "$$manager"}
    d=
    upsert=true
    multi=true
    group_mapping=
    

Legend:

The next 5 parameters are required only if Synchronizing users in Sonar based on LDAP groups. If you don’t synchronize users, leave these values empty:

1. useradmin_username - user in SonarW with 'userAdmin' role
       Note: this user needs to authenticate against “admin” DB

2. useradmin_password - Password for useradmin

3. useradmin_uri= - can be used instead of User-name/password cpmbination

4. ldap_identify_field= one of: sAMAccountName, CN,  userPrincipalName
   (Note: this will be the username users will login with)

5. group_mapping - maping between LDAP groups and SonarW roles.
   Single Line Example:
     {"read_group": [{"db":"admin", "role":"sonarGROUser"}], "readwrite_group":
     [{"db": "admin", "role":"sonarGUser"}]}

The mapping can includes multiple entries, each entry maps a single LDAP-group to one or more Sonar-roles. The syntax: “LDAP group name” : [{“db”: “db where the role is defined”, “role”: “Name of role”}, {“db”: “db where the role is defined”, “role”: “Name of role”}]

Base parameters:

ldap_username - LDAP server user with query permissions
ldap_password - Password for authenticating against the LDAP server
ldap_address - IP address (or domain name) of the LDAP server
ldap_base - the “location/branch” on the LDAP server to query against
ldap_query - the query to run against the LDAP server w
max_query_cycles - max number of data retrieve cycles from LDAP
# (each cycle brings 1 "page" of records as defined on the LDAP server, in most cases...
# ...LDAPO servers are set to return 1,000 records per page).
sonar_db - DB in Sonar where the LDAP user info be saved
sonar_col - the Collection within sonar_db where the LDAP user info be saved.
Note: sonar_db & sonar_col need to be created by the system admin
sonar_username – user to be used for updating the data in sonar
sonar_password – Password for sonar_username
sonar_keyfile - can be used instead of password
q - query to run against sonar_col to retrieve user data from previous runs
u - the update statement to be executed against the  sonar_col
d - Delete statement to be executed against the  sonar_col
upsert - set to True, for updating existing users and inserting new users
multi - set to True, to ensure all records of each user are updated.

Syntax for queries: (q & u):

{“Key to query”: “$$Variable returned from LDAP query”}

Example: q={“cn”: “$$cn”}

  • Save and exit.
  • Restart the sonardispatcher service

You can add more than one section to the dispatcher.conf, and each could do a different task.

Example_1 - importing data from LDAP:

The next configuration will import users’ information from LDAP and save it into the ‘ldap_users’ collection in the ‘sonargd’ DB. The next fields will be populated: name, manager, first_name, last_name, email, groups, title. No delete will be done on data already in the collection:

[ldap]
useradmin_username=
useradmin_password=
useradmin_uri=
ldap_username=CN=Administrator,CN=Users,DC=sonar1,DC=local
ldap_password=ldapPassword
ldap_address=192.168.4.222
ldap_base=ou=jsonar-ou,dc=sonar1,dc=local
ldap_query=(objectClass=user)

# Support CN, sAMAccountName and userPrincipalName
ldap_identify_field=
max_query_cycles=1000000

sonar_db=sonargd
sonar_coll=ldap_users
sonar_username=User1
sonar_password=Password1
sonar_keyfile=
sonar_authdb=admin
# JSON query from Sonar
q={"name": "$$cn"}
u={"name":"$$cn", "manager":"$$manager","first_name": "$$givenName", "last_name":
"$$sn", "email": "$$mail", "groups": "$$memberOf", "title": "$$title"}
# ensure the above is a single line
d=
upsert=true
multi=true
group_mapping=

Example2 - synchronizing LDAP users:

The next configuration will import users’ belonging to the ‘readwrite_group’, ‘read_group’ & ‘owners_group’ into the ‘ldap_users’ collection in the ‘sonargd’ DB. The next fields will be populated: CN & name. Users belonging to the groups: ‘owners_group’, ‘read_group’ & ‘readwrite_group’ will be granted access to sonar based on the next mapping:

owners_group - {“db”: “admin”, “role”:”root”} & {“db”: “admin”, “role”: “userAdmin”}

read_group - {“db”: “admin”, “role”:”sonarGROUser”}

readwrite_group - {“db”: “admin”, “role”:”sonarGUser”}

[ldap_users]
useradmin_username=User3
useradmin_password=Password3
useradmin_uri=
ldap_username=CN=Administrator,CN=Users,DC=sonar1,DC=local
ldap_password=ldapPassword
ldap_address=192.168.4.222
ldap_base=ou=jsonar-ou,dc=sonar1,dc=local
ldap_query=(&(objectClass=user)(|(memberof=CN=readwrite_group, OU=jsonar-ou,DC=sonar1,
DC=local)(memberof=CN=read_group, OU=jsonar-ou,DC=sonar1,DC=local)
(memberof=CN=owners_group, OU=jsonar-ou,DC=sonar1,DC=local)))
#ensure the ldap_query is on a single line
# Support CN, sAMAccountName and userPrincipalName
ldap_identify_field=sAMAccountName
max_query_cycles=1000000

sonar_db=sonargd
sonar_coll=ldap_users
sonar_username=User2
sonar_password=Password2
sonar_keyfile=
sonar_authdb=admin

# JSON query from Sonar
q={"name": "$$cn"}
u={"CN":"$$cn", "name":"$$name"}
d=
upsert=true
multi=true
group_mapping={"CN=owners_group,ou=jsonar-ou,dc=sonar1,dc=local": [{"db": "admin",
"role":"root"}, {"db": "admin", "role": "userAdmin"}], "CN=read_group,ou=jsonar-ou,
dc=sonar1,dc=local": [{"db": "admin", "role":"sonarGROUser"}],  "CN=readwrite_group,
ou=jsonar-ou,dc=sonar1,dc=local": [{"db": "admin", "role":"sonarGUser"}]}
#ensure the group_mapping is on a single line

Note: you must specify the full ldap path to the group in the mapping

Importing LDAP Users from a Sonarw collection

This process is put in place to accommodate cases where the SonarC server cannot query the LDAP source. In this case, it is assumed that the users will be exported to the Sonarw collection by a separate process, and than imported from that collection into the SonarC user management system.

In this example we assume that the following data was provided and inserted into the collection:

"Login Name" : <Username>,
"First Name" : <first name>,
"Last Name" : <last name>,
"EMAIL Address" : <email address>,
"Role" : <ldap group>,
"SonarG Source" : <sonarg source>

In this case, the configuration section for the LDAP import will have the next fields:

[ldap_2]
sonar_username_convert_to_lowercase=True (Optional)
import_users_db=<database>
import_users_collection=<collection>
# JSON query from Sonar
group_mapping={"read_group": [{"db": "admin", "role":"sonarGROUser"}],"owner_group":[ {"db": "admin", "role": "sonarGROUser"},{"db":"admin", "role" : "readWrite"}]}
group_app_roles_mapping ={"read_group": ["sonarG","WorkflowManager"], "owner_group": ["sonarG", "userAdmin","WorkflowUser"]}

Save the dispatcher.conf file, and restart the sonardispatcher service for the change to take effect, then run a job that will include the ldap target (under the ‘Integration’ tab), in this example, the target is ‘ldap_2’.


eMail Syncronization

When synchronising LDAP users, the system will also import the email addresses of all LDAP users, and will add them to the lmrm__users_email colelction in the sonargd DB. The emails will beused to map users to email addresses.


Schedule synchronization

In order to schedule a synchronization you need to go to the SonarC User-interface and schedule a new “job”.

Set the job frequency as required, give it a name and set the “LDAP Target” field to the “section” name as defined in the configuration

_images/ldap_sync.png

Encryption of Data-at-Rest

SonarC supports three encryptions options:

  • When running in the cloud, you may use the cloud provider’s encryption options.
  • Using Linux full-disk encryption.
  • Using SonarC’s built-in encryption option.

When relying on SonarC encryption you have three further options:

  1. KMIP configuration
  2. Manual key entry
  3. One time key entry

Option (1) requires that you have a KMIP-capable key-management solution. SonarC setup asks for all parameters and saves the information on the pykmip.conf and on the sonard-setup-encryption config file.

Option (2) and (3) requires the administrator to enter a key directly - option (2) every time the server is booted and option (3) only once. In option (3) setup saves the password locally so that SonarC may be used after a reboot without an operator. This is the simplest but also the least secure option since the password persists on the computer hard drive.

For option (1) and (2), the user needs to run the sonard-setup-encryption script on every machine reboot and provide the password for the key generation or for the KMIP server access - both these options are more secure.

Note that SonarC’s data repository (SonarW) refuses to start if there’s a sonard-setup-encryption configuration and no encryption key is found on the system ramdisk. In this case, a message is printed on the syslog.

For more information on setting up the keys for SonarW seee http://sonarwdocs.jsonar.com/latest/encrypt.html