Set PLSQL_CCFLAGS for the schema-CodePudding

There are several schemas in my database. Some of them are for debugging (we call them DEV schemas) and the others are for production. To differ the DEV schemas from others we use one package which has function named is_debug. In default it return false. To make the schemas as DEV we replace this package with the function that returns true and compile it.

But, recently I learned about Conditional-compilation and PLSQL_CCFLAGS. And I think it could be better to use flags to differ the DEV schemas from others. But it can be set to the DATABASE and SESSION only. I want to set it to the SCHEMA, so that the every package in this schema can use it. Is it possible?

CodePudding user response：

As the documentation states

PLSQL_CCFLAGS provides a mechanism that allows PL/SQL programmers to control conditional compilation of each PL/SQL library unit independently.

And can only be changed at session or system level

Modifiable ALTER SESSION, ALTER SYSTEM

However, you might overcome this limitation by using a logon trigger. Let's imagine you want to change this debug to true when anyone is connecting with the SCHEMA_DEV , and leave it as false when they connect with any other one

create or replace trigger sys.logon_flags_plsql
after logon on database
declare
v_true     varchar2(200) := 'ALTER SESSION SET PLSQL_CCFLAGS = ''debug:TRUE'' ';
v_false    varchar2(200) := 'ALTER SESSION SET PLSQL_CCFLAGS = ''debug:FALSE'' ';
v_user     varchar2(128);
v_os_user  varchar2(128);
begin 
SELECT UPPER(SYS_CONTEXT ('USERENV', 'SESSION_USER')),
       UPPER(SYS_CONTEXT('USERENV', 'OS_USER')),
       INTO
      v_user,
      v_os_user
FROM DUAL;
--
if v_user in ( 'SCHEMA_PRE', 'SCHEMA_PRO' ) 
then 
    execute immediate v_false;
elsif v_user = 'SCHEMA_DEV' 
then
    execute immediate v_true;
else 
    null; -- or whatever you consider to
end if;
end;
/

One advantage of using logon triggers is that you can apply a lot of settings to connections coming from users, operating system users, and any other property you can think of available in the default context sys_context

However, this solution will only work as long as the users are not setting the flag by themselves. Keep in mind that the privilege alter session to modify any parameter is inherited by the privilege create session.

Demo of this ( as sys we create an user with only create session privilege )

SQL> create user test3 identified by Oracle_1234 ;

User created.

SQL> grant create session to test3 ;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

Connected as this user with only create session privilege

$ sqlplus test3/Oracle_1234

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 4 14:39:47 2021
Version 19.6.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> alter session SET PLSQL_CCFLAGS = 'debug:FALSE' ;

Session altered.

SQL>

So, as long as your users are not kind of "cheating" this could solve the issue at schema level, applying the alter session you want depending on who is connecting. Of course, if you are using personal users with privileges over the schema, you have to use another logic if you want to make it work. I think you get the idea.