I’ll use the term DB-user, in this essay, as short-hand for what the Oracle RDBMS Sys.DBA_Users view lists. And I’ll define these subclasses:
—Power-DB-user: a DB-user that has at least one Any privilege (either directly or via a role)
—DBA-DB-user: a DB-user that has (at least) the DBA role
—Ordinary-DB-user: a DB-user that has not a single Any privilege (neither directly nor via a role)
—Connect-DB-User: a DB-user that starts with just Create Session, that will never own objects, that doesn’t have a partner password-protected role (see below), and that (following application back-end installation) will have only the Execute privilege on some or all of the PL/SQL subprograms that jointly define the database’s hard shell API.
To save breath, I’ll add these definitions:
—DBA: a person who knows the password for a DBA-DB-user
—App-Admin: a person who knows the password for an Ordinary-DB-user and (see below) for its partner password protected role—but who doesn’t know the password for a single DBA-DB-user.
Experts agree that a nefarious person, who knows the password for a Power-DB-user, can find a way to elevate its privileges to turn it into a DBA-DB-user—in other words, to turn her/himself into a DBA. They agree, too, that such a nefarious App-Admin cannot find a way to turn an Ordinary-DB-user into a DBA-DB-user.
This leads to a clear recommendation:
—never allow the direct grant of even a single Any privilege to a DB-user
—rather, simply meet the perceived need by granting the DBA role to that DB-user.
—limit the group of DBAs to a tiny few, each of whom has been scrupulously screened.
This thinking determines everything I now say.
The Development Shop presents a request to the DBAs:
(1) To create a set of Ordinary-DB-users, each with its specified list of explicit non-Any privileges. (I prefer to avoid Oracle-maintained roles to make the analysis simpler). For ordinary OLTP applications, just Create Session is sufficient for those that will own only code; and this together with quotas on appropriate tablespaces is sufficient for those that will own quota-consuming objects like tables and indexes.
(2) To create a set of password-protected roles in one-to-one correspondence with the Ordinary-DB-users, each with its specified list of explicit non-Any privileges (like Create Table, Create Procedure, and the like). This may include some object privileges with grant option like Execute on the Sys.DBMS_Lock package.
(3) To create a set of empty roles (i.e. roles to which no privileges or roles are yet granted), to be used as CBAC roles. Then to grant these roles with delegate option to those Ordinary-DB-users that will own singleton packages that define the database's API.
(4) To tell the the team of App-Admins their passwords. This can be fine-grained: for example, App-Admin-1 knows only the passwords for Ordinary-DB-user-1 and its partner password protected role.
Thereafter the App-Admins can run the scripts that the Development Shop has provided both that do the initial fresh install and that later patch it and upgrade it. Each script is designed to run as a single designated Ordinary-DB-user and, by construction, will be able to create, modify, and drop only objects that this Ordinary-DB-user owns and to grant object privileges on these to other DB-users and (by intention) to CBAC roles. Each script is run by an App-Admin who connects as the given Ordinary-DB-user and then enables its partner password protected role.
Within this regime, every single PL/SQL unit will be invokers rights. And each of the set of jacket PL/SQL singleton packages that implement the API—and only these—will be granted its partner CBAC role to which the minimum object privileges have been granted that allow it to fulfill its purpose.
Critically, engineers who install client-side code will know the passwords only for Connect-DB-Users.
Notice that a deliberately designed consequence of this scheme is that, at run time, the invokers rights code executes with only object privileges—i.e. not a single system privilege.