| Previous | Table of Contents | Next |
The user privileges are simple and straightforward. The next set of privileges relates to developers who may be working with the system (see Table 13.3). These privileges can be a little trickier in that you may not want to give out all the developer privileges in your instance (there are some rather powerful ones).
| Privilege | Description |
|---|---|
| CREATE CLUSTER | Creates clusters of tables that the developer owns. The developers can also drop clusters that they own. |
| CREATE PROCEDURE | Creates stored procedures, packages, and functions owned by the developer. The developers can also drop any of these objects that they own. |
| CREATE DATABASE LINK | Defines a database link. This feature is similar to a synonym because it is a named pointer to the other database. A key difference is that you store the Oracle ID and password for the remote system as part of the link, thereby establishing access privileges in the remote system. The developers can also drop any database links that they create. |
| CREATE PUBLIC SYNONYM | Creates an alternative name for referencing a database object such as a table or view. Any user in the instance can use this name to make a call for that object. Object privileges are still needed for that user to have access to an object. In certain situations, you may want to give this privilege only to DBAs or certain senior developers. |
| DROP PUBLIC SYNONYM | Deletes an alternative name for referencing a database object that is available to all users in that instance. In certain situations, you may want to give this privilege only to DBAs or certain senior developers. |
| CREATE SEQUENCE | Creates a sequence owned by the developer. The developers can also drop any sequences that they create. |
| CREATE SNAPSHOT | Creates a local copy of a table located on another Oracle instance. The developers can also drop any snapshots that they own. |
| CREATE SYNONYM | Creates a private synonym (for the developer’s use only). The developers can also drop any private synonyms that they own. |
| CREATE TABLE | Creates a table. The developers can also create indexes on and drop tables that they own. |
| CREATE TRIGGER | Creates a trigger (command executed when a specified event occurs) owned by the developer. The developers can also drop triggers that they own. |
| CREATE VIEW | Creates a view owned by the developer. The developers can also drop views that they own. |
| UNLIMITED TABLESPACE | Allows the developers to create objects that consume as much space in any tablespace as is available. This overrides any quotas assigned in that tablespace. You could consider this to be very much like an “any” privilege. |
| CREATE TYPE | Creates a new object type. |
| DROP TYPE | Drops the object type. |
| CREATE LIBRARY | Creates a new object library. |
| DROP LIBRARY | Drops the object library. |
These are the privileges that are normally considered for developers. You may find cause to grant some of the “any” privileges described in the next section or even certain database maintenance privileges to certain developers. However, this is the common list that you should consider and a good place to start.
One of the candidates for the most powerful but dangerous features in the Oracle system is what I call the “any” privileges (see Table 13.4). If you grant CREATE TABLE to developers, they have the power to destroy (via the SQL drop table command) tables that they have created. However, if they had the DROP ANY TABLE privilege, they can destroy any table in the system. This is a serious privilege. DBAs need to think of this every time they execute destructive commands because, by default, they have most of these “any” privileges assigned to them. In many cases, this is needed. What happens when a developer leaves the company and has a lot of personal tables that are no longer needed? The DBA can delete these tables and drop the user from the system. However, be aware that if you drop a table, especially one referenced by a public synonym, you may not get the table you anticipated, and your DROP ANY TABLE privilege allows you to do it. Enough said. Please be careful when you use your “any” privileges.
Caution:
DBA accounts have most of the “any” privileges by default. This enables you to do almost anything in the system. Be extremely careful that your syntax and purpose are correct before issuing a command that takes advantage of your “any” privileges.
| Previous | Table of Contents | Next |