A View of Creating an Oracle User - Part I
June 2, 2005
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.