Database security

Database Security: Principle of Least Privilege
Mike Henderson, October 2007
About the author
Mike Henderson has a justifiable reputation as one of the top Oracle
consulting DBAs in Ireland. His time with Curach Consulting has
supplemented a distinguished 18 year career in IT including several years
with Oracle Corporation. Mike’s roles have encompassed development,
database administrationand consultancy, but the ability to deliver
optimised database performance through expert and innovative design is
his hallmark. Mike is an Oracle Certified Professional and currently has
identified database security, high availability and Oracle Text as areas for
promotion.
Introduction
'The price of peace is eternal vigilance'. Whilst the original intent of the
passage was not databasesecurity the quote holds true for every DBA.
Security has always been a concern for the DBA but the growth of
compliance requirements over recent years has put database security high
on the agenda.
This series of Insight pieces is intended to provide a guide to improving the
security of Oracle databases. We will be taking small steps up from the
standard install and taking each concept alittle further than is regularly
applied. The hints here should be applicable to any database edition from
9i up and usually earlier versions too. The next Insight piece will look at
passwords and user profile management.
Extending the principles of least privilege
DBAs and developers should be familiar with one of the foundations of
database security, the principle of least privilege. For thoseunfamiliar with
it, the principle is that users should only be granted the minimum amount
of privilege in order to conduct their assigned tasks.
The practical application of this principle is usually expressed as by the
creation of a series of database roles e.g. a viewer role, an 'accounts role'
that can perform certain functions but not other, an HR role with a
different series of grants& privileges etc. A role is effectively a container
for object grants and database privileges.
In an Oracle Forms environment or similar, roles usually comprise grants
on tables. When a user is granted the role they have direct access rights
on a table, select grant allows them to view the whole of any row in that
table, updates can be applied to any column of the table, deletes can be toany row. The privileges are general and unconstrained - this cannot be
'least privilege'!
In an api driven environment direct access to tables is not allowed and is
replaced with a series of execute rights on packages. There will be an
insert procedure, another for update, maybe a few with different select
properties. The api constrains the access and may enforce extra business
logic, limitingprivilege and enhancing security. This model is generally
considered more secure than the direct grant approach.
So far so good, but what we haven't addressed are dimensions such as
time, location and application.
Curach Insight
© Curach Consulting 2008
In most environments, roles are enabled by default when the login
succeeds. A user gains full rights anytime they manage to connect to thedatabase. If we apply the principle of least privilege then this method is
probably not appropriate - the roles that the user was given were intended
for use within the HR package, not for when they started up a SQL Plus
session. Neither method above differentiates. Oracle has addressed this
problem in a number of ways, with varying degrees of usefulness.
The first mechanism is the nondefault role - a role which has been granted
but has to be explicitly asked for before it becomes active.
dba_sql> CREATE ROLE BIG_ROLE;
dba_sql> GRANT UPDATE ON EMP TO big_ROLE;
dba_sqL> GRANT BIG_ROLE TO mike;
dba_sql> ALTER USER MIKE DEFAULT ROLE ALL EXCEPT big_role;
This mechanism works fine embedded in scripts for sql*Plus but can be
difficult to embed in some application tools e.g....