alter session enable restricted session

如何查看是否enable了restricted session

SQL> select logins from v$instance;

LOGINS
----------
ALLOWED

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED

alter system enable restricted session这个语句是针对instance级别的，所以如果是rac环境，需要在所有的instance上都设置

02:21:32 SQL> show parameter instance_name;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
instance_name			     string	 comp12
02:21:54 SQL> select instance_name,logins from gv$instance;

INSTANCE_NAME	 LOGINS
---------------- ----------
comp11		 ALLOWED
comp12		 RESTRICTED

查看谁具有restricted session权限(有的用户会通过role获得restricted session 权限)

SQL>  select grantee,privilege from dba_sys_privs where privilege like '%RESTRIC%';

GRANTEE 	     PRIVILEGE
-------------------- ------------------------------
SYS		     RESTRICTED SESSION
DBA		     RESTRICTED SESSION

没有restricted session权限的用户是无法登录的

SQL> create user c##u1 identified by u1;

User created.

SQL> grant connect,resource to c##u1;

Grant succeeded.

[crsusr@slcz01db03 bin]$ ./sqlplus "c##u1/u1"

SQL*Plus: Release 21.0.0.0.0 - Development on Sat Jul 4 09:40:24 2020
Version 21.1.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Enter user-name:

restricted session是可以在pdb级别设置的

pdba可以enable restricted session 而 pdbb不enable restricted session

20:42:33 SQL> show user;
USER is "SYS"
20:42:44 SQL> connect sys/vault1@vault1pdb88888 as sysdba
Connected.
20:43:10 SQL> alter system enable restricted session;

System altered.

Elapsed: 00:00:00.08
20:43:52 SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED

Elapsed: 00:00:00.01
20:44:45 SQL> alter session set container=vault1pdb10001;

Session altered.

Elapsed: 00:00:00.02
20:45:19 SQL> select logins from v$instance;

LOGINS
----------
ALLOWED

Elapsed: 00:00:00.01