oracle18c新特性-用户NO AUTHENTICATION认证方式和Proxy user连接

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:oracle18c新特性-用户NO AUTHENTICATION认证方式和Proxy user连接

使用Oracle18.3,我们可以在没有密码的情况下创建用户。实际上出于安全原因,没有人可以直接连接到应用程序的架构是一个非常好的功能。一个很好方法是使用代理连接,实际上连接为t1用户但使用t2用户的密码,。下面我们就来研究测试一下。
创建T1用户,赋予create session,resource权限;

create user t1 identified by t1;
grant create session,resource to t1;

创建T2用户,只赋予create session权限;

create user t2 identified by t2;
grant create session to t2;

赋予允许proxy连接的权限.

alter user t1 grant connect through t2;

使用代理连接的方式如下,这样就不用输入t1用户的密码,就连上了t1用户

Usage:  ::= [][/][@]

SQL> connect t2[t1]/t2@ORCLPDB1
Connected.
SQL> select sys_context('USERENV','SESSION_USER') as session_user,sys_context('USERENV','SESSION_SCHEMA') as session_schema,sys_context('USERENV','PROXY_USER') as proxy from dual;

SESSION_USER      SESSION_SCHEMA      PROXY
----------------- ------------------- ------------
T1                T1                  T2

当我们把T1账号锁定一下,会发现使用代理的连接会被锁定。

SQL> alter user t1 account lock;
User altered.

SQL> connect t2[t1]/t2@ORCLPDB1
ERROR:
ORA-28000: The account is locked.

搞明白代理用户的连接方式,接下来我们来研究NO AUTHENTICATION认证。这里先把t1和t2用户删除。

以下是18c的新特性。使用NO AUTHENTICATION,可以创建一个没有密码的用户。也没有办法登陆这个用户。

NO AUTHENTICATION Clause
Use the NO AUTHENTICATION clause to create a schema that does not have a password and cannot be logged into. This is intended for schema only accounts and reduces maintenance by removing default passwords and any requirement to rotate the password.

SQL> create user t1 no authentication;
User created.

SQL> grant create session to t1; 
Grant succeeded

SQL> connect t1@ORCLPDB1
Enter password: 
ERROR:
ORA-01017: invalid username/password; logon denied

可以看到这里是没办法按登录的。需要输入密码。我们可以使用proxy连接试试看能不能登陆。

SQL> create user t2 identified by t2;
User created.

SQL> alter user t1 grant connect through t2;
User altered.

SQL> connect t2[t1]/t2@ORCLPDB1
Connected.

可以看到这里可以通过proxy用户进行连接了。即使t1用户没有密码也可以连接。当然这里有个问题,就是这么做有什么作用?我们的一个想法是,让t1用户里面有数据,然后t1用户是没有密码且不能登录的。然后我们通过代理t2用户对t1用户下的数据进行查询和其他操作。这样我们就影藏了数据库的用户。全程通过代理用户做所有的操作。我们来测试一下。
1.先删除t1和t2用户

drop user t1 cascade;
drop user t2 cascade;

2.创建t1,t2用户,t1使用no authentication认证方式。,同时赋权让t2成为proxy用户。

SQL> create user t1 no authentication;
User created.
 
SQL> grant create session , create table to t1;
Grant succeeded.

SQL> create user t2 identified by t2;
User created.

SQL> alter user t1 grant connect through t2;
User altered.

3.测试使用proxy方式连接。

SQL> connect t2[t1]/t2@ORCLPDB1
Connected.

4.直接登陆,需要输入密码,此时无法登陆

SQL> connect t1/t1@ORCLPDB1
ERROR:
ORA-01017: invalid username/password; logon denied

5.授权sysdba给用户t1,发现管理权限不能被授权给该用户,因为该用户使用的是no authentication认证方式。

SQL> connect sys/oracle@ORCLPDB1 as sysdba;
Connected.

SQL> grant sysdba to t1;
grant sysdba to t1
*
ERROR at line 1:
ORA-40366: Administrative privilege cannot be granted to this user.

6.把no authentication认证方式修改成密码认证。此时就可以授予sysdba权限了

SQL> alter user t1 identified by t1; 
User altered.
 
SQL> grant sysdba to t1;
Grant succeeded.

7.再次修改成no authentication认证方式,此时是不行的,不能把sysdba权限用户设置成no authentication认证方式,收回sysdba权限,就又能成功了。

SQL> alter user t1 no authentication;
alter user t1 no authentication
*
ERROR at line 1:
ORA-40367: An Administrative user cannot be altered to have no authentication
type.

SQL> revoke sysdba from t1;
Revoke succeeded.
 
SQL> alter user t1 no authentication;
User altered.

8.再次取消no authentication认证方式,在t1用户下创建表和插入数据.

SQL> connect sys/oracle@ORCLPDB1 as sysdba;
Connected.

SQL> alter user t1 identified by t1;

SQL> connect t1/t1@ORCLPDB1
Connected.

SQL> create table t1 (name varchar2(32));
Table created.
 
SQL> insert into t1 values('buddy');
1 row created.
 
SQL> commit;
Commit complete.

9.再次重新设置no authentication认证方式,通过proxy方式进行连接,可以查看表的数据和插入数据。此时虽然表属于t1用户,但是t1用户是没有密码和不能登录的,只能通过t2用户使用proxy连接方式登录操作。

SQL> connect sys/oracle@ORCLPDB1 as sysdba;
Connected.

SQL> alter user t1 no authentication;
User altered.

SQL> connect t2[t1]/t2@ORCLPDB1
Connected.

SQL> select * from t1;
NAME
--------------------------------
buddy

SQL> insert into t1 values('buddy99');
1 row created.

SQL> commit;
Commit complete.

SQL> select object_name, object_type, owner from all_objects where object_name ='T1';

OBJECT_NAME          OBJECT_TYPE             OWNER
-------------------- ----------------------- ------------------------------
T1                   TABLE                   T1

那么我们能不能使用审计来审计是什么用户干的呢,能不能看到代理用户干的?

SQL> connect sys/oracle@ORCLPDB1 as sysdba;
Connected.

SQL> create audit policy t1_user_audit_policy privileges create table when 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''T1''' evaluate per session container=current;
Audit policy created

SQL> audit policy t1_user_audit_policy whenever successful;
Audit succeeded.

SQL> connect t2[t1]/t2@ORCLPDB1
Connected.
SQL> create table a1 (name varchar2(30));
Table created.

查看审计unified_audit_trail表,可以看到DBPROXY_USERNAME字段不为空就是代理用户T2创建的。

SQL> connect sys/oracle@ORCLPDB1 as sysdba;
Connected.

SQL> select object_name,event_timestamp, dbusername, dbproxy_username from unified_audit_trail where action_name = 'CREATE TABLE';
OBJECT_NAME                    EVENT_TIMESTAMP                DBUSERNAME                     DBPROXY_USERNAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
A1                             23-OCT-18 10.21.33.073649 PM   T1                             T2

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569