RE: Real Application Security with centrally-managed users

At a few of my clients we were heavy RAS users. We wrote some extensive PL/SQL packages to implement and use RAS.

As Tim states for detecting CMUs you need an after login trigger in order to attach the incoming user into the RAS architecture and the session to a RAS session.  

Part of the extensive RAS PL/SQL packages included administrative cleanup of the RAS architecture based on choices with CMU.

At one client we had a daily package that pulled all AD users and inserted them into the RAS architecture so they are just ready. At another client we performed the RAS insertion into the RAS structure on login so only users who accessed the db had accounts.

There were other components to detect users were deprecated in AD and removed from the system.  

For some clients part of the architecture was the users had privs that were only updated daily whereas other clients wanted the privs checked and modified every time and end user executed a query against the database. Different than the way security works that you only get you privs on login, we were testing and updating privs on every query.    

Even in the PL/SQL packages we wrote we used a two package architecture to get past the RAS components requiring to run purely as sys.  

sys.ras_security_admin

with the procedures in the package protected by

ACCESSIBLE BY (PACKAGE sys.ras_security_admin, PACKAGE sys.ras_apps, PACKAGE sys.ras_security_util);  

sys.ras_apps

AUTHID CURRENT_USER   After login trigger calls sys.ras_apps which calls sys.ras_security_admin for base RAS admin task.        

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Tim Gorman Sent: Thursday, February 15, 2024 11:21 AM To: Timur Akhmadeev <timur.akhmadeev_at_gmail.com> Cc: oracle-l_at_freelists.org
Subject: Re: Real Application Security with centrally-managed users  

Timur,

The problem seems to be two different methods of authenticating users which do not appear to intersect.

With CMU, there is callback functionality from Oracle to AD to authenticate a database connection, but nothing for RAS to authorize because RAS wasn't used for the authentication. I agree that RAS should support AD users, but we can't find out how, so far. That is the crux of my inquiry.

As a workaround, our team built functionality into an AFTER LOGON trigger to unpack the authenticated CMU information from data-dictionary views to then attempt authentication via RAS as well, but the RAS packaged functions/procedures are AUTHID CURRENT_USER which are prohibited inside an AFTER LOGON trigger, which require AUTHID DEFINER. I can think of a further workaround to that workaround to address that obstacle, but I don't want to go further down the rabbit-hole of workarounds when it probably should work without any workarounds at all.

Does that make sense? Please let me what you think?

Thanks!

-Tim
 

On 2/15/2024 10:35 AM, Timur Akhmadeev wrote:

Hi Tim,  

I know a little bit of CMU and glanced at RAS documentation. My understanding is that those two have a few similarities.  

CMU is a way to make a transparent authentication and (optionally) authorization via roles mappings for end users from AD.

Depending on your requirements you may configure authorization (mapping of the AD roles to DB roles) with CMU, but it is not a requirement. You can use just the authentication part, and manage authorization manually.  

Now the RAS, from what I quickly read, provides a fine grained authorization for application users beyond simple roles.

Most of the docs mention creating apps users in the db via package xs_principal, and they also mention the users could be mapped to a directory server.

According to the docs <https://docs.oracle.com/en/database/oracle/oracle-database/21/dbfsg/XS_PRINCIPAL-package.html#GUID-AB88CD3F-89B0-4C25-A404-D5C8D7FEB1AD> the password for those external users can't be set via set_password which makes me think the RAS can work as an authentication mechanism in a similar to CMU way. Otherwise, they would have to store passwords in the DB.

So I would say if you're looking at RAS then possibly you don't even need CMU, as RAS should be able to support AD users. I haven't touched RAS and obviously may be wrong.  

HTH   On Wed, Feb 14, 2024 at 12:32 AM Tim Gorman <tim.evdbt_at_gmail.com <mailto:tim.evdbt_at_gmail.com> > wrote:

Friends and colleagues,

I'm working on a problem involving two somewhat obscure -- but vitally important -- pieces of functionality in Oracle19c...

• Real Application Security (RAS)
• Centrally-managed users (CMU)

In general, RAS is the successor to virtual private databases (VPDs), which was introduced way back in Oracle8i for fine-grained row-level security and column-level security. CMU is the management of database users by a centralized external authority such as Microsoft Active Directory, rather than an Oracle DBA using CREATE USER commands in each Oracle database.

There is copious documentation and support for either mechanism, but I am hard-pressed to find anything indicates that both can be used together.

We've already started down the road of devising a custom solution for integrating the two, but it is hitting difficulties, so I would like to find out if anyone on this list has any experience -- or knows of someone who has experience -- using both RAS and CMU together?

If anyone from the security or identity-management product groups at Oracle could offer any advice, it would be gratefully accepted!

Please let me know what you think?

Thanks!

-Tim
 

--

Regards
Timur Akhmadeev

--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 16 2024 - 15:30:32 CET