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.
Contact:
Franz
Security,
Access control on tables, views, Synonyms or Rows - Part
2
Introduction to Views
A view displays the data from a table. Typically the view will display only a subset
of the rows of the base table, and also only a subset of the columns of the
base table. A view is really a stored
query on a certain set of data. So every
time you access the view, it causes the underlying SELECT statement/s to be executed. So the view itself, although it is an object,
does not contain any data. A view
consists of the Query statement.
Data in a view can be INSERTED, UPDATED, and DELETED;
provided that the NOT NULL columns are represented in the view, of if the NOT
NULL columns are not present in the view, then they have default values. Changing the data in a view, means that the
changes submitted are also dependent on all of the integrity constraints and triggers
that have been defined on the base tables.
Because of all the restrictions, you will find times, when you can’t
update a table through a view.
A view may consist of a query statement that includes
several base tables that are joined together.
This type of view will also be updatable, as long as all the integrity
constraint requirements, and trigger requirements on the base tables are
met. Because of having default values
for certain columns, you may not even be aware of all the values that are going
into the full row on the table side.
A view may include summary data, for example a view may
consist of the totals of the business conducted in a supermarket on a daily or
hourly basis. In this case you can’t
update the base tables, because the data that you see, has been derived from
the base tables, and therefore does not exist in the base table.
In business applications, a view is often designed to bring
a set of data together , that takes quite a complex query to assemble. Then for reporting purposes, queries can be
designed on top of this view, to report on various aspects of the progress of
the business. I think you can recognise
the problem. Just a simple query on the
view, will mean that the complex and potentially long running underlying query
will first have to be run. As a result
such application reporting functionality is slow, to the point of being
unusable to some extent. One solution to
this dilemma is to create a temporary table, and clean it out and populate it
every day, with the query that would have returned the data for the view. Now when you run a query against this
temporary table it goes very quickly by comparison. Oracle has gone the next step for you, and
created an object called a Materialized View, or a snapshot. A materialized view is like a hybrid between
a view and a table. It includes the
query as part of the object, but also includes the storage definition as part
of the object. So when you create or
refresh a materialized view it will run the query, and then store the results
in an object that has all the properties of a table; a materialized view. Now you can just refresh this materialized view
whenever you need to. A straight-forward
materialized is not updatable, so in order to update the underlying tables, you
will need to update the tables directly, and then refresh the materialized view
in order to be able to see the changes.
If you want a certain set of users to be able to see only
specific columns of the underlying tables, then you create a view of that
table, that only contains the necessary columns. You can then grant these users, access to the
view, and revoke their access to the base table. A synonym to the base table, can keep the
process transparent.
Example of a view, notice how the “hire_date” and “salary”,
have been left out of the view:
schema object privilege
This is a privilege or right to perform an action on a schema
object. There are different object
privileges available for different types of schema objects. For example for objects like views and
tables, you can grant SELECT, UPDATE, DELETE, INSERT, for objects like
procedures and packages, you can grant EXECUTE on them. Some schema objects don’t have associated
object privileges, for example clusters, indexes, triggers, and database
links. For example because there are no
associated object privileges on clusters, a user must either own the cluster or
have the ALTER ANY CLUSTER system privilege granted to them, in order to be
able to alter a cluster.
In some cases, a finer level of access control is needed
for tables and views, and the rows and the possible actions, associated with
them. This finer level of access may be
associated with an application. Oracle
fine-grained access control can be used to facilitate such requirements.
Franz Devantier,
Need a database health check, or a security audit?
devantierf@gmail.com
Classifieds
No comments:
Post a Comment