Friday, November 25, 2016

HOWTO - Implement a cross-platform mixed DBA Role (Oracle+SQL Server) using Centrify Server Suite

What you'll need
  • Active Directory and a Centrify Zone
  • Centrify Access Manager and access to perform the configuration
  • A UNIX or Linux System and the Centrify Enterprise Edition Agent (DirectControl/DZ & DirectAudit)
  • A Windows System and the Centrify Enterprise Edition Windows Agent (DZWin/DAWin)
  • Two Active Directory Groups and a test user.
Planning

Privileges Based on Roles
In the previous entry, you saw how we conquered the issue of limiting access to systems based on business need-to-know and how we can create a SuperUser-like role across platforms.  Now it's time to implement a true Role Based governance model.  Just like with any capability (security included) you must achieve balance on the people-process-technology triad, in the context of Roles, its key is to understand how people use privileges in their job functions, implement a process around it and leverage the technology to implement it.  Here's an oversimplified example for DBAs in a mixed Oracle and SQL Server shop.

How DBAs access systems:   SSH and Toad on Linux and RDP on Windows
Typical tasks needed for operations:  In Linux, DBAs need to run the sqplus command as the Oracle account and switch user to oracle.  In Windows, DBAs need to run SQL Server Manager and to manipulate the SQL Server Service.
Typical tasks needed for change-control:  DBAs need to elevate to Root during upgrades and installations.

In my fictitious company, I will allow the SSH PAM access role and the commands to run sqlplus and switch to oracle.  The Sysadmin role (that allows to elevate to root) will only be granted with a change-control number and duration.

This process needs to be adjusted because of two things:
  • Resistance to change
    I guarantee you that users will initially think that you're making their jobs harder and will try to find ways to resist.
  • Nuances around how people perform their jobs
    For example, a Windows DBA may also need access to SQL Server Configuration tools; or a UNIX DBA may like to switch user without profile  (su oracle, instead of su - oracle).
So be ready to have a review of roles and rights with certain cadence, to add/remove commands or apps; as well a to make usability adjustments.  Make it part of your attestation process.

In our example, we'll use the Centrify-Model-Mixed-Role-DBAs group to provision this role.

Access Based on Roles
The concept continues to be the same as the previous post, but now you are further breaking down your access governance model based on your needs.  For example:  you may have a DEV-QA-PROD train for an application and you can grant access just to that set of systems, and you can classify systems based on that model;  This is where your investment in Centrify Professional Services can pay the most.   In my example, I'll just use a Computer Role called Mixed Database Servers, based on an AD group called Centrify-Model-Mixed-CR-Database Servers.

Security Operations Requirements
In the previous example we leveraged both the UNIX Syslog and the Windows Event log to augment log collection capabilities.  Since DirectAudit data is contained inside a SQL server database, this is another repository that can be tapped by log aggregators.  Since DirectAudit provides session data, information gathered from security operations can be further investigated by looking at session data.

Provisioning
Here the principles continue to be the same as the previous entry. Granting or revoking the role or making a system part of the collective is performed via AD Group Membership (Manually with ADUC, Programmatically with PowerShell or Scripting and controlled via any Workflow engine)

Planning for Platforms
Same exceptions apply:  IBM AIX with LAM enabled don't allow the breakdown of PAM access rights.

Implementation
Just like the previous entry, remember that these steps can be automated via Centrify adedit on UNIX/Linux or Centrify Access Module for PowerShell as well as the DirectManage SDK.

Recap: Rights go into Roles, Roles are configured and Assigned to AD Principals in a Given Scope (zone, child zone, computer role or individual computer)

Step 1:  Create the AD Groups for the Computer Role and to assign the Roles
  1. Manually you can open Active Directory Users and Computers (ADUC)
  2. Navigate to your designated OU, right click > New > Group.
  3. Select the appropriate scope and give it a name.  In our case we repeat this for both Centrify-Model-Mixed-CR-Database Servers and Centrify-Model-Mixed-Role-DBAs
Step 2:  Create the Mixed Database Systems Computer Role
  1. Open Access Manager and right-click your Zone > Authorization > Computer Roles and Select Create New Computer Role.
  2. In the New Computer Role window, give it a name (Mixed Database Systems), description and browse for the Centrify-Model-Mixed-CR-Database Servers group.  Then Press OK.
Step 3:  Create the DBA commands to be used in UNIX and Linux platforms

SQL Plus as Oracle
Creating this command requires a bit of research. Information about the diversity of platforms, versions and their differences are relevant. For example, in my environment, I’m using Oracle Express 11G running on Linux.

Another important detail has to do with the environment variables. I want to conserve them in this case so sqlplus has all it needs to launch correctly:

Switching to the Oracle account
Some DBAs may need to do a simple (no profile) elevation, or they would want the opposite, Centrify offers the flexibility of Regular or Global expressions.

Service Control for the Oracle Service
More research required. In my environment, the service is oracle-xe and I will limit the directives available for service.


Step 4:  Create the DBA commands to be used in Windows platforms

SQL Server Management Studio
Windows application rights provide a lot of flexibility. Some key highlights
  • You can consolidate several applications in a single command
  • Applications can be defined manually, by looking at local or remote processes or by browsing directly to the file.
  • Application arguments can be evaluated with or without case sensitivity
  • Application types can be further broken down (e.g. exe, bat, cmd, vbs, ps1, etc.)
  • Additional criteria can be added based on the file metadata (e.g. version, company, etc)
  • Application rights can be executed in 3 different contexts
    • As a service account: the user in the role does not need to know the credentials.
    • As a member of an AD group without persistent membership
    • As a member of a Windows built-in group (like Power Users, etc.)
In the case of the SQL Server Management studio, I have a Windows native deployment with a SQL management account. I can easily use an AD group, but since I have this privilege elevation mechanism, I don’t need to fall into the bad habit sharing that account - there are many other ways to accomplish the same objective.

Stop, start and query the SQL Server Service command
I'm choosing to use the service control command line utility (sc.exe) in the context of the local administrator account to provide the DBA with the ability to control the SQL server service.  The alternative today is to grant the DBA administrator rights, which they can use to stop other services (like the AV or firewall).


Step 5: Creating, configuring, assigning the role and provisioning a user
This process follows exactly the same steps of the previous post. Keep in mind that the Windows rights will be for remote (RDP or Citrix) and to add the UNIX and Windows rights after the role is created.

The role assignment will be performed at the Computer Role level, so it only applies to computers provisioned into that group. Finally you can add your test user to the group that gets the role assigned.

Part of our process dictates that if they do need higher privileges, they can request it based on a change control approval.


Step 6: Verifying and the rights on the UNIX/Linux system
To view the role attributes and definition, use the dzinfo command
$ dzinfo
User: thomasf
Forced into restricted environment: No

  Role Name        Avail Restricted Env
  ---------------  ----- --------------
  Mixed            Yes   None
  DBA/Global

    Effective rights:
        Password login
        Non password login
        Allow normal shell

    Audit level:
        AuditIfPossible

    Always permit login:
        false


  PAM Application  Avail Source Roles
  ---------------  ----- --------------------


Privileged commands:
  Name             Avail Command               Source Roles
  ---------------  ----- --------------------  --------------------
  DBA Oracle -     Yes   service oracle-xe     Mixed DBA/Global
  oracle-xe              start|stop|status|re
  service                start
  control/Global
  DBA Oracle -     Yes   su oracle             Mixed DBA/Global
  switch to
  oracle
  no-profile/Glob
  al
  DBA Oracle -     Yes   sqlplus *             Mixed DBA/Global
  sqlplus as
  oracle/Global
  DBA Oracle -     Yes   su - oracle           Mixed DBA/Global
  switch to
  oracle with
  profile/Global

To view the command definitions, use the dzdo –l command

$ dzdo -l
AD Password:
User thomasf may run the following commands on this host:
    (root) service oracle-xe start|stop|status|restart
    (oracle) sqlplus *
    (root) su oracle
    (root) su - oracle

To use your privileges via privilege elevation use dzdo

$ dzdo service oracle-xe restart
Shutting down Oracle Database 11g Express Edition instance.
Stopping Oracle Net Listener.

Starting Oracle Net Listener.
Starting Oracle Database 11g Express Edition instance.

Finally, and for the benefit of security operations, all privilege elevation continues to be written to syslog:
Dec 27 15:15:53 engcen6 dzdo:  thomasf : TTY=pts/3 ; PWD=/home/thomasf ; 
USER=root ; COMMAND=/sbin/service oracle-xe restart

Step 7: Verifying and the rights on the Windows system

In Windows, to view the role definition, you can open the Authorization Center in the System Tray, the role definitions tab shows the applications defined for the role, double clicking provides a deeper dive.
 
To use your privileges, in the GUI you can use the Run as role shell extension, pick the role and the application will be launched as defined. Finally, just like with UNIX, privileged elevation will be logged in the Windows Event Log.
 

Step 8: Session Capture and Replay with Audit Analyzer
Audit analyzer provides a very neat view of what's going on with your audited systems;   in the left pane you have queries and categories, on the right side you can see the sessions.  The columns include the AD user, the client and server systems, the status of the session, etc.


If you drill down into a session, you can see an index of the commands or events that happened during the session.


Looking at a replay, provides you with the event data +  a video of the terminal or Windows session.


Reconstructing what happened (especially on Windows platforms) is as simple as just watching and controlling like a DVR.


Finally, let's look at this scenario and do some testing:

This article is a repost of  a two-part series I wrote for the Centrify Community.

2 comments:

  1. It is very good blog and useful for students and developer , Thanks for sharing

    Sql server DBA Online Training

    ReplyDelete
  2. Awesome article I really glad to read this article it’s really nice Thanks.

    Cross-Platform Mobile Development

    ReplyDelete