User-Schema Mappings

The directory maintains the mapping for the Enterprise Users to specific database schemas. Before EUS, if you wanted to create database users that had separate usernames, passwords, and privileges, you had to create individual database schemas for each user. With EUS, this 1:1 mapping model is still supported, but so are the shared schema designs.

The separation of user from schema allows administrators to provision the user once in the directory and then create a mapping or mappings to the databases that participate in the EUS. The user will then be able to log on to any of the databases within the domain as long as a mapping for the user exists.

Creating the Shared Schemas

The most common use of mapping is a shared schema in which multiple Enterprise Users share the same schema (N:M mapping). The first step is to create a container schema that will be shared by the users. For the KNOX Enterprise User example, we had to first create a shared schema and grant the privileges to connect:

sec_mgr@KNOX10g> CREATE USER app_public IDENTIFIED GLOBALLY AS ''; User created. sec_mgr@KNOX10g> GRANT CREATE SESSION TO app_public; Grant succeeded.

The syntax used to create this schema is what makes it sharable. There is no password, and the empty single quotes indicate that the account is anonymous from the database’s view. The directory will be able to map users to this schema (sometimes referred to as the shared schema). Notice that there is no way to log on directly to the schema because there is no password. If you check the password value stored in the database for the APP_PUBLIC schema, you’ll see that there’s a non-null value:

sec_mgr@KNOX10g> SELECT username, password     2    FROM dba_users     3   WHERE username = 'APP_PUBLIC'; USERNAME                       PASSWORD   ------------------------------ --------------------   APP_PUBLIC                     GLOBAL

It appears as though the database schema APP_PUBLIC was created with the “Identified by values ‘GLOBAL’” syntax you learned in Chapter 2. These two creation statements are identical. In the following example, the APP_PUBLIC user is dropped and then re-created using the identified by values clause. The Enterprise User is then able to connect to this schema. Note that this is done for illustrative purposes and isn’t the officially supported way to create shared schemas:

sec_mgr@KNOX10g> DROP USER app_public; User dropped. sec_mgr@KNOX10g> -- create user with password of GLOBAL   sec_mgr@KNOX10g> -- Note this is NOT the officially supported   sec_mgr@KNOX10g> -- way to create shared schemas   sec_mgr@KNOX10g> CREATE USER app_public IDENTIFIED BY VALUES 'GLOBAL'; User created. sec_mgr@KNOX10g> GRANT CREATE SESSION TO app_public; Grant succeeded. sec_mgr@KNOX10g> CONN knox/oracle10g  Connected. app_public@KNOX10g>

You can’t map an Enterprise User to a schema that doesn’t have ‘GLOBAL’ as the password value. If you map an Enterprise User to a database-authenticated schema, for example SCOTT, the Enterprise User will receive an ORA-01017: invalid username/password error when they try to log on. When you create the database schema for the Enterprise User, use the identified globally as ‘’ syntax.

Directory Mappings

Now that the database has a schema to which the Enterprise User can attach, you need to define the mappings. The user mappings can be done at both the domain level and the individual database level. The first choice is to allow a domain-level mapping that defines the user-schema relationship across all databases in a domain. When you set up EUS, you register your databases in specific domains. The domain-level mapping is convenient because you only have to create one mapping for all of your databases. From a management perspective, it provides the consistency that the user(s) defined in the directory server are always mapped to the same schema(s) everywhere in your enterprise. This also assumes that every database has the same shared schemas.

The second mapping choice you have is to provide a mapping for each individual database. This adds the flexibility of allowing the same user to attach to different schemas for different databases. This would be used in designs where an application account already exists for the user but differs across databases. For example, a user might already have an account of DKNOX in one database, and DKNOX_US in a different database. These accounts (schemas) can be preserved by supplying a specific mapping for each database. The benefit? The user can now log in to both databases using the same username and password.

There is another dimension to the mapping that must be understood before the process can occur.

Directory Entry Mappings and Subtree Mappings

There are two more ways to define the user-schema relationship for both domain-level mapping and individual database mapping. First and most specifically, the mapping is from the individual directory user to a database schema. This is called an entry mapping because it maps a specific user entry to a specific database schema.

Second and more generally, the mapping can be defined for a group of directory users. That is, all the users located in a certain part of the directory tree will share a mapping to a database schema. This is called a subtree mapping.

Mapping Permutations Example

For an Enterprise User, there are four possible ways the user can be mapped. The database will follow a specific path of most specific to least specific in trying to determine in which schema a user should reside. The following example illustrates the various mappings as well as how the user’s schema is resolved.

Creating the Schemas

For this example, four database schemas are used. Each schema will be used as a container for the Enterprise User.

CREATE USER domain_entry IDENTIFIED GLOBALLY AS ''; GRANT CREATE SESSION TO domain_entry; CREATE USER domain_subtree IDENTIFIED GLOBALLY AS ''; GRANT CREATE SESSION TO domain_subtree; CREATE USER db_entry IDENTIFIED GLOBALLY AS ''; GRANT CREATE SESSION TO db_entry; CREATE USER db_subtree IDENTIFIED GLOBALLY AS ''; GRANT CREATE SESSION TO db_subtree; 

The Enterprise User requires the CREATE SESSION privilege to log on to the database. You can either grant the privilege to the Enterprise User using Enterprise Roles (see Chapter 7), or grant the privilege to the database schema to which they will attach. You may find it easier to grant the privilege once to the database schema rather than grant it many times to all the Enterprise Users that will be sharing the schema.

Note 

There is no security concern in granting the privilege to the shared database schema because a user can’t log on directly to the shared schema, and the CREATE SESSION privilege will be required by all users (if you want them to actually attach to your database); therefore, you’re not violating the least-privilege principle.

Note 

You should only grant database object and system privileges to shared database schemas when all users sharing the schema require those privileges.

Creating the Mappings

To create the user-schema mappings, you’ll use the ESM client-server program. For this example, we’ll create four user mappings, one to each database schema. We’ll log on as the KNOX user to show which mapping was used.

For clarity, the schema names created above describe the level of mapping that will occur. For example, the DOMAIN_SUBTREE schema will apply to all users in the directory subtree across all databases within the enterprise domain. Figure 5-4 shows a domain mapping with one entry mapping and one subtree mapping. The first entry shows that all users that exist in the subtree defined by the Directory Entry field will be mapped to the DOMAIN_SUBTREE schema. The second entry shows the Knox user will be mapped to the DOMAIN_ENTRY schema. There are five databases defined in this domain, which means these two mappings will exist across all five databases.

Figure 5-4: Domain mappings apply to all databases registered in the domain.

The directory doesn’t verify that the database schema does or doesn’t exist. This will be done at the time the user logs on. If the user(s) is mapped to a schema that doesn’t exist, an ORA-01017: invalid username/password error will be raised. This is a great technique for allowing all but one user into the database. That is, provide a mapping to a schema that doesn’t exist, and the user will not be able to log on.

Tip 

To prevent a user from accessing your database while at the same time allowing general user access, create a user-schema mapping to a schema that doesn’t exist.

The same types of mappings are defined at the individual database levels; for each database in a domain, you can specify entry or subtree mappings. In Figure 5-5, you’ll see two analogous entries for the knox10g database. The first entry maps all users in the directory subtree to the DB_SUBTREE schema. The second entry maps the Knox directory user to the DB_ENTRY schema. The database-level mappings supersede the domain-level mappings.

Figure 5-5: Database mappings are specific to the database and supersede domain mappings.

Testing the Mappings

Using the four previously defined mappings, you’ll see how the actual user-schema resolution occurs. This is crucial because it ultimately determines which schema the user will be attached to, which is important for ensuring your users have the correct database privileges.

The resolution process starts from the most specific mapping definition to the least specific. The directory will search in the following order:

1. Specific user entry for the specific database

2. Directory subtree entry for the specific database

3. Specific user entry for the domain

4. Directory subtree entry for the domain

For the database knox10g, a database-specific mapping for the Knox user exists for the DB_ENTRY schema. When the user connects to the Dagger database, there’s no database-specific mapping, but there’s an entry-level mapping for the Knox user to the DOMAIN_ENTRY schema.

To illustrate the identity preservation, the following SQL connects as the Knox user and then displays the database schema and the database name, which are also viewed in the SQL prompt, and the directory user’s identity. This last item shows how the Enterprise User’s identity is preserved even when connected to a shared database schema.

SQL> -- test DB specific mappings  SQL> -- conn to Knox10g database which has database specific entry  SQL> CONN Knox/oracle10g@knox10g  Connected. db_entry@KNOX10g> COL "Directory User" format a40  db_entry@KNOX10g> COL schema format a15  db_entry@KNOX10g> COL database format a10  db_entry@KNOX10g> SELECT SYS_CONTEXT ('userenv', 'external_name')   2                                   "Directory User",   3         USER SCHEMA,   4         SYS_CONTEXT ('userenv', 'db_name')   5                                           DATABASE      6    FROM DUAL; Directory User                           SCHEMA          DATABASE   ---------------------------------------- --------------- ----------   cn=Knox,cn=users,dc=iac,dc=oracle,dc=com DB_ENTRY        knox10g     db_entry@KNOX10g> -- test domain mapping by connecting to different database   db_entry@KNOX10g> -- Note, must create database schemas on target database   db_entry@KNOX10g> -- for this to work   db_entry@KNOX10g> CONN Knox/oracle10g@dagger  Connected. domain_entry@DAGGER> SELECT SYS_CONTEXT ('userenv', 'external_name')   2                                   "Directory User",   3         USER SCHEMA,   4         SYS_CONTEXT ('userenv', 'db_name')   5                                           DATABASE      6    FROM DUAL; Directory User                           SCHEMA          DATABASE   ---------------------------------------- --------------- ----------   cn=Knox,cn=users,dc=iac,dc=oracle,dc=com DOMAIN_ENTRY    dagger

Removing the user-specific entries for the Knox user at both the database and the domain levels forces the mapping to resolve to the subtree entries. Running the connection test again validates the resolution order:

SQL> -- conn to Knox10g database which has database specific entry   SQL> CONN Knox/oracle10g@knox10g  Connected. db_subtree@KNOX10g> SELECT SYS_CONTEXT ('userenv', 'external_name')   2                                   "Directory User",   3         USER SCHEMA,   4         SYS_CONTEXT ('userenv', 'db_name')   5                                           DATABASE      6    FROM DUAL; Directory User                           SCHEMA          DATABASE   ---------------------------------------- --------------- ----------   cn=Knox,cn=users,dc=iac,dc=oracle,dc=com DB_SUBTREE      knox10g     db_subtree@KNOX10g> -- test mapping by connecting to different database   db_subtree@KNOX10g> -- Note, must create database schemas on target database   db_subtree@KNOX10g> -- for this to work   db_subtree@KNOX10g> CONN Knox/oracle10g@dagger   Connected. domain_subtree@DAGGER> SELECT SYS_CONTEXT ('userenv', 'external_name')   2                                   "Directory User",   3         USER SCHEMA,   4         SYS_CONTEXT ('userenv', 'db_name')   5                                           DATABASE     6    FROM DUAL; Directory User                           SCHEMA          DATABASE   ---------------------------------------- --------------- ----------   cn=Knox,cn=users,dc=iac,dc=oracle,dc=com DOMAIN_SUBTREE  dagger

Security for Shared Schemas

A majority of the EUS configurations utilize the shared schema capability. As the name implies, the users share a database schema; however, this differs from the anonymous connections where the application connects to the same schema because anonymous connections are handled by applications so the user’s identity may not be preserved.

As shown in the previous example, the EUS shared schema design preserves the user’s identity. The identity is automatically stored in a USERENV attribute called external_name. Not only is this value accessible to your database programs—triggers, secure application roles, VPD policies, and so on, the value is also audited when auditing is enabled. This allows you to use database security with the assurance that the security controls are being accurately applied to the real end users.

For database object and system privileges, you can use Enterprise Roles, which are discussed in Chapter 7.

Exclusive Schemas

The Enterprise Users don’t have to share database schemas. You can create distinct user-schema (1:1) mappings. The benefit to using exclusive schemas is that the database security processes can be based on the database schema. It’s often desirable to have exclusive mappings for privileged users because the security assurance is higher. Because the schema isn’t shared, security access controls and auditing that operates at a schema level can be easily and intuitively implemented. (For two users sharing a database schema, there is no way to audit one of the users and not the other.) The obvious administration drawback with 1:1 mappings is that there’s potentially a database schema and an LDAP entry for every end user.

There are two ways to implement exclusive schemas. First, you can use ESM to create an entry-level mapping for the directory user to a database schema. The exclusivity is provided when you don’t assign any other directory users to the database schema. The important point: there is no way to enforce the exclusive mapping in the directory.

The alternative implementation is to have the database do the user-schema mapping. This ensures that the exclusivity is upheld. You create the mapping at the same time you create the database schema by providing the user’s DN in the CREATE USER DDL. For example, to create an exclusive schema mapping for the KNOX directory user to the KNOX_DB database schema, you would issue the following:

sec_mgr@KNOX10g> -- Create a database mapped Enterprise User sec_mgr@KNOX10g> CREATE USER knox_db IDENTIFIED   2    GLOBALLY AS 'cn=Knox,cn=users,dc=iac,dc=oracle,dc=com'; User created. sec_mgr@KNOX10g> GRANT CREATE SESSION TO knox_db; Grant succeeded. sec_mgr@KNOX10g> CONN knox/oracle10g Connected. knox_db@KNOX10g> COL "Directory User" format a40 knox_db@KNOX10g> SELECT SYS_CONTEXT ('userenv', 'external_name')   2                                   "Directory User"   3    FROM DUAL; Directory User ---------------------------------------- cn=Knox,cn=users,dc=iac,dc=oracle,dc=com

Note that the user provides KNOX as the username during database log on. The directory is performing the authentication, however the database is providing the schema mapping. A database schema mapping supersedes any directory mapping for the user. Unlike operating system authenticated users, there is no dependency on the database schema name and the directory username; the database schema in the previous example could have been named anything.

When you view the exclusive schema in the DBA_USERS view, you can identify the directory user that is mapped by looking at the EXTERNAL_NAME column. Note that both the exclusive schema KNOX_DB and the shared schema APP_PUBLIC have the password ‘GLOBAL’, but the KNOX_DB also has an EXTERNAL_NAME value. This shows that both database schemas are used for EUS; the APP_PUBLIC mappings are created by the directory, and the KNOX_DB mapping is provided locally by the database.

sec_mgr@KNOX10g> COL password format a20 sec_mgr@KNOX10g> COL username format a10 sec_mgr@KNOX10g> COL external_name format a40 sec_mgr@KNOX10g> SELECT username, password, external_name   2    FROM dba_users   3   WHERE username IN ('KNOX_DB', 'APP_PUBLIC'); USERNAME   PASSWORD             EXTERNAL_NAME ---------- -------------------- ---------------------------------------- APP_PUBLIC GLOBAL KNOX_DB    GLOBAL               cn=Knox,cn=users,dc=iac,dc=oracle,dc=com

Converting Existing Database Users to Directory Users

The exclusive schema design is often the first step in introducing EUS to existing applications. The 1:1 security model is consistent with the existing security. Assuming you have already configured your database for EUS, you can change currently defined database users to Enterprise Users in three easy steps. The following example shows how this can be done for the SCOTT user. First, alter the user by changing their database authentication to be directory-based. Note that this doesn’t alter any of the schema objects or privileges.

sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED GLOBALLY AS ''; User altered.

Next, create the user in the directory. The Enterprise Security Manager will allow you to create the SCOTT user in the directory. The final step is to create the exclusive mapping for the Scott directory user to the database schema SCOTT. When you now connect to the database with a username of SCOTT, the password is authenticated with the directory. All of the original schema privileges and objects are still enabled and available. The transparency of making these changes makes them applicable to existing applications. The user or application will not know that the user is now being authenticated by the directory.

sec_mgr@KNOX10g> CONN scott Enter password: Connected. scott@KNOX10g>

If you later decide you want to revert back from the directory-authenticated user to the database-authenticated user, you simply alter the user again:

sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY tiger; User altered. sec_mgr@KNOX10g> conn scott/tiger Connected. scott@KNOX10g>

In “The Connection Process” section, earlier in the chapter, you learned that the database first checks for the user’s password locally. It’s only if the password has the value of GLOBAL that the database refers to the directory for user authentication. This allows you to switch database users from locally authenticated to directory authenticated by simply using the ALTER USER command as just illustrated.

Another attractive alternative for moving all your database users to Enterprise Users is to employ the User Migration Utility. This is a program that you can configure for bulk migration of your existing database users to directory users. Exclusive schemas and shared schemas are supported. See Appendix G of the Oracle Advanced Administrator’s Guide 10g for more information.