Dear Readers,
My name is Franz Devantier, creator of this blog. I am an Oracle Certified
Professional (OCP DBA 11g) Security DBA.
I will be sharing with you the basic duties of an Oracle DBA, and also
some of the undocumented, and not so well known tasks.
I will make a deal with you: If you refer me to a company that needs
database support, from a few hours per week to full time, and I am able to sign
a contract with them.
Then I will give you 10% of the monthly
contract or deal price every month. When
the contract ends, and we re-sign the contract, I will again give you 10% of
the monthly contract price. This will go
on until the company no longer employs or contracts me or my agents to look
after their databases.
I can do this, because that 10% is my
marketing budget. When we re-sign the
contract, in the future, it may depend on you giving the thumbs up again, and
that is worth 10% of the monthly contract price, to be given to you as
commission.
Security,
Privileges, Roles, Profiles and Resource Limitations - Part
8
Type
Dependencies
When a
type is referenced from another object, it is called a dependency. Now a table contains data that will rely on
the type definition for access to the data.
If there is a change to the type, then the stored data becomes
inaccessible. The type of change that
can cause this situation is if the privileges required by the type are revoked;
or the type is dropped; or the dependent types are dropped. If this happens, then the table becomes
invalid, and the data can no longer be accessed.
There is a
big difference between an ordinary table that has become invalid because of
missing privileges. When the missing or
required privileges are granted, then the table becomes valid again. So with an ordinary table becoming invalid is
not too much of a problem, provided you can work out which privileges are
missing.
A table
that has become invalid because the missing privileges constitute a dependent
type that has been dropped is another situation. In such a case the table can never be
accessed again. The only action that you
can perform on such a table is to drop the table. This reveals a security issue when using
types. You may still be able to recover
to some extent if you have recent backups of the database.
So you can
see that the effects of revoking a privilege on a type or dropping a type can
cause catastrophic results, as far as the integrity of your database is
concerned. The Oracle Database Server
has implemented a check on the REVOKE and DROP TYPE statements, called “restrict
semantics”. What this does is that if
the Name Type in either of the two statements has any table or type
dependencies, then the statement aborts with an error. This makes you feel a bit better about these
statements, however there is a FORCE clause available with both of these
statements. If the FORCE clause is added
to the REVOKE or DROP TYPE statement, then the statement will succeed. If there are any dependent tables on the
types that were revoked or dropped, then the tables become permanently invalid,
with no way of remedying the situation.
This
illustrates the importance of now granting the REVOKE and DROP TYPE system
privileges to any of the users, unless it is absolutely essential; and then it
should not be granted on a permanent bases either.
Franz Devantier,
Need a database health check, or a security audit?
devantierf@gmail.com
Classifieds
No comments:
Post a Comment