2 Getting Started with Enterprise User Security
Enterprise User Security enables you to centrally manage database users across the enterprise. Enterprise users are created in Oracle Internet Directory, and can be assigned roles and privileges across various enterprise databases registered with the directory.
Configuring Your Database to Use the Directory
The first step in configuring Enterprise User Security is to configure the database to use the directory. Running the Net Configuration Assistant (NetCA) tool enables you to configure the directory host name and port that your database should use.
To configure your database for directory usage:
Registering Your Database with the Directory
The next step is to register the database with the directory service. The Database Configuration Assistant (DBCA) tool enables you to register the database with Oracle Internet Directory.
To register the database with the directory:
Note:
After you register the database with the directory, make sure that auto login is enabled for the database wallet. The default wallet is created in the $ORACLE_BASE
/admin/
database_sid
/wallet
directory.
You can verify that auto login for the wallet is enabled by checking for the presence of the cwallet.sso
file in the wallet directory. If the file is not present, you can enable auto login by opening the wallet using Oracle Wallet Manager, and using the option to enable auto login for the wallet.
Creating a Shared Schema in the Database
Creating a shared schema in the database enables you to map multiple enterprise users to the same schema. Example 2-1creates a shared schema, global_ident_schema_user
, and grants the CONNECT
role to it.
Example 2-1 Creating a Shared Schema
SQL> CREATE USER global_ident_schema_user IDENTIFIED GLOBALLY; User created. SQL> GRANT CONNECT TO global_ident_schema_user; Grant succeeded.
Mapping Enterprise Users to the Shared Schema
Enterprise User Security can be managed using Enterprise Manager. Example 2-2 maps the DN, cn=users, dc=us, dc=oracle, dc=com
to the shared database schema, global_ident_schema_user
.
Example 2-2 Mapping Enterprise Users to the Shared Schema
To create the user-schema mapping:
-
Log in to Enterprise Manager Cloud Control, as an administrative user.
-
To navigate to your database, select Databases from the Targets menu.
-
Click the database name in the list that appears. The database page appears.
-
Under the Administration menu, select Security, Enterprise User Security. The Oracle Internet Directory Login page appears.
-
Enter the distinguished name (DN) of a directory user who can administer enterprise users in the User field. Enter the user password in the Password field. Click Login.
See Also:
Registering Your Database with the Directory, specifically Step 6, where you previously entered the user DN and password to register the database with the directory service
The Enterprise User Security page appears.
-
Click Manage Enterprise Domains.
The Manage Enterprise Domains page appears.
-
Select the enterprise domain which contains the database. Click Configure.
The Configure Domain page appears.
-
Click the User-Schema Mappings tab. All user-schema maps that apply to the enterprise domain are displayed.
-
Click Create.
The Create Mapping page is displayed.
-
Under the From section, select Subtree. Click the Search icon. Select the DN, cn=Users, dc=us,dc=oracle,dc=com.
-
Under the To section, enter global_ident_schema_user in the Schema field. Click Continue.
The user-schema mapping is added in the Configure Domain page.
-
Click OK.
Connecting to the Database as an Enterprise User
All users in the mapped Oracle Internet Directory subtree can now connect to the database as enterprise users. Example 2-3 shows the cn=orcladmin, cn=users, dc=us,dc=oracle,dc=com
user connecting to the database.
Example 2-3 Connecting to the Database as an Enterprise User
SQL> CONNECT orcladmin Enter password: Connected.
Using Enterprise Roles
Enterprise roles are created in the directory. Enterprise roles contain global roles from different databases that are part of the enterprise domain. Enterprise roles are used to assign database privileges to enterprise users.
Example 2-4 creates two enterprise users, Joe and Nina. Both these users are created in the subtree, cn=Users, dc=us,dc=oracle,dc=com
, which is already mapped to the global_ident_schema_user
in the EUSDB database. See RFC4519 — Lightweight Directory Access Protocol (LDAP) : Schema for User Applications for information about the LDAP attribute types shown in the previous subtree example.
Nina is an HR manager. She needs the SELECT
privilege on the hr.employees
table in the EUSDB database. Example 2-4 achieves this using enterprise roles.
Example 2-4 Using Enterprise Roles
We start by creating two enterprise users, Joe and Nina. You can create enterprise users using the Oracle Internet Directory Self Service Console.
To create enterprise users, Joe and Nina:
-
Connect to the Oracle Internet Directory Self Service Console. Use the following URL:
http://
hostname
:port
/oiddas/
Here,
hostname
is the name of the host that is running the Oracle Internet Directory server. Theport
number is the TCP port number on which the Oracle Internet Directory Self Service Console is running. This is 7777 by default. -
Click the Directory tab.
The Sign In page appears.
-
Log in as the user that can create users in Oracle Internet Directory.
The User page appears.
-
Click Create.
The Create User page appears.
-
Enter joe under User Name. Enter values for the other required fields. Select Enabled under Is Enabled.
-
Click Submit.
-
Click Create Another User.
The Create User page appears.
-
Enter Nina under User Name. Enter values for the other required fields. Select Enabled under Is Enabled.
-
Click Submit. Click OK.
Next, we create a global role in the database that allows access to the hr.employees
table. The following SQL*Plus statements create a global role, hr_access
and grant the necessary privilege to it.
SQL> CREATE ROLE hr_access IDENTIFIED GLOBALLY; Role created. SQL> GRANT SELECT ON hr.employees TO hr_access; Grant succeeded.
Next, we create an enterprise role called hr_access
and assign the global role to it. We then assign this enterprise role to the enterprise user, Nina. The enterprise role can be created using Enterprise Manager.
To create the enterprise role, hr_access:
-
Log in to Enterprise Manager Cloud Control, as an administrative user.
-
To navigate to your database, select Databases from the Targets menu.
-
Click the database name in the list that appears. The database page appears.
-
Under the Administration menu, select Security, Enterprise User Security. The Oracle Internet Directory Login page appears.
-
Enter the distinguished name (DN) of a directory user who can administer enterprise users in the User field. Enter the user password in the Password field. Click Login.
The Enterprise User Security page appears.
-
Click Manage Enterprise Domains.
The Manage Enterprise Domains page appears. This page lists the enterprise domains in the identity management realm.
-
Select the enterprise domain that contains the database. Click Configure.
The Configure Domain page appears.
-
Click the Enterprise Roles tab.
-
Click Create.
The Create Enterprise Role page appears.
-
Enter hr_access in the Name field.
-
Click Add to add the database global role to the enterprise role.
The Search and Select Database Global Roles window is displayed.
-
Select the
hr_access
global role in your database. Click Select.Note:
You will be required to log in to the database before you can select the global role.
-
Click the Grantees tab. Click Add.
The Select Users or Groups window appears.
-
Select user Nina. Click Select.
-
Click Continue in the Create Enterprise Role page.
-
Click OK in the Configure Domain page.
The enterprise user, Nina can now access the hr.employees
table in the database. The following SQL*Plus statements illustrate this:
SQL> CONNECT Nina Enter password: Connected. SQL> SELECT employee_id FROM hr.employees; EMPLOYEE_ID ----------- 100 101 102 ... ... 107 rows selected.
The enterprise user, Joe cannot access the hr.employees
table, as he does not have the enterprise role assigned to him.
SQL> CONNECT joe Enter password: Connected. SQL> SELECT employee_id FROM hr.employees; SELECT employee_id FROM hr.employees ERROR at line 1: ORA-00942: table or view does not exist
Using Proxy Permissions
Proxy permissions are created at the enterprise domain level. Proxy permissions allow an enterprise user to proxy a local database user, which means that the enterprise user can log in to the database as the local database user. You can grant proxy permissions to individual enterprise users or groups. Proxy permissions are especially useful for middle-tier applications that operate across multiple databases as enterprise users.
Example 2-5 illustrates the use of proxy permissions. The enterprise user, joe
is a sales manager and needs to log in to enterprise databases as the target database user, SH
. The SH
user owns the sample SH
schema that contains Sales History related tables.
Example 2-5 Using Proxy Permissions
The first step in allowing enterprise user proxy is to ALTER
the target database user to allow CONNECT
through enterprise users. The following SQL
statements unlock the SH
database account, set a password for it, and ALTER
the account to allow enterprise user proxy:
SQL> CONNECT SYSTEM Enter password: Connected. SQL> ALTER USER SH IDENTIFIED BY hrd2guess ACCOUNT UNLOCK; User altered. SQL> ALTER USER SH GRANT CONNECT THROUGH ENTERPRISE USERS; User altered.
Next, use Enterprise Manager to configure the proxy permission. This allows the enterprise user joe
to connect as the local database user, SH
.
To configure the proxy permission for enterprise user, joe
:
-
Log in to Enterprise Manager Cloud Control, as an administrative user.
-
To navigate to your database, select Databases from the Targets menu.
-
Click the database name in the list that appears. The database page appears.
-
Under the Administration menu, select Security, Enterprise User Security. The Oracle Internet Directory Login page appears.
-
Enter the distinguished name (DN) of a directory user who can administer enterprise users in the User field. Enter the user password in the Password field. Click Login.
The Enterprise User Security page appears.
-
Click Manage Enterprise Domains.
The Manage Enterprise Domains page appears. This page lists the enterprise domains in the identity management realm.
-
Select the enterprise domain that you wish to configure. Click Configure.
The Configure Domain page appears.
-
Click the Proxy Permissions tab.
-
Click Create to create a new proxy permission.
The Create Proxy Permission page appears.
-
Enter SH_Proxy, as the name of the proxy permission, in the Name field.
-
Ensure that the Target DB Users tab is selected. Click Add.
The Search and Select window appears.
-
Log in to the database that contains the
SH
user. A list of all database users that have been altered to allow enterprise user proxy is displayed. -
Select the SH user. Click Select.
The
SH
user is added under Target DB Users in the Create Proxy Permission page. -
Click the Grantees tab.
-
Click Add.
The Select Users or Groups window appears.
-
Select
cn=users,dc=us,dc=oracle,dc=com
under Search Base. SelectUser
under View. Click Go.A list of users under the subtree,
cn=users,dc=us,dc=oracle,dc=com
is displayed. -
Select
cn=joe,cn=users,dc=us,dc=oracle,dc=com
. Click Select.The user
joe
is added under Grantees in the Create Proxy Permission page. -
Click Continue in the Create Proxy Permission page.
The proxy permission,
SH_Proxy
is added in the Configure Domain page. -
Click OK.
The enterprise user, joe
can now log in as the local database user SH
. The following SQL
statements illustrate this:
SQL> REMARK Joe uses his own password to connect as the local database user, SH. SQL> CONNECT joe[SH] Enter password: Connected. SQL> SELECT * FROM SH.sales WHERE cust_id=4; PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD ---------- ---------- --------- ---------- ---------- ------------- ----------- 37 4 31-MAY-00 3 999 1 60.43 39 4 31-MAY-00 3 999 1 38.45 40 4 31-MAY-00 3 999 1 48.1 ... ... 72 rows selected.
Using Pluggable Databases
You can use Enterprise User Security with Pluggable Databases (PDBs), introduced in Oracle Database 12c Release 1 (12.1). Each PDB has its own Enterprise User Security metadata, such as global users, global roles, and so on. Each PDB should have its own identity in the directory. A PDB is like any regular database registered with the directory, except for the following restrictions:
-
Client-side SSL authentication uses the Container Database (CDB)-wide wallet configured for the listener. The PDB-specific wallet is used for database-to-directory authentication.
-
If the client-to-database authentication uses
SSL
, and the database-to-directory authentication also usesSSL
, then two wallets need to be configured for the database with certificates. The first wallet is the CDB-wide wallet and the second wallet is the PDB-specific wallet. -
Current user database link is not supported in the CDB environment.
Note:
LDAP_DIRECTORY_ACCESS
parameter is the same for all PDBs. Even if only a subset of PDBs register with the directory, each PDB sees the LDAP_DIRECTORY_ACCESS
value as if the PDB is registered.
Wallet Location for Pluggable Databases
For pluggable databases, when a PDB is registered with the directory, the Database Configuration Assistant (DBCA) creates the wallet at the following location:
-
If the
ORACLE_BASE
environment variable is set:ORACLE_BASE/admin/db_unique_name/pdb_GUID/wallet
-
If
ORACLE_BASE
is not set:ORACLE_HOME/admin/db_unique_name/pdb_GUID/wallet
The GUID of the PDB is used because the PDB name can change, but the GUID does not change. So, the PDB wallet location is still valid even if the PDB name changes.
Note:
On the Microsoft Windows x64 platform, without specifying the WALLET_LOCATION
parameter in the listener.ora
file and server side sqlnet.ora
file, the server does not pick up the wallets from the default system location, which is %USERPROFILE%
\ORACLE\WALLETS
. Hence, when you try to login using an SSL connection, the login fails with the following error: ORA-28864: SSL connection closed gracefully
. There is no workaround to this known issue.
Default Database DN Format
When a PDB is registered with the directory using DBCA, the default PDB Distinguished Name (DN) is generated in the following format:
cn=PDB_NAME.DB_UNIQUE_NAME,cn=oraclecontext,realm
You can change the default cn
(PDB_NAME.DB_UNIQUE_NAME) to a custom value in the DBCA registration screen. It cannot be altered after registration.
Plugging and Unplugging PDBs
You can plug existing registered databases into a CDB. You do not need to register the PDB again, as long as you perform the following steps:
-
Pick the wallet files from the source location and put them in the new default wallet location for the PDB.
-
Set the
LDAP_DIRECTORY_ACCESS
parameter to the desired value in the CDB.
Similarly, when unplugging an existing PDB that is registered with the directory, you do not need to re-register the database. You need to copy the wallet to the new default location after unplug. The default location should be:
-
If the
ORACLE_BASE
environment variable is set:ORACLE_BASE/admin/db_unique_name/wallet
-
If
ORACLE_BASE
is not set:ORACLE_HOME/admin/db_unique_name/wallet