Transfering authentication from API to Oracle Database per individual user id and password? Is this possible?

Question

I am a little ignorant to this area of API/Database development so please be kind in your answers; I am just starting to learn this aspect.

I am creating/ maintaining APIs that use Oracle Databases for their data sources. In past projects that I worked on, all of the authorization was gated in the API, and it only governed what access you had to the given API request endpoints, and in turn the stored procedures that read, wrote to, updated or deleted records.

For now we are retrieving a user name, based on a call to the API that brings back "userSettings" like this database id...which is not good enough security wise I guess?

Our CIO thinks we should provide a mechanism to pass that user's authorization to the Oracle database? In the past we had some sort of token (or so I am told) passed back to the desktop application, based on the user name and password, forwarded on to the database from the desktop app. I know, it is an old application. In the older desktop application database behavior was governed in part by a user id, and so if user jim called a stored procedure called getEmployees he might get 300 records back but if user john accessed the same stored procedure, he might only get 15 records back. john had permission to access 15 records based on his authorization rules (grants I guess)...but jim has a different set of grants or permissions, and so he gets 300 records.

Previously in other projects I have worked on, the authorization was solely controlled by the security mechanism in the API (OAuth2, Bearer Token, etc.) and this worked fine but in this web application we need a finer grained, secure way of causing control flow changes based on database authorization or permissions (grants maybe?) of an individual user. All I am asking is where in the heck do I start looking into this?