Carlos Sierra's Tools and Tips

So, what is new in version 11.4.5.0?

There are some functional enhancements and a couple of small fixes. What really changed is the security model supporting SQLT. Now we have two users: SQLTXPLAIN and SQLTXADMIN. Legacy SQLTXPLAIN owns all SQLT Tables, Indexes and Sequences, while SQLTXADMIN owns all SQLT Packages and Views. Why? It has to do with restricting as much as possible access to a system. SQLTXPLAIN used to have several system privileges needed by its set of PL/SQL libraries. Once we split this account into two we can strip SQLTXPLAIN from all its system privileges and assign a subset of them to new SQLTXADMIN.

Why this change?

We needed SQLTXPLAIN with no system privileges  since any SQLT user needs to provide the SQLTXPLAIN password every time SQLT is used. This latter requirement supports the export of the SQLTXPLAIN repository for the SQL being analyzed, which in turn is needed for Test Case (TC) creation. We also needed those system privileges to extract a Test Case Builder (TCB) and to perform several other “admin” tasks SQLT does in the background.

SQLTXADMIN has now some system privileges listed in the “Security Model” of the sqlt_instructions.html file that comes within the sqlt.zip file from MOS 215187.1. SQLTXPLAIN only has CREATE SESSION system privilege, and it is no longer granted SQLT_USER_ROLE. So, if you try to execute SQLTXPLAIN connected as SQLTXPLAIN you will get this error:

BEGIN
*
 ERROR at line 1:
 ORA-20109: User "SQLTXPLAIN" lacks required "SQLT_USER_ROLE" role. Request to your DBA: "GRANT
 SQLT_USER_ROLE TO SQLTXPLAIN;".
 ORA-06512: at line 3

Do you want to grant SQLT_USER_ROLE to SQLTXPLAIN?

The anser is NO. What you want instead is to execute SQLT connected as the application user that generated the SQL statement you want to analyze. Be sure this application user has been granted the SQLT_USER_ROLE, or you will get the same error. If for some reason you cannot use the application user or you cannot grant SQLT_USER_ROLE to it, then and only then execute SQLT connecting as SYS or any DBA account.

Be also aware that SQLTXADMIN is created locked and with a random password. There is no need to unlock this account or reset the password. Of course SYS could do that but it is not needed nor wanted by SQLT. Think of SQLTXADMIN as the guardian of the system privileges SQLT needs. It uses those system privileges from within SQLT code in those PL/SQL libraries owned by SQLTXADMIN and mostly acting on top of SQLTXPLAIN Objects.

Regarding SQL_USER_ROLE.

This SQLT_USER_ROLE  has also been stripped from system privileges preserving only two: ADVISOR and SELECT_CATALOG_ROLE. If granting those privileges to the application user through this role is a concern at your corporation, you can always strip this role from the application user when SQLT is not used by this user, or you can execute SQLT as SYS or any other DBA account.

What happens when you connect and execute SQLT as SYS or DBA instead of the application user that issued the SQL?

Well, you get almost everything, but what you don’t get is the plan produced by the EXPLAIN PLAN FOR command, which basically shows you the plan if you had disabled bind peeking. Not a big issue in most cases. So, if you don’t want to grant your applications users a couple of system privileges then don’t do it, and execute SQLT connecting as any DBA. You will also lose the EVENT 10053 Trace.

What about Trace Analyzer?

Trace Analyzer (MOS 224270.1) also known as TRCANLZR or TRCA for short, went through the same process. So TRCA 11.4.5.0 is also split into two accounts: TRCANLZR and TRCADMIN. Legacy TRCANLZR owns Tables, Indexes and Sequences, while TRCADMIN owns Views and Packages. Similar concepts as SQLTXPLAIN and SQLTXADMIN division of functions, but on a smaller scale. TRCA users must now be granted the new TRCA_USER_ROLE before executing the trcanlzr.sql script to analyze a Trace.

How this new “Security Model” affects SQLT or TRCA functionality?

The new security model is expected to have no effect in the functionality of these two tools. The only thing you may notice is that some operations that could be done connecting as SQLTXPLAIN cannot longer be done from this account. This means some operations like creating a SQL Plan Baseline for a SQL statement would require now a qualified account other than SQLTXPLAIN. But, for the most part like executing any of the main methods, a user connecting as such, should not notice any functional difference after 11.4.5.0 is applied.