technology ASK TOM
BY TOM KYTE
On Separation and Counting
Our technologist promotes more than one schema, counts partitions, and uses TKPROF.
data architect at work has proposed
that we start using separate database accounts to hold the code
(packages, procedures, views,
and so on) and the data (tables, materialized
views, indexes, and so on) for an application. I’ve never come across this idea before,
and it seems to be contrary to the concepts
of encapsulation, in that the application will
be spread across at least two schemas and
require more administrative overhead to
maintain the necessary grants between them.
Are there any situations you can think
of where this would be a recommended
approach? And if you did this, how would you
recommend referencing objects in the data
schema from the application schema? Finally,
would you put any views into the code or
data schema?
This separate application (code) and
data schema approach is hardly contrary. This approach promotes encapsulation; modularization; and most
importantly, least privilege.
Maintaining the grants is a positive
aspect to this approach, because you’ll
have to state why you need a privilege,
and that can be documented. Auditors
can inspect this setup and see what
access you have to the data.
It is a really good idea, in fact, to set
up more than one schema for application code to give even finer-grained
control over the grants.
Right now, developing the application code in the same schema as the
data, you could drop the table, truncate it, perform any data manipulation language (DML) on it, and alter
it in any way you see fit. You will
(fortunately) lose all of that when this
really good separate-schema idea is
put in place. The new separate schema
approach will ultimately lead to a
better-documented system, with great
security controls in place.
Will you lose some “flexibility”?
Sure, you will think so, but a development team should not necessarily have
this flexibility. With so many privileges,
there are too many things you can do
wrong in this environment.
With the separate application and
data schema approach in place, you’ll
be able to query the data dictionary and
see the least set of privileges you need
for your application to execute—which
is very good—and you’ll understand
why you need those privileges, because
you had to ask for them—which is also
very good.
This is a very common implementation, becoming more common today with
the need to be more accountable—
needing to know who does what and when,
who can do what and when, and so on.
Remember, the data is the data,
and the application is something that
accesses data. They are not one and the
same, and they should be separate.
As for the question of how to reference the objects in the data schema in
your application code, I prefer the following, in order:
O Use fully qualified references:
schema.object_name. If you are
worried about the schema name changing in the future, you can always make
the schema name a SQL*Plus substitution variable.
O Use private synonyms, so the schema
name is not referenced in the code.
Private synonyms incur less overhead
than public synonyms, and public synonyms are something to be avoided at
all costs, because there can be only one
public synonym “SOME_NAME” in a
database. The use of public synonyms
can easily prevent server consolidation
if two applications both need to use the
public synonym “SOME_NAME” for
two different objects.
As for the question of where the
views go, the answer is that views can
go in either schema. They make sense in
both places.
The case for putting views into the application schema. A view in the application
schema is a view of convenience; it
joins N tables, selects needed data,
and formats a couple of columns. It is
in place because its SQL is used here,
there, and everywhere. The application
was granted access to the base tables
without the grant option/admin option,
so the application cannot grant access
to this view to others (it is usable only
by the application logic in the database), so you haven’t opened up any
security holes.
In this case, the view is very much
like a subroutine—it accesses the data
to which the application schema was
granted access.
The case for putting views into the data
schema. A view in the data schema is
used to further restrict access to data.
It includes a predicate, for example,
that limits which data can be seen.
Suppose there is a large base table and
a given application schema needs to
see only half of that data. You create
a view that exposes the correct set of
data and grant access on the view to
that application schema, and the application schema uses the view as if it
were a table. That application schema
will not have access to the base table.
You can use the “with check option”
and so on to enforce INSERT/UPDATE
restrictions as well. Note that fine-grained access control (DBMS_RLS)
can also do this.
So the developers are free to create
their views in the application schema,
and the data owners, the people securing the data, create views in the data
schema—to protect the data.
