Don’t
know how many database shops I’ve gone into that have no idea of the security
breaches that occur across user granted privilege and floating unused synonyms.
Let’s not
kid ourselves. It isn’t often that we actually think too hard about how we have
created users with our Oracle databases. In fact, we often let third party
vendors come in and dictate how users and privileges should be created. Oh the
times I’ve gone browsing database users and found them all granted DBA level
security. Why does this happen? Well the answer is very simple. Not many
developers are DBAs and many DBAs do not partake in the development process. It
is a two way street that requires both development and administration to talk
together and determine what the true access rights should be.
That being said, the question becomes, how can we get a
handle on this. To begin with, the Oracle 2-Day Security Guide points to the
two distinct ways we first grant privileges. The first is simply granting
single and individual privileges to a distinct user. For instance, a DBA might
grant SELECT or UPDATE privileges to user SCOTT to table EMPLOYEE.
SQL> GRANT select,update ON sys.employee TO SCOTT;
Grant succeeded.
This
obviously gives the user SCOTT the ability to not only SELECT information but
also UPDATE it. however, user SCOTT is not able to INSERT or DELETE; unless of
course SCOTT was granted those privileges earlier. This is very important to
realize as we could easily see that there might be a business rule that says:
SCOTT may UPDATE employee information but he may not add or remove employees.
This is the only way to help ensure proper security measures are in place and
that users can accomplish the work they are required to do. No more, no less.
-
EVERY
privilege that is granted to a user should be given consideration and in
accordance to accomplishing a business rule -
NO user should
be granted carte blanche privileges just because an application controls access
While
there are many different types of privileges, the above-mentioned privileges
(SELECT, UPDATE, INSERT, DELETE) are simply called object privileges—giving the
authorization to act upon or use a database object such as a table or stored
procedure.
For some
reason, and the reason I’m going to discuss here, many people are confused with
the proper use of synonyms and privileges. It is very easy to see that the
above granted SELECT privilege of SELECT on the sys.employee table to SCOTT
allows SCOTT to issue the following command:
SQL> SELECT * FROM sys.employee;
EMPNO ENAME JOB
———- ———- ———
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
Moreover,
it is very easy to see that if the SYS schema owner was not specified then user
SCOTT would get an error such as this:
SQL> SELECT * FROM employee;
SELECT * FROM employee
*
ERROR at line 1:
ORA-00942: table or view does not exist
Obviously, the error is because the SELECT statement
doesn’t know where to find the employee table. There is actually some real
beauty in this design within databases that you need to think hard about before
creating synonyms to objects.
It is just as clear that databases could have very easily
written engine code that said find a table name EMPLOYEE without needing the
schema owner. The issue here is quite simply that ownership wouldn’t have mattered.
However, with real databases such as Oracle, we can actually define multiple
tables with the same name of EMPLOYEE. We can create an EMPLOYEE table under
the SYS schema, the SCOTT schema, the WHOMEVER schema.
Synonyms are created to create an abstraction layer
between the user and the SQL so that the schema owner will not need to be
entered. I won’t get into too much detail here but this is really cool as the
same application can be used to point to different sets of tables based on the
underlying synonym creation.
For instance, the SYS user could create a public synonym
that pointed to the SYS.EMPLOYEE table and everyone would immediately point to
the SYS.EMPLOYEE table if a SELECT statement were issued:
SQL> CREATE PUBLIC SYNONYM employee FOR sys.employee;
Synonym created.
Now when
user SCOTT SELECTs from EMPLOYEE he gets:
SQL> SELECT * FROM employee;
EMPNO ENAME JOB
———- ———- ———
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
Let’s say
that SCOTT now wants to use an EMPLOYEE table of a different user, say
JKOOPMANN. User SCOTT could easily create his own synonym (if he has CREATE
SYNONYM and SELECT privileges):
SQL> CREATE SYNONYM employee FOR jkoopmann.employee;
Synonym created.
Now when
he selects from the EMPLOYEE table he gets data from the JKOOPMANN schema:
SQL> SELECT * FROM employee;
EMPNO ENAME JOB
———- ———- ———
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
Verifying
the synonyms within a database is often just as important as understanding the
privileges granted to their underlying tables. You don’t want to assume anything
here and accidentally leave a synonym around that might point to an object. The
reason here is two fold. Not only do you expose the underlying object of
interest but someone could come along and assume that because there is a
synonym defined that a particular user should have access to it. I cannot count
the number of times I’ve seen DBAs do this. They, in their attempt to get an
application up and running will comb the database for “broken” links between an
application user and underlying database objects. Under the scenario described,
they might see a user has access to a synonym but no object privileges and
immediately create a little script that will grant it.
Performing
a sanity check for all the synonyms out on your database and privileges granted
by selecting from the DBA_SYNONYMS and DBA_TAB_PRIVS views can save a lot of
heartache. Just remember privileges are a very powerful mechanism to give
authorization to a user while synonyms help point to the proper objects. Misuse
of either one opens up your database to potential attacks. After all, if a user
can’t see an object he can’t manipulate the data. Keep those objects a secret
until someone actually needs to see them.
