A View of Creating an Oracle User – Part I

Before any schema objects can be created, you must first
create a user that will own these objects. This somewhat simple procedure is
often overlooked and it can open wide holes in security and portability.

How much thought do you put into creating an Oracle user?
Have you ever looked within your database after you have installed a database
tool or software solution? Many times, we given these requirements at face
value and do not feel we can or should question the procedures and requirements
of software vendors or our own development teams. After all, they surely would
not request authorizations that they do not require.

In the past, I have installed many software products that
use Oracle on the back end. I have even worked for a few software companies in
the past couple of years. The scenario is often the same. Most developers are
not database experts, they build solutions in a vacuum, they never get database
staff to look at their designs from the beginning and we are all too concerned
with just getting a project done. The end result is an open database model
where database privileges are the last thing on anyone’s minds. This mindset
often aggravates many system and database administrators because they see
privileges granted to these database users that go against all best practices
and open many security holes. One of these holes is allowing every other user
to see and modify all of the information within the just installed schema. This
is usually done by granting object privileges to PUBLIC and creating PUBLIC
synonyms. By doing this, everything is totally open and your data is vulnerable
and accessible to everyone. I will address this in the next part in this two
part series. However, the most common hole is where the new user created in the
database has privileges that allow that user to alter and see other schema’s
data structures and information. This in fact is the easiest to remedy of the
two solutions.

It does not matter if you develop applications for mass
distribution or internally for your company. You should have the same concerns
when creating a database user. You cannot just grant DBA privileges to everyone
and not expect problems down the road. For instance, I have seen many development
shops that were reduced to one database instance after having separate
development, test, and QA environments. They thought that they could just EXPort
and IMPort under a different schema owner and all would be fine. After merging
all three environments into one, they soon were shocked to realize that
everyone had privileges to see and modify everyone else’s objects. Know when
development, test, and QA was done in the single environment there was some
confusion and uneasiness about the true objects that were being used. As a side
note, in this day of mergers and our global economy, every company should be
concerned with the possibility of either being purchased at some time or having
to merge database resources. Not having a security mechanism in place that
protects your data can and will reduce your abilities for operating in these
environments.

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles