OraFAQ Forum: Security » NO AUTHENTICATION and PROXY ONLY CONNECT

Home » RDBMS Server » Security » NO AUTHENTICATION and PROXY ONLY CONNECT (db19.x)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NO AUTHENTICATION and PROXY ONLY CONNECT [message #683054]

Mon, 30 November 2020 08:33

John Watson
Messages: 8707
Registered: January 2010
Location: Global Village

Senior Member

Release 18 introduced schema only users without any sort of password, eg,

create user abc no authentication;

very useful: they can store all your objects, but you cannot connect to them directly, only through a proxy. But 12.1 had already introduced proxy only accounts, eg,

alter user scott proxy only connect;

Either technique must be better than the old way, where you create all your objects in accounts that you lock and expire the password.

But why would you use one rather than the other? In terms of functionality and security they seem identical to me.

Thank you for any insight.

Report message to a moderator

Re: NO AUTHENTICATION and PROXY ONLY CONNECT [message #683339 is a reply to message #683054]

Thu, 31 December 2020 03:35

Michel Cadot
Messages: 68171
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I made some research on this point.

First create 2 users and check the differences in DBA_USERS (or SYS.USER$):

SQL> create user test1 no authentication;

User created.

SQL> create user test2 proxy only connect;

User created.

SQL> exec print_table(q'[select * from dba_users where username in ('TEST1','TEST2') order by username]')
USERNAME                      : TEST1
USER_ID                       : 170
PASSWORD                      :
ACCOUNT_STATUS                : OPEN
LOCK_DATE                     :
EXPIRY_DATE                   :
DEFAULT_TABLESPACE            : TS_D01
TEMPORARY_TABLESPACE          : TEMP
LOCAL_TEMP_TABLESPACE         : TEMP
CREATED                       : 31/12/2020 09:36:43
PROFILE                       : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP   : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME                 :
PASSWORD_VERSIONS             :
EDITIONS_ENABLED              : N
AUTHENTICATION_TYPE           : NONE
PROXY_ONLY_CONNECT            : N
COMMON                        : NO
LAST_LOGIN                    :
ORACLE_MAINTAINED             : N
INHERITED                     : NO
DEFAULT_COLLATION             : USING_NLS_COMP
IMPLICIT                      : NO
ALL_SHARD                     : NO
PASSWORD_CHANGE_DATE          :
-----------------
USERNAME                      : TEST2
USER_ID                       : 171
PASSWORD                      :
ACCOUNT_STATUS                : OPEN
LOCK_DATE                     :
EXPIRY_DATE                   :
DEFAULT_TABLESPACE            : TS_D01
TEMPORARY_TABLESPACE          : TEMP
LOCAL_TEMP_TABLESPACE         : TEMP
CREATED                       : 31/12/2020 09:36:52
PROFILE                       : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP   : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME                 :
PASSWORD_VERSIONS             :
EDITIONS_ENABLED              : N
AUTHENTICATION_TYPE           : NONE
PROXY_ONLY_CONNECT            : Y
COMMON                        : NO
LAST_LOGIN                    :
ORACLE_MAINTAINED             : N
INHERITED                     : NO
DEFAULT_COLLATION             : USING_NLS_COMP
IMPLICIT                      : NO
ALL_SHARD                     : NO
PASSWORD_CHANGE_DATE          :
-----------------

PL/SQL procedure successfully completed.

Both have an "authentication type" of NONE and TEST2 has in addition a "proxy only connect" set to YES and this is only difference.
In SYS.USER$ you will see SPARE1 for TEST1 is set 65536 and 67584 for TEST2; 67584 = 65536 ("no authentication" bit) + 2048 ("proxy only" bit).

Now let's see if there are differences when connecting through a proxy (no direct connect possible by definition):

SQL> grant create session to test2;

Grant succeeded.

SQL> alter user test2 grant connect through michel;

User altered.

SQL> connect michel[test2]/michel@mikj3db1
Connected.
SQL> show user
USER is "TEST2"

TEST2 is able to connect through a proxy which is what was expected from its CREATE USER statement.
Now let's see TEST1:

SQL> grant create session to test1;

Grant succeeded.

SQL> alter user test1 grant connect through michel;

User altered.
[code][/code]
SQL> connect michel[test1]/michel@mikj3db1
Connected.
SQL> show user
USER is "TEST1"

TEST1 is also able to connect through a proxy which is not so strange: stating that it can't directly authenticate does not mean it can't connect through a proxy.

Now let's see what happen if we change our mind and allow the users to authenticate using a password:

SQL> alter user test1 identified by test1;

User altered.

SQL> alter user test2 identified by test2;

User altered.

SQL> connect test1/test1@mikj3db1
Connected.
SQL> show user
USER is "TEST1"

SQL> connect test2/test2@mikj3db1
This is from SEC_USER_UNAUTHORIZED_ACCESS_BANNER.

ERROR:
ORA-28058: login is allowed only through a proxy

Warning: You are no longer connected to ORACLE.
SQL> connect michel[test2]/michel@mikj3db1
Connected.
SQL> show user
USER is "TEST2"

So TEST1 is able to connect but not TEST2 as we had specified in its creation statement that it can only connect through a proxy something we can see with the last command.

To summarize, in my opinion, as long as both as no possible direct authentication there are no differences between the 2 accounts.
Then if we allow some authentication only the account which is not constrained by the "PROXY ONLY" option can directly connect.

Report message to a moderator

Re: NO AUTHENTICATION and PROXY ONLY CONNECT [message #683340 is a reply to message #683339]

Thu, 31 December 2020 05:23

John Watson
Messages: 8707
Registered: January 2010
Location: Global Village

Senior Member

A nice piece of reverse engineering on the spare1 usage. I did the check:

orclz>
orclz> create user test1 no authentication;

User created.

orclz> create user test2 proxy only connect;

User created.

orclz> select name,spare1 from user$ where name like 'TEST%';

NAME                                    SPARE1
------------------------------ ---------------
TEST1                                    65536
TEST2                                    67584

orclz> alter user test1 identified by abc;

User altered.

orclz> alter user test2 identified by abc;

User altered.

orclz> select name,spare1 from user$ where name like 'TEST%';

NAME                                    SPARE1
------------------------------ ---------------
TEST1                                        0
TEST2                                     2048

orclz> alter user test2 cancel proxy only connect;

User altered.

orclz> select name,spare1 from user$ where name like 'TEST%';

NAME                                    SPARE1
------------------------------ ---------------
TEST1                                        0
TEST2                                        0

orclz>

There really doesn't seem to be a lot of documentation about this. I'm beginning to suspect that PROXY ONLY may be a facility intended for use with JDBC connection pools. The docs docs talk a lot about persistent connections switching identity. I've never done that except for APEX, where ORDS does it all for you. Whereas NO AUTHENTICATION would be for client-server applications such as EBS, which are basically client-server Forms.

Thanks for the research.

Report message to a moderator

Previous Topic:	ACCESSIBLE BY clause on CREATE procedural object
Next Topic:	wrong path for waltet TDE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 25 07:58:19 CDT 2022