Schema privilege and object privilege using sql grant and Oracle role using business logic.
Here, I denote to the several system and object privileges that could be granted and retracted from separate database user IDs. As remarked former, 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.
Finally in mission-critical environments, system resource preservation is essential. Users should not log on, finish their tasks, and tend to leave their connections open for extended periods of time. Even if they have not finished their respective tasks, they should not be allowed to leave their connection unguarded. Besides causing potential locking problems within the database, this could also lead to serious security branches. As part of a complete security scheme, you need to define profiles and implement them for various users to prevent them from wasting system resources and inducing security hazards.