Each Oracle user defined on the database can have one or more of over 80 system-level privileges. These privileges control on a very fine level the right to execute SQL commands. The database administrator assigns system privileges either directly to Oracle user accounts or to roles. The roles are then assigned to the Oracle user accounts.
For example, before I can create a trigger on a table (even if I own the table as an Oracle user), I must have a system privilege called CREATE TRIGGER either assigned to my Oracle user account or assigned to a role given to the user account.
The CREATE SESSION privilege is another frequently used system-level privilege. In order to make a connection to the database, an Oracle account must have the CREATE SESSION system-level privilege assigned to it. This gives the account the privilege to make connections to the database.
Object-Level Privileges
Object-level privileges provide the capability to perform a particular type of action (select, insert, update, delete, and so on) on a specific object. The owner of the object has full control over the object and can perform any action on it; he doesn't need to have object-level privileges assigned to him. In fact, the owner of the object is the Oracle user who grants object-level privileges to others.
For example, if the user who owns a table wants another user to select and insert rows from his table (but not update or delete), he grants the select and insert object-level privileges on that table to the other user.
You can assign object-level privileges either directly to users or to roles that are then assigned to one or more Oracle user accounts.
Users and Roles
A role is a type of object that you can use to simplify the administration of system and object-level privileges. Instead of assigning privileges directly to user accounts, you can assign the privileges to roles that are then assigned to users.
Roles are essentially groupings of system and object-level privileges. They make the administration of privileges much easier because you can configure the privileges for a particular type of user once and assign those privileges to a role. When a user needs that set of privileges, you can use a single role assignment command to set that user up. Without the use of roles, you'd need to issue many commands for each of the different privileges required.
In addition, you can set up different roles with the correct privileges even though you don't yet have Oracle user accounts that require those assignments. You can assign a role to another role, building hierarchies of roles. Also, you can protect a role with a password that the user must supply when he wants to enable the role.
As already discussed, a physical database could contain many Oracle user accounts that are protected by passwords. You must supply the username and password regardless of which tool you use to gain access to the database. Roles are not the same as Oracle users; you cannot connect to the database by supplying a role name and password.
Auditing
Oracle's auditing mechanism provides three types of audit trails. One audit trail tracks which system privileges are used. Statement auditing keeps track of which SQL statements are used without regard to specific objects. Object-level auditing audits access to specific objects. You can initiate these audit trails to track when the statements succeed, when they fail, or both, so that all accesses are audited. You can use auditing to keep track of anyone attempting to break into the system.
In addition, you can set up how all the different types of auditing record the entries. The audit trail can record one entry per operation regardless of how many attempts are made on the operation during the connection session. Alternatively, request one entry in the audit trail for every attempt (successful or not) on the operation during the session.
If it's set up and enabled, the audit trail keeps the audit information in a data dictionary table owned by the user SYS. This table indicates the operation being audited, the user performing the operation, and the date and time of the operation. Oracle provides a set of data dictionary views to make the information in the dictionary audit table more meaningful. Although the audit trail is implemented in a data dictionary table, it keeps the insertion of rows in the audit trail even if the user rolls back his transaction.
The database administrator can clear out or archive the audit trail periodically.
For example, before I can create a trigger on a table (even if I own the table as an Oracle user), I must have a system privilege called CREATE TRIGGER either assigned to my Oracle user account or assigned to a role given to the user account.
The CREATE SESSION privilege is another frequently used system-level privilege. In order to make a connection to the database, an Oracle account must have the CREATE SESSION system-level privilege assigned to it. This gives the account the privilege to make connections to the database.
Object-Level Privileges
Object-level privileges provide the capability to perform a particular type of action (select, insert, update, delete, and so on) on a specific object. The owner of the object has full control over the object and can perform any action on it; he doesn't need to have object-level privileges assigned to him. In fact, the owner of the object is the Oracle user who grants object-level privileges to others.
For example, if the user who owns a table wants another user to select and insert rows from his table (but not update or delete), he grants the select and insert object-level privileges on that table to the other user.
You can assign object-level privileges either directly to users or to roles that are then assigned to one or more Oracle user accounts.
Users and Roles
A role is a type of object that you can use to simplify the administration of system and object-level privileges. Instead of assigning privileges directly to user accounts, you can assign the privileges to roles that are then assigned to users.
Roles are essentially groupings of system and object-level privileges. They make the administration of privileges much easier because you can configure the privileges for a particular type of user once and assign those privileges to a role. When a user needs that set of privileges, you can use a single role assignment command to set that user up. Without the use of roles, you'd need to issue many commands for each of the different privileges required.
In addition, you can set up different roles with the correct privileges even though you don't yet have Oracle user accounts that require those assignments. You can assign a role to another role, building hierarchies of roles. Also, you can protect a role with a password that the user must supply when he wants to enable the role.
As already discussed, a physical database could contain many Oracle user accounts that are protected by passwords. You must supply the username and password regardless of which tool you use to gain access to the database. Roles are not the same as Oracle users; you cannot connect to the database by supplying a role name and password.
Auditing
Oracle's auditing mechanism provides three types of audit trails. One audit trail tracks which system privileges are used. Statement auditing keeps track of which SQL statements are used without regard to specific objects. Object-level auditing audits access to specific objects. You can initiate these audit trails to track when the statements succeed, when they fail, or both, so that all accesses are audited. You can use auditing to keep track of anyone attempting to break into the system.
In addition, you can set up how all the different types of auditing record the entries. The audit trail can record one entry per operation regardless of how many attempts are made on the operation during the connection session. Alternatively, request one entry in the audit trail for every attempt (successful or not) on the operation during the session.
If it's set up and enabled, the audit trail keeps the audit information in a data dictionary table owned by the user SYS. This table indicates the operation being audited, the user performing the operation, and the date and time of the operation. Oracle provides a set of data dictionary views to make the information in the dictionary audit table more meaningful. Although the audit trail is implemented in a data dictionary table, it keeps the insertion of rows in the audit trail even if the user rolls back his transaction.
The database administrator can clear out or archive the audit trail periodically.
