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?
@Chris Aldrich has given a good explanation. I will just add a few things here.
1) There is no concept of a "database user" in DB2. All authentication happens outside the database or instance, in the operating system. Also, there is no direct relationship between a user ID and a schema name, unlike in Oracle. In DB2 a schema is just a logical grouping of objects, it does not have any special security features. Any user can create any schema. For example, while logged in as mustaccio I run the statement create table foo (id int...), and that creates a schema MUSTACCIO (if it's not already there) and a table in it. As you see, the schema name resolution defaults to my authorization ID. However, I might as well run the statement create table alok.foo (id char(3)...), which in this case creates a schema ALOK and the table in it. mustaccio will be the owner of both tables.
2) Regarding the user ID mapping, I would probably say that the DAS owner dasusr1 and the fenced user db2fenc1 do not map to anything in an Oracle database. The instance owner db2inst1 maps to the oracle user ID. Whoever creates a database (might be db2inst1 or some other user authorized to do that by membership in the SYSADM group, for example) obtains DBADM and SECADM privileges in that database, which is somewhat similar to being system and/or sys (I'm not really sure what is the distinction between the two in an Oracle database). If you need a functional ID that owns database objects, you create appowner in the operating system, grant to it appropriate permissions, and connect to that user when creating objects. Similarly, you create appuser in the operating system, grant object access privileges to it, and let your application connect as that user.
3) Since there are no database users in DB2, you cannot drop a user. To delete objects in a particular schema you can use the ADMIN_DROP_SCHEMA() procedure.
Best Answer
This is such a common problem when the database was installed with user separation (grid + oracle user) and DBAs tend to overlook this.
When you use RAC or even just Oracle Restart (with or without ASM), you need to install Grid Infrastructure. Grid Infrastructure can be installed as a different user (typically grid).
When you have Grid Infrastructure, the proper way to handle listeners is through Grid Infrastructure. If Grid Infrastructure was installed with grid user, then the listener runs as grid user.
In Oracle architecture, by design, remote connections log in through the listener, and the database server process is forked by the listener. On Linux/UNIX platforms, the
oraclebinary is owned by oracle user, and it has the setuid bit enabled. grid and oracle users share a common group, and theoraclebinary can be executed by the members of this group.Given the above information, remote connections coming through the listener running as grid user can spawn processes whose UID and EUID is the same as the UID of oracle.
So far this is what usually everyone knows. The difference is however the RUID and the inherited privileges because of it.
On a machine with users as (this is the default configuration taken from an Exadata X5-2 compute node, so this is how Oracle officially deploys its configuration):
When you log in locally, all user IDs are 1001 (=oracle):
But when you log in through the listener:
Notice that the RUID is different, it is the UID of grid user.
For example, this can lead to a situation, where file generation from the database with
UTL_FILEis successful when executed in a scheduled job, but fails when executed from a remote session.With the above setup, server processes forked by the listener inherit the privileges of grid user. Scheduled jobs spawned by the database itself inherit the privileges of oracle user. If they do not have the same privileges, they will behave differently.