Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 9, 2009

Oracle 11g Object privileges and synonym usage

By James Koopmann

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;
---------- ---------- ---------
      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;
---------- ---------- ---------
      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;
---------- ---------- ---------
      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.

» See All Articles by Columnist James Koopmann

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM