Oracle by Example brandingTesting the EUS, OUD 12c and Active Directory Integration

section 0Before You Begin

This tutorial provides a step-by-step example of how to test the integration of Oracle Unified Directory (OUD) 12c PS3, Microsoft Active Directory, and Oracle Enterprise User Security (EUS). This tutorial takes approximately 15 minutes to complete.

This is the seventh and final tutorial in the series Integrating Oracle Unified Directory Proxy Server 12c with Microsoft Active Directory and Oracle Enterprise User Security. Read them sequentially.

Background

This tutorial will demonstrate the different access levels or permissions granted to users based on their Microsoft Active Directory group membership. It will show that no users need to be created in the database because Active Directory users are mapped to a shared schema while accessing the database. A summary of the users and their group memberships defined in Active Directory are as follows:

UserID Groups Password
user.0 [no group] Welcome1
user.1 ora_connect, ora_resource Welcome1
user.2 ora_connect, ora_dba Welcome1

What Do You Need?

section 1Testing the TNS Alias

In this section you test that the TNS Alias myorcl is resolved successfully via OUD.

  1. Launch a terminal window as oracle on the database server and run the following commands:
    cd $ORACLE_HOME/bin
    ./tnsping myorcl
    If successful you should see the following:
    TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 10-NOV-2017 11:50:38 
    Copyright (c) 1997, 2014, Oracle.  All rights reserved. 

    Used parameter files: 
    /u01/app/oracle/product/12.1.0/db_1/network/admin/sqlnet.ora 

    Used LDAP adapter to resolve the alias 
    Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl.example.com)))
    OK (0 msec)

section 2Testing Database Access with No Privileges

In this section user.0 will try to connect to the database.

  1. Launch a terminal window as oracle on the database server and run the following commands:
    cd $ORACLE_HOME/bin
    ./sqlplus user.0/Welcome1@myorcl
    Although user.0 is a valid user within Active Directory, user.0 does not have a valid group membership and as a result, is prevented from accessing the database:
    ERROR:
ORA-01045: user GLOBAL_USER lacks CREATE SESSION privilege; logon denied

section 3Testing Database Access With DBA Privileges

In this section user.2 will try to connect to the database.

  1. Launch a terminal window as oracle on the database server and run the following commands:
    cd $ORACLE_HOME/bin
    ./sqlplus user.2/Welcome1@myorcl
    As user.2 belongs to the ora_connect group in Active Directory, the user can connect to the database. This is possible because the enterprise role EUS_CONNECT was granted to this group and linked to the global role GLOBAL_CONNECT which gives users privileges to create a session with the database.
  2. Run the following SQL statements:
    SQL> select * from session_roles;
    ..
    ROLE
-------------------------------------------------------------------------------- 
JAVA_ADMIN 
JAVA_DEPLOY 
OLAP_XS_ADMIN 
XS_RESOURCE 
OLAP_DBA 
27 rows selected.                           

    SQL> create view role_view AS select * from session_roles;
    View created.
    User.2 can run all the commands successfully as the user also belongs to the ora_dba group in Active Directory. This group has been granted the enterprise role EUS_DBA that is linked to the global role GLOBAL_DBA, which gives users DBA privileges.
  3. Run the following SQL statements:
    SQL> select sys_context('USERENV','AUTHENTICATED_IDENTITY') from dual;
    SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
    --------------------------------------------------------------------------------
    USER.2
    SQL> select sys_context('USERENV','ENTERPRISE_IDENTITY') from dual;
    SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY')
    --------------------------------------------------------------------------------
    cn=user.2,cn=Users,dc=example,dc=com
    These commands show the authenticated identity and enterprise identity.

section 4Testing Database Access With Controlled Privileges

In this section user.1 will try to connect to the database.

  1. Launch a terminal window as oracle on the database server and run the following commands:
    cd $ORACLE_HOME/bin
    ./sqlplus user.1/Welcome1@myorcl
    As user.1 belongs to the ora_connect group in Active Directory, then the user can connect to the database.
  2. Run the following SQL statements:
    SQL> select * from session_roles;                              
    ROLE
    --------------------------------------------------------------------------------
    GLOBAL_CONNECT
    CONNECT
    GLOBAL_RESOURCE
    RESOURCE

SQL> create view role_view AS select * from session_roles;
    create view role_view AS select * from session_roles
                *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    The first command runs successfully, but the second command fails. User.1 belongs to the ora_resource group in Active Directory. This group has been granted the enterprise role EUS_RESOURCE that is linked to the global role GLOBAL_RESOURCE. This grants users privileges to query data but not to create or delete objects in the database.
  3. Run the following SQL statements:
    SQL> select sys_context('USERENV','AUTHENTICATED_IDENTITY') from dual;
    SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
    --------------------------------------------------------------------------------
    USER.1
    SQL> select sys_context('USERENV','ENTERPRISE_IDENTITY') from dual;
    SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY')
    --------------------------------------------------------------------------------
    cn=user.1,cn=Users,dc=example,dc=com
    These commands show the authenticated identity and enterprise identity.


more informationWant to Learn More?