Oracle grant concession and side effects

Question

Working every day on a large Oracle production database (10g), we noticed that operations like

• granting an user read privileges on a table
• creating a trigger on a table

lock that table, and invalidate all the cursors insisting on it.

This has tremendous consequences if the table is big (> 20 million rows) and many users are working on it.

My question is: why is Oracle locking the table (we are not altering its structure, after all, but just giving an user the permission to read it) and why does it need to invalidate the cursors?

Is there a way to make actions like those "softer"?

Thanks in advance.

Alternative question: is there a way to know how many cursors are open on a specific table, in order to minimize the impact invalidation has on that object?

Answer 1

Maybe granting rights to groups one time, and then in the future granting membership rights to users, instead of direct table rights. Pretty bare answer I know, but I get the impression you know what you are doing and can figure out the details.

Answer 2

I can see how adding a trigger would require a lock and either invalidating the cursors or waiting for all the open ones to close, at least if the cursors could potentially be used for an action that would require execution of the new trigger.

It's hard to see a reason why granting read privileges would have similar requirements, probably just a side-effect of the implementation. MJB's answer seems like a decent way to deal with that (and in lots of cases is a good practice anyway, simplifies administration of access privileges).

Answer 3

The groups thing from MJB would be the best work around to the grant issue, and for "trigger" issues I would recommend separating the business logic such that it performs anything that a magical "trigger" would do, especially on a 20 mil+ row table.

Answer 4

1) My question granting read access to a table would be why are you granting read directly to the table and not creating a role, granting read on the role to the table, then granting (or removing) the role to the users? This will remove the locking table problem on grants.

2) Oracle will lock the table on creating a trigger because the trigger can alter the table when installed. All DDL will lock the table, creating a transaction, to know exactly when it can engage the trigger (or other changes). I suspect this is also true for the grants.

If you are continually adding/removing triggers from the table, I would remove the code you are changing from the trigger and put it into a separate PL/SQL procedure. Then update the procedure as needed. This will cause the trigger to become invalid (and require recompiling) which is done automatically.

I sure there is a way to figure out which cursors are opened against a given table, using the v$ views in the Oracle data dictionary.

Answer 5

Can't help with the first question, but for the alternative I found a couple useful commands here and here

Answer 6

Eliminating invalidations based on grants:

Create xxx_READONLY roles where the xxx is some appropriately meaningful value, and grant select access to all the appropriate tables to the role, and add the role to the users when they need them.

Eliminating DDL locks based on trigger creation:

Last time I really checked, trigger were interpreted code, whereas procedures and packages were compiled code. Therefore, performing complicated logic in triggers is generally frowned upon. One can invoke a procedure or package method within triggers, and having your trigger logic be encapsulated in a procedure/package may mitigate or eliminate the DDL locks against the base tables.