SQL Tuning Advisor – Security and Performance Implications

oracle-11goracle-12cperformanceSecurity

I work on a relatively small development team that works with an Oracle (11g) database. It's recently been requested that all the developers be given the Advisor role so that we can utilize SQL Tuning Advisor when developing complex queries. Some have raised concerns that this may have significant performance and/or security implications, but I have not been able to find concrete answers to what these implications are.

If this role were to be given to the various members of my team, what are the major pitfalls we should be watching out for? I've included the tag for Oracle 12c as well, since we'll be upgrading to that in the near future. If there's a significant difference between the two, I'd appreciate it if it was at least pointed out.

Best Answer

As long as the developers only have that role in the development database instance, I see no problem; far better to tune now than later. Now, if your development machine has limited resources, there may be a reason to limit access. But in general, usually tuning tasks run from a few minutes to the default maximum of 30 minutes; during that time, Oracle may try parallel queries (if enabled), so that could be an issue, but you can play with the instance parameters parallel_adaptive_multi_user and parallel_degree_level to minimize the impact.

There are no changes between Oracle 11g and 12c that would affect your decision.

Related Solutions

Performance tuning OLTP query

I would first consider trying to combine the two queries into one. If the second query could be refactored to return null in the case where the first query returned zero would you be able to work with that?

For example,

testbed:

create table t1( cp_id integer, 
                 brl_id integer, 
                 security_enabled_ind char(1) );
create table t2( brl_id integer, 
                 ranking number );
create table t3( ppr_id integer,
                 brl_id integer );
create table t4( ppr_id integer, 
                 plcy_id integer, 
                 where_clause varchar(1000), 
                 ranking number );
create table t5( plcy_id integer, 
                 db_object_name varchar(100), 
                 statement_type integer );

refactored query:

with w as ( select rpr.brl_id, ppr_id, plcy.statement_type
            from t3 rpr join t4 ppr using(ppr_id) join t5 plcy using(plcy_id)
            where plcy.db_object_name=:2 )
select where_clause
from( select where_clause
      from t4 ppr_o
      where not exists ( select *
                         from t1 cpr join t2 brl using(brl_id)
                         where cpr.cp_id = 1 and brl.ranking > 0
                               and cpr.security_enabled_ind='Y'
                               and brl_id not in (select brl_id from w) )
            and ppr_o.ppr_id in ( select ppr_id
                                  from t1 cpr join w using(brl_id)
                                  where cpr.cp_id=:1
                                        and cpr.security_enabled_ind='Y'
                                        and w.statement_type=:3 )
    order by ppr_o.ranking desc nulls last )
where rownum=1;

Sql-server – Security implications of guest account and database chaining on sql server

It does not open up directly to an "attack". It just means that any user from Database 1 (Kdb) can also access database 2 (Ydb). What's usually more critical is, when you have users with DDL-Permissions (create views, procedures) - they will also be able to access objects in database2. Maybe even more, than plain guests can. That depends on the object owners and permissions you are planning to grant to guest(s).

Under SQL Server before 2012, there was one little security hole, described here: Security-issue: guest-guest impersonation, but this is a very limited scenario

Best Answer

Related Solutions

Performance tuning OLTP query

Sql-server – Security implications of guest account and database chaining on sql server

Related Question