Development with a user except IFSAPP

Is there any specific reason why you are not using IFSAPP to do your developments?

Commonly, irrespective of the instance being on premise or on the cloud, customers have access to IFSAPP in the development instance where all the developments are done.  Once the development is completed along with unit testing, the work can then be deployed to the Test instance for user acceptance testing, and finally the work will be deployed in the Production instance once it has been signed off.  The deployment of the work in other instances can be done without having access to IFSAPP.

For development the Appowner user should be used. In Test and Prod I think it is best practice to use another ID for appowner.

As my colleagues have mentioned above, purely from a development perspective, you should always use IFSAPP to develop.

Any analysis or investigative work, you don’t necessarily need to use IFSAPP, it depends on the nature of the work I suppose.

For any testing of development work, you should never use IFSAPP to test, it should always be an end user account instead.

If you have a session open in a tool like SQL Developer, you can make queries easier to write with shorthand:

ALTER SESSION SET current_schema = IFSAPP;

This doesn’t elevate privileges; it just changes which schema gets the lookups for unqualified object names.

Another method you can use is proxy authentication. This has the affect of setting up another password to the same account.

ALTER USER anotheruser IDENTIFIED BY ░░░░ ACCOUNT UNLOCK;
ALTER USER ifsapp GRANT CONNECT THROUGH anotheruser;

sqlplus anotheruser[ifsapp]/░░░░@tns_name
sqlplus anotheruser[ifsapp]/░░░░@hostname:1521/service_name

By the way, your application owner is probably a Foundation user, and the IFSSYS user has proxy authentication access to every Foundation user account. This means your IFSSYS user can use proxy authentication to log in as your application owner, so you should treat that password with at least as much care as your application owner password.

sqlplus ifssys[ifsapp]/ifssys_password@...

The database grant must include WITH GRANT OPTION.

GRANT SELECT ON ifsinfo.test_view TO x_user WITH GRANT OPTION;

Alternatively, if you really trust these users, you can allow them to grant everything.

GRANT GRANT ANY PRIVILEGE TO it_superusers;

As a word of caution, this gives as much power as the DBA permission set because it even allows the user to grant DBA to themselves.