oracle pdb lockdown简单测试

SQL> create lockdown profile lockdown_test;

Lockdown Profile created.
SQL> alter lockdown profile lockdown_test disable statement =('alter system');

Lockdown Profile altered.

SQL> alter lockdown profile lockdown_test enable statement=('alter system') clause=('set');

Lockdown Profile altered.

alter system set pdb_lockdown='LOCKDOWN_TEST' scope=both sid='*';
System altered.

SQL> connect sys/cdb3@cdb3pdb10001 as sysdba
Connected.
SQL> alter system flush buffer_pool default;
alter system flush buffer_pool default
*
ERROR at line 1:
ORA-01031: insufficient privileges
Help: https://docs.oracle.com/error-help/db/ora-01031/
SQL> alter system set shared_pool_size=2g scope=spfile sid='*';

System altered.

disable这个feature之后，local user就不能在common user里面创建表之类的了
SQL> alter lockdown profile lockdown_test disable feature=('local_user_common_schema_access');

Lockdown Profile altered.

SQL> connect u33/cdb3@cdb3pdb10001
Connected.

SQL> create table c##u1.t1(a varchar2(20));
create table c##u1.t1(a varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges
Help: https://docs.oracle.com/error-help/db/ora-01031/
SQL> alter lockdown profile lockdown_test enable feature=('local_user_common_schema_access');

Lockdown Profile altered.
enable这个feature之后，就又可以在common user里面创建表了
SQL> create table c##u1.t1(a varchar2(20));

Table created.
SQL> alter lockdown profile lockdown_test disable feature=('awr_access');

Lockdown Profile altered.
disable feature awr_access之后，在pdb里面就不能创建snapshot了

SQL> exec dbms_workload_repository.create_snapshot;
BEGIN dbms_workload_repository.create_snapshot; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 112
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 146
ORA-06512: at line 1
Help: https://docs.oracle.com/error-help/db/ora-01031/
disable common user 直接登录pdb后，即使是sys用户也无法登录pdb，只能采用先登录cdb然后alter session set container的方式登录pdb
SQL> alter lockdown profile lockdown_test disable feature=('common_user_connect');

Lockdown Profile altered.
./sqlplus "sys/cdb3@cdb3pdb10001 as sysdba"

SQL*Plus: Release 23.0.0.0.0 - Development on Thu May 11 19:49:00 2023
Version 23.1.0.0.0

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

ERROR:
ORA-01017: invalid credential or not authorized; logon denied
Help: https://docs.oracle.com/error-help/db/ora-01017/

SQL> alter lockdown profile lockdown_test disable statement=('alter system') clause=('set') option=('cpu_count') minvalue=8;

Lockdown Profile altered.
在用lockdown限制不能将cpu count设置为8以下之后，再执行将cpu count设置为7就会报错
SQL> alter system set cpu_count=7 scope=spfile sid='*';
alter system set cpu_count=7 scope=spfile sid='*'
*
ERROR at line 1:
ORA-01031: insufficient privileges
Help: https://docs.oracle.com/error-help/db/ora-01031/