Re: access to dbms_fga package on shared server

[ora...@msn.com]

Comments embedded.

On May 12, 8:03 am, maxim2k <maxi...@gmail.com> wrote:
> Hi,
>
> I manage an Oracle Database 10g R2 on Red Hat Enterprise Linux 4, the
> server is shared between a few customers: each customer has access
> (CONNECT and RESOURCE priveges) to his own schema only, he cannot access
> other customers objects.

I can only presume this access is through the schema owner. Is this
the ONLY account accessing this users objects?

>
> One of our customers just asked EXECUTE privilege on the dbms_fga package.
>

Which should not be an issue. My question is this: if there is only
ONE user account which can access these user objects what good does
having execute privilege on dbms_fga provide? This is used to provide
Fine-Grained Access (fga) to database objects based upon a user id.
If only ONE user id accesses these objects I can see no purpose in
granting access to this package.

> I'm new to this package and I'm not sure what would be the consequences
> of such grant.
>

None, really, as normally it restricts/audits user access to objects
not owned by that user.

> Can I safely grant that to the customer in question without compromising
> the security of other customers data on the shared server?
>

Certainly, however I see little, if any, benefit to this if my
understanding of this configuration (one user account per customer) is
correct.

> Thanks.

David Fitzjarrell

[Mark D Powell]

To add to what David posted ask the customer what he or she intends to
do. It the customer application passes in the 'real' user then the
customer may be trying to capute who really performed a change or may
actually want to use the dbms_rls package.

Personally I do not think customers should have the ability to create
objects in a production environment. If this is a valid activity for
the application then I would want the object creation handled via a
package referenced via a provided screen interface.

HTH -- Mark D Powell --

[ora...@msn.com]

On May 12, 9:03 am, maxim2k <maxi...@gmail.com> wrote:

> On 5/12/08 3:30 PM, fitzjarr...@cox.net wrote:
>
> > I can only presume this access is through the schema owner.  Is this
> > the ONLY account accessing this users objects?
>

> Yes, each customer has an Oracle user/schema.

>
> > Which should not be an issue.  My question is this: if there is only
> > ONE user account which  can access these user objects what good does
> > having execute privilege on dbms_fga provide?  This is used to provide
> > Fine-Grained Access (fga) to database objects based upon a user id.
> > If only ONE user id accesses these objects I can see no purpose in
> > granting access to this package.
>

> I think the customer needs to create an audit log of actions performed
> over database objects. Is there a way to do that without using the
> dbms_fga package?
>
> Thanks.

That depends upon what you want to accomplish with this audit trail,
this may give you some ideas on how to proceed depending upon the
desired result:

http://oratips-ddf.blogspot.com/2006/05/audit-this.html

David Fitzjarrell

[Mark D Powell]

> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

Besides the native audit command and the FGA feature you have the
option of using table row triggers to insert a change row into a
history/audit table whenever a base table row is changed.
Applications can also be written to record change activity.

Which feature or method for capturing change data depends on the
environment, the application, and the data. You might use the audit
command to record DDL actions, row triggers to record detail data
changes to specific tables, and FGA to filter what data users can see
on a query against other tables all within a single database.