Oracle 11g Object privileges and synonym usage
October 9, 2009
Dont know how many database shops Ive gone into that have no idea of the security breaches that occur across user granted privilege and floating unused synonyms.
Lets not kid ourselves. It isnt 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 Ive 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.
While there are many different types of privileges, the above-mentioned privileges (SELECT, UPDATE, INSERT, DELETE) are simply called object privilegesgiving the authorization to act upon or use a database object such as a table or stored procedure.
For some reason, and the reason Im 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 doesnt 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 wouldnt 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 wont 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
Lets 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 dont 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 Ive 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 cant see an object he cant manipulate the data. Keep those objects a secret until someone actually needs to see them.