11gR2, Linux, 2-node RAC replicating to a 2-node active data guard. On the standby site, we have created an 'reports' service, and we have a tnsnames entry which directs anyone connecting @reports to the standby site first, as per Oracle's maximum availability architecture documentation.
Other users wanting to insert/update/delete live data (or, occasionally, query little bits of live data) connect to various other services which run preferentially on the primary site.
My question is, what can I do to thwart a determined user who's smart enough to know how to craft a tnsnames.ora that directs to the primary site and who wants to run his reports there?
The obvious concern is a "reporter" running a long multi-hour, temp-thrashing report on what's supposed to be the primary, production database. He obviously has the relevant select privileges in both the primary and the standby; we just don't want him exercising them on the primary, usually.
I thought of a number of things:
1. Create a profile, attached to the user, which says 'if estimated execution time > 30secs AND you're instance role is 'primary', then don't execute'. But I don't think you can do instance-role aware profiles like that.
2. An after-logon trigger that says 'if username is X and instance role is primary, kick the user off', but don't like the idea of maintaining a list (or table) of users who are only allowed to connect to the standby
3. OID-managed connections, so the REPORTS alias is defined correctly to go to standby first (but still wouldn't help thwart someone determined enough to edit their sqlnet.ora and craft their own tnsnames.ora)
4. Something to do with Resource Manager... but we don't currently use it, and I wouldn't know where to begin
General thoughts on what the best sort of approach would be?