RE: How to stop access to prod instance ...

From: sundeep maini <sundeep_maini_at_yahoo.com>
Date: Sun, 06 Jan 2002 21:06:05 -0800
Message-ID: <F001.003E8139.20020106203019@fatcity.com>

Raj,

If you are using 8i or above, I would highly recommend you go through fine grained access features. They are a bit exhaustive to cover here but I have used them previously for some very complex stratifications and they will work for your requirements too.

Here is a brief note for each of your criterion but you will have to build the procedures.

 Use log on triggers with fine grain access (dbms_rls and sys_context/user_context) to enable roles from forms application and impose user/role restrictions at logon.
You can design appropriate roles to segregate users to control their behavior. This solution is not profile dependent and not tool specific. Albit it will have the context of an application(s) and alter the queries and set the initial environment/restrictions trough a trusted PL/SQL package whichever client the queries may be issued from. You also have the potential to add dynamic predicates to further control query behavoir.

Your reporting tool doesn't have to do anything special like invoking a runtime role. The logon triggers the environment change or enabling of specialized roles. You can store policies with tables and you can segregate behavior for selects vs updates (upd,ins,del - by encapsulating them in packages if required or simply by adding appropriate dynamic predicate to your table and Oracle will rewrite the queries involving those tables and views by including the predicate).

In a rare departure, the Oracle manuals (application developers guide and dbms_rls documentation in pl/sql packages reference and sys_context documentation) will give you all the fodder to feed your fancy. Not only what you propose is doable, I am sure you'll find more flexibility than you have asked for.

HTH
Sundeep

• "Jamadagni, Rajendra" <Rajendra.Jamadagni_at_espn.com> wrote:
  > Thanks all ...
  >
  > The problem doesn't end here ... the requirements
  > (loosely specified) are as
  > follows
  >
  > 1. Developers and end users can access production
  > ONLY through FORMS
  > APPLICATION.
  > 2. End users can't get to sqlplus so I am not even
  > worried about that. It is
  > developers that I am worried about.
  > 3. Enabling roles at runtime is an option as long as
  > we are not talking
  > about reports. We use SQR and Oracle reports, a
  > reports agent looks up
  > requests and creates shell files on Unix server
  > which are then scheduled to
  > run. This scheme should work there as well.
  > 4. How about people accessing tables through db
  > links?
  >
  > I don't think there is a 100% proof method, but
  > basically I want to be able
  > to restrict developers accessing the instance except
  > through forms
  > application. Dropping developer's accounts is not an
  > option.
  >
  > I thought over it, discussed it with my senior DBAs
  > and finally I am turning
  > to you guys. All the suggestions are helpful but I'd
  > like to avoid as many
  > loose ends as possible.
  >
  > Thanks in advance
  > Raj
  >
  

  ---

  
  > Rajendra Jamadagni MIS, ESPN Inc.
  > Rajendra dot Jamadagni at ESPN dot com
  > Any opinion expressed here is personal and doesn't
  > reflect that of ESPN Inc.
  >
  > QOTD: Any clod can have facts, but having an opinion
  > is an art!
  > >
  >
  *********************************************************************1
  >
  > This e-mail message is confidential, intended only
  > for the named recipient(s) above and may contain
  > information that is privileged, attorney work
  > product or exempt from disclosure under applicable
  > law. If you have received this message in error, or
  > are not the named recipient(s), please immediately
  > notify corporate MIS at (860) 766-2000 and delete
  > this e-mail message from your computer, Thank you.
  >
  >
  *********************************************************************1
  >
  >
  

  ---

Sundeep Maini
Consultant
Currently on Assignement at Marshfield Clinic WI mainis_at_mfldclin.edu

---

Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: sundeep maini
  INET: sundeep_maini_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sun Jan 06 2002 - 23:06:05 CST