You Asked

Is there a way to create a database user that is only allowed to connect via a proxy user? A logon trigger to check how a user connected would work but I was wondering if there was a user or profile setting that could be set so a logon trigger woulfdnt be needed.

Example:
USER_A should only be allowed to connect to the database this way: connect USER_B[USER_A]/passw0rd
USER_A can never connect to the database this way: connect USER_A/passw0rd

and Chris said...

I'm not aware of a way other than a trigger.

Locking the account or revoking create session both prevent proxy access too:

SQL> create user usera identified by usera;

User created.

SQL> create user userb identified by userb;

User created.

SQL> grant create session to userb;

Grant succeeded.

SQL> alter user usera grant connect through userb;

User altered.

SQL> conn userb[usera]/userb
ERROR:
ORA-01045: user USERA lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn chris/chris
Connected.
SQL> grant create session to usera;

Grant succeeded.

SQL> alter user usera account lock;

User altered.

SQL> conn userb[usera]/userb
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.

But you can do it in a logon trigger:

create or replace trigger stop_logon
after logon on database
begin
  if user = 'USERA' 
    and sys_context('USERENV','PROXY_USER') is null then
    raise_application_error(-20001, 'Connect via proxy only!');
  end if;
end;
/

SQL> conn usera/usera
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Connect via proxy only!
ORA-06512: at line 4


Warning: You are no longer connected to ORACLE.
SQL> conn userb[usera]/userb
Connected.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Another way to do it...

Pete, August 12, 2016 - 1:04 pm UTC

One method I have used successfully in the past (before user accounts could actually be locked), is to set the password hash of the protected account to something that can NEVER be matched up correctly by Oracle. The password hash value in USER$.PASSWORD or USER$.SPARE4 is always upper case, alpha-numeric. All we need to do is make the hash value lower case and include some special characters.

By setting the password hash directly using alter user [name] identified by values '[hash]' we can effectively lock out the account to direct access without actually locking it - preserving the ability to connect via proxy.

Example:

alter user usera identified by values 'no_login_allowed';

Now any attempt to connect directly to this user will always result in an ORA-01017 - invalid password error.

August 14, 2016 - 5:41 am UTC

Nice input.

Check this post (and the comments) for when it comes to 12c.

https://connormcdonald.wordpress.com/2014/08/09/12-1-0-2-security-grrr/

Why?

Pete, August 16, 2016 - 3:01 am UTC

Read the post you recommended. Why, why, why would Oracle make this impossible in 12c? It's one of the best ways to secure an account; locking disables too many other features and as the post points out, provides valuable information to a hacker.

August 16, 2016 - 2:25 pm UTC

Well "identified by values" is undocumented and therefore unsupported. So you shouldn't really have been using this anyway!

Chris

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Another way to do it...

Why?

More to Explore

PL/SQL demos

PL/SQL docs

Oracle Proxy User & database User Connections

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Another way to do it...

Why?

More to Explore

PL/SQL demos

PL/SQL docs