Direct Privilege Management of Oracle Database

Here, I refer to the various system and object privileges that may be granted and revoked from individual database user IDs. As mentioned earlier, always follow the "least privilege" path, where the barest minimum privileges are granted to each database user ID. In a bid to ease your overall administration overhead and maintenance time, you may be tempted to grant unwanted privileges to even new users. However, these users may not understand the importance of security and inadvertently reveal their passwords to other users, who could potentially use them for malicious purposes, typical example: the disgruntled employee just about to tender his resignation.

In order to make privilege management easier, Oracle allows use of roles that consolidate privileges and grant them on an as-needed basis. For making management of roles easier in an enterprise environment consisting of multiple applications and databases, the Oracle8 Security Server allows global roles to be defined, which can be centrally defined and interpreted locally within each database. For instance, you may define a global role called MANAGER consisting of various roles within each database. Thus, the MANAGER global role could have the CHANGE_PAY role granted in the Payroll database, whereas it could have the CHECK_INVENTORY role granted in the Inventory database.

Thus, a new manager joining the company could be easily assigned this single role, and whenever he or she logs onto the Payroll database, he or she can change people's salaries, whereas if he or she logs onto Inventory database, he or she can check the current inventory. Ideally, implement privilege management completely as roles. In earlier Oracle releases, however, EXECUTE privileges on stored procedures granted via roles do not translate at runtime, thus roles and actual privileges in your environment. In any case, understand the scope of database use required by various users (in case they insist on additional privileges beyond what you think is necessary, periodically monitor database use by them to determine their actual needs) and grant/revoke privileges and roles accordingly. Oracle provides a decent audit mechanism to keep track of commands run by various users at various times.

This is especially a very useful feature when tracking suspicious activity within database. (Again if multiple users/applications access the database via a single user ID, the default audit configuration wouldn't help much. In such cases, however, you could write user-defined triggers on various critical tables to log the OSUSER and MACHINE columns from v$session into separate administrative tables. However, this approach would only trap DML activity; DDL would still not be traceable.) However, the audit mechanism involves overhead and could impact performance. Accordingly, use it only when necessary.