Lets create a simple simulation of such an application and then discuss the faults and the fixes and possible issues. We do not have an application BUT I will simulate the database parts. First connect to SYS in my 23c database and create the schema and a procedure:
C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 9 10:15:26 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> create user tt1 identified by tt1;
User created.
SQL> grant create session, create procedure to tt1;
Grant succeeded.
SQL>
And create a simple procedure:
SQL> connect tt1/tt1@//192.168.56.18:1521/freepdb1
Connected.
SQL> create procedure test is
2 begin
3 dbms_output.put_line('hello from tt1');
4 end;
5 /
Procedure created.
SQL>
SQL> grant execute on test to public;
Grant succeeded.
SQL>
Now imaging we have an application that uses this procedure "test" from its interface; this could be vb forms, php, Apex or indeed any interface BUT the application/interface has a bad design feature that I see regularly that application connects ad the schema to the database. Also the application has all of its statements not using full paths. So the application does "test();" and not "tt1.test();".
Let us simulate this in SQL*Plus; so connect to TT1 the schema and execute the procedure:
SQL> connect tt1/tt1@//192.168.56.18:1521/freepdb1
Connected.
SQL> set serveroutput on
SQL> begin
2 test();
3 end;
4 /
hello from tt1
PL/SQL procedure successfully completed.
SQL>
This simulates a much more complex application with a lot of tables, views, PL/SQL that runs this code.
There are numerous faults with this design BUT it is a design model I see regularly on audits of customer systems. The faults:
- Usually code and data in one schema - no grants needed so I can see why people do it BUT it also means no object/data level security
- Connect to the schema - regularly see this in commercial products and also internal projects; again it makes the design and development simple as no grants needed
- Permissions - in this example the application connects as the schema so not grants were needed BUT this is not a good design and in this example the schema granted execute to PUBLIC on its PL/SQL
- Code executed as object(); not schema.object(); - again written for simplicity and ease for the developer
- more - There could be more issues such as vulnerabilities in the PL/SQL such as SQL Injection, multiple applications installed into the same schema - This could create a bridge between applications that should not be there
One simple initial fix could be to not connect the application as the schema. So for this example lets connect as SYS and create a new connection user and then imagine we are connecting the application to the database as the new user and test it. i.e. run the "test();" procedure with no path. Create the user:
SQL> create user uu1 identified by uu1;
User created.
SQL> grant create session to uu1;
Grant succeeded.
SQL>
Now simulate the application connecting as UU1 and running the the PL/SQL as "TEST();" - the application has no path to the use of TEST(); so as we are connected as UU1 and try and execute it - it does have PUBLIC EXECUTE
SQL> connect uu1/uu1@//192.168.56.18:1521/freepdb1
Connected.
SQL> set serveroutput on
SQL> begin
2 test();
3 end;
4 /
test();
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'TEST' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
SQL>
This of course fails as expected as there is no procedure TEST() in the UU1 schema BUT UU1 can execute TEST():
SQL> begin
2 tt1.test();
3 end;
4 /
hello from tt1
PL/SQL procedure successfully completed.
SQL>
BUT, this does not work for the application which hard codes the path to TEST(); in this example and all other objects in the TT1 schema. So we can use CURRENT_SCHEMA instead:
SQL> sho user
USER is "UU1"
SQL> alter session set current_schema=tt1;
Session altered.
SQL>
And now execute the example procedure without a path:
SQL> begin
2 test();
3 end;
4 /
hello from tt1
PL/SQL procedure successfully completed.
SQL>
It works!!, so a simple security fix to allow more control of access to objects in the database is to not use the schema to connect the application to the database and create a connect user for each application and make grants on only the needed objects and then use ALTER SESSION SET CURRENT_SCHEMA=TT1;
This can be added to a logon trigger to make the application work.
It would be better to design for security in the first place; put data in one schema, code in another, critical data in another, critical code in another and make proper grants and design proper connection users. Also use secure coding and lock down the database BUT this simple setting can allow better security and separation more easily.
The user DOES NOT need ALTER SESSION system privilege to do this. CURRENT_SCHEMA is not a parameter:
SQL> sho parameter curre
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_currency string
nls_dual_currency string
nls_iso_currency string
SQL>
It is a memory setting:
SQL> select sys_context('userenv','current_schema') from dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
TT1
SQL>
As a final thought. This ALTER SESSION SET CURRENT_SCHEMA could also be used for abuse. Imagine there is an application that does not use paths to database objects and it relies on access to objects in the connected schema. If there is a "better" object in another schema where the owner has more rights then use of ALTER USER SET CURRENT_SCHEMA could be used to direct an application to use the wrong object
#oracleace #23c #oracle #database #security #grants #session #schema #alter
