Privileges such as CREATE ANY VIEW and CREATE ANY PROCEDURE include a series of privileges that I qualify as being needed, depending on what the user is trying to do. If the user to whom you are trying to assign the object does not have another object with the same name as the object that you are trying to create, you do not need any of these special privileges. However, if you are trying to create a table with the same name as one that the user already has, you need to have DROP ANY TABLE to get rid of the existing table so that you can replace it with the new one.

As mentioned earlier, the “any” privileges are something that I use every day. Many of them are not particularly harmful. SELECT ANY TABLE may pull in a lot of data, but nothing is destroyed if you make a typing mistake. Many of them I have never used (ALTER ANY TRIGGER, for example). Let’s go over some guidelines that I use when working with these “any” privileges using my DBA account:

•  When deleting database objects, always use the fully qualified name and do not rely on synonyms. It takes only a few extra characters to specify owner.table_name.

•  Use dummy accounts (accounts that are not associated with an end user) to create the objects that you want controlled in the database. You can do it using your DBA account with its CREATE ANY TABLE privilege, but remember, you need to access this dummy account to perform the object privilege grants anyway. An advantage to coding your object creation scripts with the owner.table_name format is that if you are logged into the wrong account when you execute them, it tells you about your mistake. Yes, I have occasionally built a few dozen tables only to realize that I was distracted and put them in the wrong schema.

•  If your data is particularly sensitive to mistakes and you have other functions to perform such as development, consider making a less-privileged account for yourself. Utilize your personal DBA account only when needed and that will minimize the chance that your “any” privileges will do something that you do not want. Also avoid using the SYS and SYSTEM accounts whenever possible because they own too many sensitive objects that you might overwrite.

The Database Maintenance Privileges

Next on the list of system privileges are those that you use to maintain the database itself. These are some of the traditional DBA privileges that you use to create the database, add tablespaces, and so forth (see Table 13.5).

Monitoring Privileges

Finally, there is a privilege used to control access to the auditing utilities that Oracle uses (see Table 13.6). (Actually, there are two, but the AUDIT ANY privilege, which enables you to audit access to database objects of any users, is covered under the “any” privileges.)