Using PDB Lockdown Profile to disable specific SQL statement

Antes de criarmos o Lockdown Profile, vamos consultar o valor de um parâmetro e alterá-lo utilizando o comando ALTER SYSTEM:

[oracle@quiasma ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Apr 16 03:54:17 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
 
Session altered.
 
SQL> SHO PARAMETER OPTIMIZER_DYNAMIC_SAMPLING
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
SQL> ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING=3;
 
System altered.
 
SQL> SHO PARAMETER OPTIMIZER_DYNAMIC_SAMPLING
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     3
SQL>

Consultando o atual Lockdown Profile que temos no ambiente:

SQL> conn / as sysdba
Connected.
SQL> col profile_name format a30
SQL> SELECT PROFILE_NAME FROM DBA_LOCKDOWN_PROFILES;
 
PROFILE_NAME
------------------------------
PRIVATE_DBAAS
PROFILE_DO_BRUNO
PUBLIC_DBAAS
SAAS
 
SQL> col rule format a25
SQL> col clause format a10
SQL> SELECT PROFILE_NAME, RULE, CLAUSE, STATUS FROM DBA_LOCKDOWN_PROFILES WHERE PROFILE_NAME='PROFILE_DO_BRUNO';
 
PROFILE_NAME                   RULE                      CLAUSE     STATUS
------------------------------ ------------------------- ---------- -------
PROFILE_DO_BRUNO               PARTITIONING                         DISABLE

Criando a regra para não permitir a execução de comandos ALTER SYSTEM no PDB HIPOFISE1, exceto para realizar KILL SESSION:

SQL> ALTER LOCKDOWN PROFILE PROFILE_DO_BRUNO DISABLE STATEMENT= ('ALTER SYSTEM') CLAUSE ALL EXCEPT = ('KILL SESSION');
 
Lockdown Profile altered.
 
SQL> col rule format a25
SQL> col clause format a10
SQL> SELECT PROFILE_NAME, RULE, CLAUSE, STATUS FROM DBA_LOCKDOWN_PROFILES WHERE PROFILE_NAME='PROFILE_DO_BRUNO';
 
PROFILE_NAME                   RULE                      CLAUSE     STATUS
------------------------------ ------------------------- ---------- -------
PROFILE_DO_BRUNO               PARTITIONING                         DISABLE
PROFILE_DO_BRUNO               ALTER SYSTEM                         DISABLE
PROFILE_DO_BRUNO               ALTER SYSTEM              KILL SESSI ENABLE
                                                         ON

Validando a regra em ação:

SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
 
Session altered.
 
SQL> ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING=4;
ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING=4
*
ERROR at line 1:
ORA-01031: insufficient privileges

Abri uma outra conexão com o PDB:

[oracle@quiasma ~]$ sqlplus SYSTEM/oracle@HIPOFISE1
 
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Apr 16 04:03:03 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
Last Successful login time: Thu Apr 15 2021 05:51:34 -03:00
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL> SELECT * FROM DUAL;
 
D
-
X

Identificando seu SID e SERIAL# e realizando o KILL com sucesso:

SQL> SELECT SID,SERIAL#,STATUS FROM V$SESSION WHERE USERNAME='SYSTEM';
 
       SID    SERIAL# STATUS
---------- ---------- --------
       129      15313 INACTIVE
 
SQL> ALTER SYSTEM KILL SESSION '129,15313' IMMEDIATE;
 
System altered.

Percebemos que a sessão foi derrubada efetivamente:

SQL> /
SELECT * FROM DUAL
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3181
Session ID: 129 Serial number: 15313

Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.

Leave a Comment Cancel Reply