| Previous | Table of Contents | Next |
Figure 13.5 shows how the object privileges apply to the various objects within the database. There is some overlap between system and object privileges. Some objects within the database require system privileges for creation and others require object privileges. The objects use object privileges to restrict access to the appropriate users (see Table 13.1).
)
Figure 13.5. Mapping object privileges to objects.
){kind=link}
| Object | Protections |
|---|---|
| Database | System privilege ALTER DATABASE. |
| Tablespace | System privileges CREATE TABLESPACE, ALTER TABLESPACE, MANAGE TABLESPACE, DROP TABLESPACE. |
| Tables | System privileges CREATE TABLE, CREATE ANY TABLE, ALTER, ALTER ANY TABLE, and DROP ANY TABLE protect the table itself. Object privileges DELETE, INDEX, INSERT, REFERENCES, SELECT, and UPDATE protect the data within the table. System privileges SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, and DELETE ANY TABLE can be used to override the object privileges. |
| Indexes | The system privilege CREATE INDEX gives the user the privilege to create indexes. The object privilege INDEX is required to access the data in a table to build the index. The system privileges CREATE ANY INDEX, ALTER ANY INDEX, and DROP ANY INDEX can override the object privileges. |
| Views | The system privileges CREATE VIEW, CREATE ANY VIEW, and DROP ANY VIEW protect the object itself. Object privileges DELETE, INSERT, SELECT, and UPDATE protect the data within the view. |
| Sequences | The system privilege CREATE SEQUENCE protects the object itself. The object privileges ALTER and SELECT protect the data in the sequence. The system privileges CREATE ANY SEQUENCE, ALTER ANY SEQUENCE, DROP ANY SEQUENCE, and SELECT ANY SEQUENCE can override the object privileges. |
| Procedures and so on | The system privileges CREATE PROCEDURE and CREATE TRIGGER protect the objects themselves. The object privilege EXECUTE protects access to the procedures. The system privileges CREATE ANY PROCEDURE, ALTER ANY PROCEDURE, DROP ANY PROCEDURE, CREATE ANY TRIGGER, ALTER ANY TRIGGER, DROP ANY TRIGGER and EXECUTE ANY PROCEDURE can override other privileges. |
| Synonyms | The system privilege CREATE SYNONYM protects the object itself. Private synonyms are accessible only by their owners and do not have object privileges. The system privileges CREATE ANY SYNONYM and DROP ANY SYNONYM can be used to override other privileges. |
| Public Synonyms | The system privileges CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM protect the object itself. All public synonyms are accessible by all users and therefore there are no object privileges associated with public synonyms. |
| Objects | The system privileges CREATE TYPE, DROP TYPE, CREATE ANY TYPE, DROP ANY TYPE, CREATE LIBRARY, DROP LIBRARY, CREATE ANY LIBRARY, and DROP ANY LIBRARY protect these objects. |
Figure 13.6 illustrates the protection of the objects themselves, and Figure 13.7 shows the protection of the data within the object.
)
Figure 13.6. Protection of the objects themselves.
){kind=link}
)
Figure 13.7. Protection of the data within the object.
){kind=link}
You may feel a little challenged right now with all the things to think about while devising a scheme to protect your data. Let’s add a few more considerations to the challenge. You may have a table to which you have granted access to no one other than yourself. However, any user who has the SELECT ANY TABLE or, worse yet, DELETE ANY TABLE privilege has access to that table. Therefore, you must be very careful with these “any” privileges.
Another thing to consider is Oracle’s capability of restricting access down to the column level on tables. I have never used it for fear of the performance impacts of having to check access on a column-by-column basis, but it is a tool that you can use when it makes sense. Basically, you can give users an object access (SELECT) on only certain columns within the table. It would be a tough system to administer, but it could be necessary.
Table Privileges
The first group to start with is the object privileges associated with tables. These are the fundamental data storage constructs and the type of object privilege grants that make up the vast majority of most systems. These are the privileges that you can grant on tables:
- • ALTER allows the user to change the structure of the table. This includes adding columns and changing the storage parameters associated with the table.
- • DELETE allows the user to delete rows of data from the table. It does not allow the user to delete the table itself.
- • INDEX allows the user to access the data in the table to create an index. This can be important in that Oracle automatically maintains indexes on its tables (updates the index when values in the table change). If a large number of users are creating indexes on tables, your data entry performance will suffer. One interesting feature of this privilege is that it cannot be granted to a role. It can be granted only to individual users.
- • INSERT allows the user to add new rows of data to the table.
- • REFERENCES allows the user to use the table as a foreign key in another table. This links the data in the two tables via one or more columns in each table. This is the other table object privilege that cannot be granted to a role. It can be granted only to individual users.
- • SELECT allows the user to read information from the table. This is the most common privilege granted to the user community. It may be the only privilege granted to users in data warehouses.
- • UPDATE allows the user to change the contents of existing rows of data in the table. It does not allow new rows to be created or existing rows to be deleted.
- • DELETE allows the user to delete rows of data from the table. It does not allow the user to delete the table itself.
| Previous | Table of Contents | Next |