| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Login Triggers
Speaking of VPD's, FGAC and FGA, Arup and Don Burleson have published a very good book
oracle privacy, security, auditing and how to catch a hippo. The book name is:
Oracle Privacy Security Auditing: Includes Federal Law Compliance with HIPAA, Sarbanes-Oxley & The Gramm-Leach-Bliley Act GLB by Arup Nanda, Donald K. Burleson
The authors did an extremely good job with oracle security, which is explained in full detail, and showed applications of tho oracle mechanisms to various legislative requirements. I'm only aware of HIPAA because I was working for an HMO for almost 5 years, so I skipped the other things. I can recommend that book to everybody seriously interested in finer details of oracle security. It's easy to read, clearly written and examples even work.
On 03/12/2004 03:26:07 PM, John Flack wrote:
> Now I'm beginning to see what you're talking about. My bet (but it would help if you showed us the SQL for one of those long running queries) is that your queries do joins with privilege tables that use the current username to decide which rows this user is allowed to see. You are right, having some key information from the privilege table saved in memory should help quite a bit. A login trigger COULD store this information in package variables as Stephane suggests.
>
> Another possibility that you might consider is to use the virtual private database (VPD) feature to add to the WHERE clause dynamically every time your data tables are queried. There are several good articles on VPD available on the web, but I'll point you to one of the more recent ones, "Keeping Information Private with VPD" (http://otn.oracle.com/oramag/oracle/04-mar/o24tech_security.html) by Arup Nanda in the March issue of Oracle Magazine.
>
> -----Original Message-----
> From: Stephane Faroult [mailto:sfaroult_at_oriole.com]
> Sent: Friday, March 12, 2004 2:17 PM
> To: oracle-l_at_freelists.org
> Subject: Re: Login Triggers
>
>
> Maryann,
>
> Then what you want is possibly packaged variables. You must create a
> package holding these variables, plus a function to get their values
> from the DB which will be called only once, possibly from the
> initialisation section of the package. You can call a function from the
> package in the initialization trigger, but this isn't mandatory. In
> fact, the very first reference to something in the package will cause
> the initialisation section to fire.
>
> That said, I doubt that caching such information will do much to
> improve a single query of death which run for 6 hours. I'd look
> elsewhere. Those techniques are useful for avoiding 600,000 select
> sys_context('USERENV', 'whatever') from dual per hour.
>
> SF
>
>
> Maryann Atkinson wrote:
> >
> > The queries we have which are slow, have some where clauses,
> > among others that is, to which we embed user information,
> > like privs, level, status etc, ie most likely personal info for each user.
> >
> > What I was hoping is to fire a login trigger that would save this info
> > in memory somehow, and then the queries could take it from there
> > instead of issuing other queries to find it...
> >
> > But thanks for the materialized views examples,
> > we are going to have to think and perhaps compare
> > before we figure it out...
> >
> > Thanks,
> > maa
> >
> > At 12:59 PM 3/12/2004, you wrote:
> > >If you had a login trigger that ran a 6 hour query, the user would, by
> > >default, not finish logging in until that query was done. You could have
> > >the login trigger submit a job that would run this query in the
> > >background, but then the user would have to know where to go to look for
> > >the results. If users ever do something other than run this query, you
> > >would be spending a lot of resources running the query when no one wanted
> > >the results.
> > >
> > >Materialized views are much, much easier to deal with in this
> > >situation. In addition to the fact that they're available to everyone all
> > >the time, they get refreshed automatically, you don't have to teach users
> > >where to go to get the data, and you don't execute huge queries when you
> > >don't need the results.
> > >
> > >
> > >Justin Cave
> > >Distributed Database Consulting, Inc.
> > >http://www.ddbcinc.com/askDDBC
> > >
> > >-----Original Message-----
> > >From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> > >On Behalf Of Nick Gabrielatos
> > >Sent: Friday, March 12, 2004 10:39 AM
> > >To: oracle-l_at_freelists.org
> > >Subject: Re: Login Triggers
> > >
> > >Thanks, that helps quite a bit.
> > >Let me ask you this, though,
> > >you think we should use cashed tables and materialized views instead of
> > >login triggers?
> > >
> > >Wouldnt a login trigger have an advantage?
> > >
> > >thanks,
> > >maa
> > >
> > >At 11:43 AM 3/12/2004, you wrote:
> > > >Maryan, materialized views are pre-built queries whose results are
> > > >stored in a table and refreshed periodically. Here is slightly modified
> > > >example from the manuals:
> > > >
> > > >CREATE TABLE sales_sum_table
> > > > (month VARCHAR2(8), state VARCHAR2(40), sales NUMBER(10,2))
> > > > STORAGE ( BUFFER_POOL KEEP);
> > > >
> > > >CREATE MATERIALIZED VIEW sales_sum_table
> > > > ON PREBUILT TABLE
> > > > REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/(24*60)
> > > > ENABLE QUERY REWRITE
> > > > AS SELECT t.calendar_month_desc AS month,
> > > > c.cust_state_province AS state,
> > > > SUM(s.amount_sold) AS sales
> > > > FROM times t, customers c, sales s
> > > > WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
> > > > GROUP BY t.calendar_month_desc, c.cust_state_province
> > > >
> > > >
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Mar 12 2004 - 14:44:14 CST
 |
 |