Schema privilege and object privilege using sql grant and Oracle role using business logic.
Here, I denote 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: is the disgruntled employee just about to tender his resignation.
In order to make privilege management easier, Oracle allows the use of roles that consolidate privileges and grant them on an as-needed basis. For making the 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 the Inventory database, he or she can check the current inventory. Ideally, implement privilege management completely as a role. 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 the database used 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 the 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.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.
This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.
