Oracle proxy users - any way of stopping them from changing client user password?-CodePudding

I'm currently in the process of starting a project to improve our Oracle database security by blocking developers from having direct access to application schemas, and having them connect through proxy ids instead.

I have one major concern though - can the proxy users, once connected, change the password on the application schema they've logged onto?

I don't want a situation where my developers, either accidentally or otherwise, manage to run ALTER USER and change the password to something other than what is specified by myself.

I've done some tests and the proxy user can execute the

'ALTER USER identified by "newpassword" REPLACE "oldpassword";'

statement. I appreciate this requires the current application password which they shouldn't know, but 'shouldn't' is not the approach I wish to build my security model on.

Any advice on how to disable the ALTER USER functionality on a proxy connection, or some other similar solution would be much appreciated.

Many thanks

Sean

CodePudding user response：

You've always got the ability to change your own password. Otherwise you could completely lock yourself out of the system!

When you connect via proxy, you effectively are the application schema. So using a proxy you can change the application user's password.

If you're on 18c or higher there is a way to prevent this:

Schema only accounts.

These have no password. You can't connect to these directly; the only way is via proxy users. Even better for your needs - these proxy connections can't add a password back!

--drop user app_s cascade;
--drop user prxy_u cascade;
grant create session to app_s identified by abcd1234#;
grant create session to prxy_u identified by abcd1234#;

alter user app_s 
  grant connect through prxy_u;

conn prxy_u[app_s]/abcd1234#

select user from dual;
/*
USER                                                                                                                    
-----
APP_S
*/
alter user app_s
  identified by "9876zyxw#"
  replace "abcd1234#";
/*
User APP_S altered.          
*/
alter user app_s
  no authentication;
/*
User APP_S altered.          
*/
  
alter user app_s
  identified by "qwer5678#";
/*
ORA-01031: insufficient privileges
*/
alter user app_s
  identified by "qwer5678#"
  replace "9876zyxw#";
/*
ORA-01031: insufficient privileges
*/  
conn app_s/"qwer5678#"
/*
ORA-01017: invalid username/password; logon denied
*/

Note this only works if you've revoked alter user privileges from the application schema. If you've granted it, proxy users can still change the password without the replace clause:

grant alter user 
  to app_s;
  
conn prxy_u[app_s]/"abcd1234#";

alter user app_s
  identified by "qwer5678#";
  
conn app_s/"qwer5678#"

select user from dual;
/*
USER                                                                                                                    
-----
APP_S
*/