Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: August 08, 2019 - 7:34 am UTC

Last updated: June 08, 2021 - 3:24 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

Team,

Is it not possible to have multiple values in the lock down profile? Kindly advice.

c##sys@ORA12CR2> drop lockdown profile p1;

Lockdown Profile dropped.

c##sys@ORA12CR2> create lockdown profile p1;

Lockdown Profile created.

c##sys@ORA12CR2> alter lockdown profile p1 disable statement = ('ALTER SESSION') clause=('SET')
  2     option=('CURSOR_SHARING')
  3     value=('FORCE','SIMILAR');
alter lockdown profile p1 disable statement = ('ALTER SESSION') clause=('SET')
                                        *
ERROR at line 1:
ORA-65206: invalid value specified
ORA-00922: missing or invalid option


c##sys@ORA12CR2> alter lockdown profile p1 disable statement = ('ALTER SESSION') clause=('SET')
  2     option=('CURSOR_SHARING')
  3     value=('FORCE');

Lockdown Profile altered.

c##sys@ORA12CR2>

and Connor said...

See the answer to your previous question on profiles. It is a default value to be applied, not a filter.

https://asktom.oracle.com/pls/apex/asktom.search?tag=cursor-sharing-200205#9541576100346173492

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

common user creation

Rajeshwaran Jeyabal, June 04, 2021 - 1:21 pm UTC

Team,

Is it possible to restrict common user creation in CDB using lockdown profile? tried this, but doesn't work as expected, kindly advice.

sys@XE> show con_name

CON_NAME
------------------------------
CDB$ROOT
sys@XE> show parameter pdb_lockdown

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string
sys@XE> drop user c##test2 cascade ;
drop user c##test2 cascade
          *
ERROR at line 1:
ORA-01918: user 'C##TEST2' does not exist


sys@XE> create user c##test2
  2  identified by Temp#123
  3  container = all;

User created.

sys@XE> drop user c##test2 cascade;

User dropped.

sys@XE> create lockdown profile p1;

Lockdown Profile created.

sys@XE> alter lockdown profile p1 disable statement=('create user') users=common;

Lockdown Profile altered.

sys@XE> alter system set pdb_lockdown =P1 scope=both;

System altered.

sys@XE> show parameter pdb_lockdown

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      P1
sys@XE> create user c##test2
  2  identified by Temp#123
  3  container = all;
create user c##test2
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in pluggable database XEPDB1
ORA-01031: insufficient privileges


sys@XE> create user c##test2
  2  identified by Temp#123;
create user c##test2
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in pluggable database XEPDB1
ORA-01031: insufficient privileges


sys@XE> create user demo identified by demo;
create user demo identified by demo
            *
ERROR at line 1:
ORA-65096: invalid common user or role name


sys@XE> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XEPDB1                         READ WRITE NO
         4 PDB1                           READ WRITE NO
         5 PDB2                           READ WRITE NO
sys@XE> alter session set container=XEPDB1;

Session altered.

sys@XE> create user demo3 identified by demo3;
create user demo3 identified by demo3
*
ERROR at line 1:
ORA-01031: insufficient privileges


sys@XE>


Connor McDonald
June 08, 2021 - 3:24 am UTC

I tried a few permutations and could not get any to work. I suspect it cannot be done because there is no special "clause" that we are using that a lockdown profile could interrogate.

A ddl trigger might suffice ?

SQL> create or replace trigger check_create_user
  2  before create on database
  3  declare
  4    l_ddl long;
  5    l_sql_text ora_name_list_t;
  6    l_n number;
  7  begin
  8    l_n := ora_sql_txt(l_sql_text);
  9    for i in 1 .. l_n
 10    loop
 11      l_ddl := l_ddl || l_sql_text(i);
 12    end loop;
 13
 14    l_ddl := ltrim(lower(l_ddl));
 15
 16    if l_ddl like 'create user%' then
 17      if instr(l_ddl,'c##') > 0 then
 18         raise_application_error(-20000,'This probably is bad');
 19      end if;
 20    end if;
 21  end;
 22  /

Trigger created.

SQL> conn system/oracle@db19
Connected.
SQL> create user c##test identified by test;
create user c##test identified by test
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.CHECK_CREATE_USER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: This probably is bad
ORA-06512: at line 16


and you could add as much (or as little) complexity into the trigger as you want

from 18c (18.10)

Rajeshwaran Jeyabal, June 08, 2021 - 5:14 am UTC

How ever i was able to get this in 18.10, but not just restricted to common_users, after this profile in place, can't even create a local user too.

sys@XE> show con_name

CON_NAME
------------------------------
CDB$ROOT
sys@XE> show parameter pdb_lockdown

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string
sys@XE> create user c##test2
  2  identified by Temp#123
  3  container = all;

User created.

sys@XE> drop user c##test2 cascade;

User dropped.

sys@XE> create lockdown profile p1;

Lockdown Profile created.

sys@XE> alter lockdown profile p1 disable statement=('create user') users=common;

Lockdown Profile altered.

sys@XE> alter system set pdb_lockdown =P1 scope=both;

System altered.

sys@XE> show parameter pdb_lockdown

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      P1
sys@XE> create user c##test2
  2  identified by Temp#123
  3  container = all;
create user c##test2
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in pluggable database XEPDB1
ORA-01031: insufficient privileges


sys@XE>
sys@XE> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XEPDB1                         READ WRITE NO
         4 PDB1                           READ WRITE NO
         5 PDB2                           READ WRITE NO
sys@XE> alter session set container=PDB2;

Session altered.

sys@XE> create user foo identified by "bar" ;
create user foo identified by "bar"
*
ERROR at line 1:
ORA-01031: insufficient privileges




However running this test case on 19.9, results were very different - still able to create users (both local and common users) with lock down profiles in place.

sys@CQES> show con_name

CON_NAME
------------------------------
CDB$ROOT
sys@CQES> show parameter pdb_lockdown

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string
sys@CQES> create user c##test2
  2  identified by Temp#123
  3  container = all;

User created.

sys@CQES> drop user c##test2 cascade;

User dropped.

sys@CQES> create lockdown profile p1;

Lockdown Profile created.

sys@CQES> alter lockdown profile p1 disable statement=('create user') users=common;

Lockdown Profile altered.

sys@CQES> alter system set pdb_lockdown =P1 scope=both ;

System altered.

sys@CQES> create user c##test2
  2  identified by Temp#123
  3  container = all;

User created.

sys@CQES> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0


sys@CQES>


However checked on the docs (both 18c and 19c) - restriction to 'create user' doesn't exists there, but still works in 18c and not in 19c

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/ALTER-LOCKDOWN-PROFILE.html#GUID-B4029154-54A8-4B78-97C3-9CED416F1C34

Is it possible to check to once with MultiTenant team internally, on how this restriction can be imposed using lockdown profiles ?