Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a legacy application and we have full access to source code and platform running it, so virtually any change can be implemented if required.

The application is a Java Swing heavy-weight GUI (desktop) accessing directly the database through JDBC in a 2-tier fashion. At application start-up, the user enters his/her credentials consisting on user&password. The current security schema is:

  • Oracle Database 10g Enterprise (migrating to 11g) with Oracle Internet Directory 10g (OID).
  • Oracle uses Enterprise Users so when connecting to DB, user provided in connection comes from GUI and Oracle validates against OID.
  • DB permissions are managed through Enterprise Roles. Each ER defined in DB maps a Group defined in OID, so user DB access to objects (tables, procedures...) are controlled by the Groups he/she belongs in OID.
  • Several GUI users can create others users, so create they in OID, assigning to the corresponding OID Groups in order to grant DB permissions. This is done using special Oracle permissions in OID to this "super-user". This is done from GUI using standard LDAP API, not OID API. The GUI user connects to OID with its credentials and performs LDAP operations.

The idea is to replace OID with Microsoft Active Directory, so the database would validate user against AD. DB permissions defined in ER would be retrieved also from AD, based on the Groups the users belongs to.

Assuming Oracle DB 11g and MS Windows Server 2008 (or upper versions if necessary), is that possible?

Restrictions/notes:

  • Oracle is installed on Linux, RH typically.
  • Oracle Virtual Directory can not be used nor any Identity Management Oracle product, just Oracle DB Enterprise.
  • GUI application executes on Windows workstations that are already in the Windows domain with the AD, son can use their OS credentials instead custom users used by GUI, but this must be done in Java (any version supported).
  • SSL, Certificates are not the preferred way, as they required to be provisioned.
  • Kerberos with MS KDC is also not the preferred way.
  • The new security schema with AD could lead to a less secure environment, but this is acceptable.
  • Prefer not to add third-party products to the security schema.
  • Oracle DB - MS AD integration should be supported by Oracle and Microsoft.

We need some advice from people with previous experience in similar installations about if it is possible the Oracle - AD solution. Some guidance and steps are welcome!

share|improve this question
    
You can use Windows authentication, but I think this is an optional extra - called 'advanced security' IIRC. –  ConcernedOfTunbridgeWells Apr 26 '12 at 8:06
    
ConcernedOfTunbridgeWells Clients are Java on Windows and database runs on Linux, is that possible, can provide any tip? –  David Oliván Ubieto Apr 26 '12 at 9:19
    
I think you can use windows authentication with Oracle on Unix, but a little google-fu suggests there might be security holes with that. Maybe you can read some stuff from the security context and at least default the username in the login panel. –  ConcernedOfTunbridgeWells Apr 26 '12 at 10:32
    
Security level achieved with this new solution, AD instead of OID is not a problem, I mean, a less security schema with AD is acceptable if it is not incurs in a poor security schema. –  David Oliván Ubieto Apr 26 '12 at 11:11
    
Why don't you want to use Kerberos? This is the only reasonable way to perform authentication with the Active Directory. Oracle 10/11 supports this with ´identified externally` and the UPN. The best thing about that you can use credential delegation to perform auth against the DB from multiple servers. –  Michael-O May 9 '12 at 19:27
show 1 more comment

1 Answer 1

The application I maintain does something similar that is database OS independent for around 100 users but in a much simpler fashion. Users are authenticated by the application and authorized from the database. Here are the details:

  • a table for users including their email and enabled/disabled status
  • a table of all AD groups that have a specific prefix to indicate that they are application groups (eg: APP_ADMINS, APP_USERS)
  • a table with group/user links

The next part is synchronizing the information. In our organization if new users are added or permissions for existing users are changed IT does the job in the morning. The application typically has a window of low usage overnight (ie: not 24 hours a day/usage).

On the application web server we have a windows service which syncs the groups and users. On the database at night I run a scheduled job which truncates the group-user links and recreates them by a direct query to Active Directory using DBMS_LDAP.

For your purposes you would want to write a more sophisticated package that has a database package running three LDAP queries to synchronize groups, users and group users instead of my truncate solution.

I found DBMS_LDAP to be powerful but poorly documented. If the IT department should happen to change the OU that users are in you don't get a lot of useful information from the Oracle end.

Tim's site was very useful with this article and here has the code I reused.

Edit: There are two parts to using an application:

  • who are you? (authentication) if you do not supply a name and password listed in Active Directory you are not allowed past the log in page.
  • what are you allowed to do? (authorization) Here is where the granularity of permissions has a wide range. We have limited ourselves to a basic set of operations on a portion of a unit of work: Read, Update, Assign other users, Unassign other users. Our permissions are not so granular so they can be worded as "User X has select on this table". They can be better said as "User X can update this portion of the file if they are assigned".

In part this is driven by the application. It creates a pooled connection to the database as required. Users log onto the application but connect to the database as a shared user who has all the permissions required.

To go into a more detailed example: User X logs in. They have a username and password that matches what is in Active Directory. After authentication they are taken to the main page. During that time the application queries the database for what permissions this user has and caches them in memory. The main page loads each sub work area. Each area asks the cached permissions: - "Do you have authority to see this area?". If so show the work area. - " Do you have permission to edit this area?" If so show an edit/create link.

Edit2: The original poster comments that

We are trying to replicate the user permissions onto application to DB permissions onto database objects.

This sounds like a square peg/round hole problem to me. Your business logic may be defined as User X needs select on this table but if you ask the user they are working with the application. They need to be able to work with whatever the application is displaying. It is common to group tables and views and custom filters to show what the user needs in the application. This is why some applications find it easier to enforce authorization at the application level. What is it about your application that requires database permissions to be replicated in Active Directory?

share|improve this answer
    
Thanks for the example, but it's not clear for me 1) The database connection is created using the app user and DB validates this user against AD? 2) Database permissions of the user used for the connection, are retrieved from the AD by the DB? i.e. If user A has "SELECT" grant over object B, this is reflected in AD by using Enterprise roles? (An ER is mapped by configuring DB to a group in LDAP/OID) That is, how the users get database permissions over objects using AD? –  David Oliván Ubieto May 3 '12 at 21:50
    
"Users log onto the application but connect to the database as a shared user who has all the permissions required." This is my central question. We are trying to replicate the user permissions onto application to DB permissions onto database objects. Each area in an application requires some permissions on DB, so if the user has permission to access an area in the app, must have the permissions for the required DB objects for this area. In Oracle we use ER mapped to LDAP groups so user inherits DB permissions through LDAP groups. Shared database user is not the desired way for us, if possible. –  David Oliván Ubieto May 4 '12 at 5:41
    
The idea is to enforce same user restrictions on the application to the database objects that supports that part/module of the application. That is, user X has access to module A of the app. Then access to all DB objects required by module A are granted to that users, but rest of DB objects are not granted, in order to avoid an user that can not access a module of the app can access the DB objects and information (tables) that supports its. If user X can access tariffs but not payments, the payments table must be not accessible to that user to prevent unauthorized modification. –  David Oliván Ubieto May 9 '12 at 20:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.