Side-effect of ALTER SESSION SET CURRENT_SCHEMA

For most of our database set-ups we use a different TEMP space for application users than for end-user/support/developer/reporting usage.
The intention is to minimise the potential impact of a rogue ad-hoc query on the main applicaiton.

However, turns out this is ineffective IF you use:

ALTER SESSION SET CURRENT_SCHEMA = main_app_schema;

This is documented but I wasn’t previously aware.
No surprise that this learning opportunity was presented as a direct result of a rogue query on a prod DB blowing out the main TEMP space.

Deduction quickly suggested that the above must be the case and it was quickly proven by a test case and also supported by the documentation .

the database uses the temporary tablespace of the specified schema for sorts, joins, and storage of temporary database objects

Obviously… not using ALTER SESSION and using fully qualified object names prevents this “vulnerability”… but that’s not good enough unfortunately.

Advertisements

2 Responses to Side-effect of ALTER SESSION SET CURRENT_SCHEMA

  1. Narendra says:

    Hello Dom,

    Nice finding.
    So do you think it is a good practice to allow end-user/support/developer to run queries, and that too using application tables, directly on production databases? If they have SELECT access on application tables, they can also run a SELECT FOR…UPDATE and lock part or all of the table rows. This, in my opinion, is a bigger risk. Of course, one can say that their organisation has strict manual processes and monitoring in place in order to catch the culprit who attempts to lock tables but that again means relying on manual procedures (not database) to avoid these “accidents”.

    Here is one way to avoid both of these issues i.e. having to grant SELECT access on application tables to end-user/support/developer and end-user/support/developer blowing off application TEMP space with a rogue query
    1. Maintain 2 schemas. For e.g. APP_MAIN will be the application user which owns all tables and APP_WRAPPER will be the user that has SELECT access on tables owned by APP_MAIN
    2. Create views (WITH READ ONLY option for extra security) in APP_WRAPPER schema for all tables in APP_OWNER schema, which are needed to be queried by end-user/support/developer.
    3. Grant SELECT access on views in APP_WRAPPER schema to end-user/support/developer.
    4. Assign separate TEMPORARY tablespaces to APP_MAIN, APP_WRAPPER and end-user/support/developer.

    This will ensure
    a) end-user/support/developer will never have direct access to application tables and
    b) end-user/support/developer will be able to query data from application tables and
    c) end-user/support/developer will not be able to blow off TEMPORARY tablespace used by mail application schema(s).

    One can say this borders on “obsessive compulsive security disorder” but…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: