EUS Enterprise Roles Developer Use Case

Oracle's Enterprise User Security feature continues to gain adopters who want to centralize account management across all Oracle databases in the enterprise. EUS simplifies and increases quality in processes for adding user accounts, managing credentials, eliminating orphaned accounts, and more.

Organizations who need more convincing may want to take a closer look at theEnterprise Role concept of EUS. Enterprise Roles can actually multiply the economic and security benefits that EUS brings, by introducing a framework for managing database privileges across applications and test levels.

The structure of the Enterprise Role is a pair of collections maintained in the Oracle directory:

• The first collection (Grantees) is of the individuals and/or groups who have access to the Enterprise Role. The organization manages group membership in their enterprise LDAP directory, such as AD.

• The second collection (DB Roles) contains the privileges, in the form of a list of distinct database roles. Each entry in the list contains the identification of a database and a specific role on that database. Therefore, a single enterprise role can span several databases, granting one or more distinct roles on each of those databases.

An interesting application of this has come up in a couple of our customer engagements. The use case involves several development teams who need access to their respective application schemas. Typically, developers hold full modify access (SELECT, INSERT, UPDATE, DELETE) to application data in lower test levels, but only SELECT access in user acceptance and production levels. Let's look at an example:

2 Application Schemas:

• HUM_RSRCE

• PRICING

5 Database instances spanning 4 Test Levels:

• UNIT (both apps run on DB instance: UNITDB)

• INTEGRATION (both apps run on DB instance: INTGDB)

• USER_ACCEPT (both apps run on DB instance: UACCDB)

• PRODUCTION (HUM_RSRCE runs on prod instance: HRPROD; PRICING runs on prod instance: PRPROD)

Database roles defined to manage schema privileges:

• HR_READ -- select on HUM_RSRCE schema objects

• HR_MODIFY -- select, insert, update, delete on HUM_RSRCE

• PR_READ -- select on PRICING schema objects

• PR_MODIFY -- select, insert, update, delete on PRICING

Identification of users requiring Developer privileges:

• For HUM_RSRCE:

  • Members of the HR_DEV group in AD

  • Judy Stinch, the HR IT Liaison

• For PRICING:

  • Members of the MKT_DEV group in AD

  • John Slough, the Marketing IT Liaison

Without EUS the work required to manage all these users and grants on each database, through repeated development cycles and organization changes, would be tedious and prone to error. Let's look at how EUS simplifies this. First, by implementing EUS-managed Shared Schemas, user account provisioning on the listed databases is no longer necessary.

Next we create EUS Enterprise Roles to manage the privileges against each application schema on each test level. First, the local roles on each database must be altered to designate them as global roles. Then, we create just two enterprise roles and their collections in the EUS directory:

HR_DEVELOPER

• associates those needing Developer privileges against the HUM_RSRCE schema with the appropriate global role in the appropriate test level database

PR_DEVELOPER

• associates those needing Developer privileges against the PRICING schema with the appropriate global role in the appropriate test level database

The diagram below provides the details of the HR_DEVELOPER enterprise role and its containers. The PR_DEVELOPER role would have a similar structure.

The power of this arrangement is obvious. A single object, existing in the directory, manages privileges for a class of users across a series of databases. And the privileges can vary from database to database.

Notice that the roles inside each database are the same from level to level. Because of this consistency, operations such as level promotion or test data refresh can execute without any change to the role or grant structure before or after. The Enterprise Role ensures that developer access is immediately available with privileges appropriate to the level.

In this example, we developed a database-level role structure that doesn't have to change across migration levels, then used EUS Enterprise roles to manage the differences in developer privileges across environments. The result is a configuration with:

• higher, more stable security;

• improved quality out of the development process; and

• lower maintenance costs.

Hub City Media has the world's best team to help bring the benefits of EUS to your organization. Please contact us to schedule a discovery session.

---

SENIOR DBA