Object Privileges  «Prev  Next»

Lesson 4Using Security Manager
ObjectiveGrant Object Privileges by using Security Manager

Using Oracle Security Manager

Granting Object Privileges Using Security Manager in Oracle 12c

Oracle 12c, as part of its comprehensive suite of database management tools, includes Oracle Enterprise Manager (OEM) which, in turn, encompasses the Security Manager. This graphical interface allows Database Administrators (DBAs) to manage user privileges without delving directly into SQL commands. Here’s a systematic guide on how a DBA can grant object privileges using the Security Manager:
  1. Access Oracle Enterprise Manager (OEM):
    • Start the Oracle Enterprise Manager (often referred to as OEM Cloud Control or just Cloud Control) using the appropriate URL, typically formatted as 
      `https://your_server_name:port_number/em`.
    • Log in with a user account that has the necessary administrative privileges to manage security.
  2. Navigate to the Security Manager:
    • Once logged into OEM, from the main menu, select `Target`, and then `Databases`.
    • Choose the appropriate database (in this context, it would be your Oracle 12c database).
    • In the Oracle Database homepage, click on the `Administration` link, followed by the `Security` sub-menu. This will take you to the Security Manager.
  3. Manage Object Privileges:
    • Within Security Manager, select the `Object Privileges` tab.
    • Click on the `Create` button to begin granting new object privileges.
  4. Specify User or Role:
    • In the 'Grantee' section, specify the user or role to which you intend to grant privileges.
  5. Define Object and Privileges:
    • Choose the schema and then the specific object (e.g., table, view) for which you're granting privileges.
    • From the available privileges list, select the ones you wish to grant. Common object privileges include `SELECT`, `INSERT`, `UPDATE`, and `DELETE`.
    • If you wish to allow the user to grant these privileges to other users, check the `Grant Option` checkbox.
  6. Apply Changes:
    • Once you've defined the necessary privileges, click on the `OK` or `Apply` button to finalize and grant the selected object privileges to the chosen user or role.
  7. Verification:
    • It's a best practice to verify the granted privileges. This can be done within the Security Manager by viewing the user's or role’s granted privileges.
    • Additionally, a DBA can run SQL queries against Oracle's data dictionary views such as `DBA_TAB_PRIVS` to confirm that privileges have been appropriately granted.
  8. Audit and Monitor:
    • Regularly review the object privileges using the Security Manager, ensuring that users and roles only have necessary and appropriate access.
    • Monitor the system for any privilege escalations or unauthorized access attempts. Oracle 12c provides robust auditing mechanisms that can be utilized for this purpose.
Oracle 12c's Security Manager in Oracle Enterprise Manager offers a streamlined, intuitive interface for DBAs to manage object privileges. While the graphical interface simplifies many tasks, a seasoned DBA understands the underlying structures and privileges being manipulated, ensuring that the database remains both secure and functional. Proper privilege management is paramount to maintaining data integrity, security, and compliance in an Oracle 12c environment.


Syntax for granting Security Privileges

Although the syntax for granting security privileges is simple, assigning privileges for large numbers of users and objects can be confusing. The Oracle Enterprise Manager (OEM), which was introduced earlier in this course, includes a module specifically designed for handling privileges known as the Security Manager.

Security Manager

The Security Manager module provides a graphical interface that displays all of the information related to Oracle security. You can change user passwords, assign system and object privileges for a user, or even see the roles each user can assume. Roles will be covered extensively later in this course.
You can grant security privileges or take them away, as long as you have the right to perform these actions. The best way to understand Security Manager is to read the steps below, which illustrate the process of granting a privilege for a table.

Using Security Manager

  1. Once you select Security Manager from the Oracle Enterprise Manager menu, log in, but this time log in as user SCOTT with the password TIGER and then click the OK button. You want to use Security Manager as user SCOTT because your task will be to grant access to some of SCOTT's tables to another user.
  2. You can see that Security Manager uses the now familiar OEM interface. On the left is a listing of the different categories of security entities within the Oracle database, users, roles, and profiles. Click the plus sign to the left of the Users entry to expand the list of users.
  3. You will want to assign a privilege for the user named "DEMO", so click the corresponding entry under the Users in the left-hand list box.
  4. Once you select a use, a tabbed box appears on the right listing all the security options for the user. You will want to assign an object privilege, so click the tab labeled "Object Privileges"
  5. When you bring up the tab for object privileges, you have a list of schemas that contain objects, and that you have the privilege to view. Scroll down the list to get to the schema for SCOTT. (Just click the lower part of the scrollbar and we will scroll for you.)
  6. When you see the schema for user SCOTT, click the plus sign to the left of the entry to show the list of objects for the schema.
  7. You will want to assign a privilege for one of SCOTT's tables, so click the plus sign to the left of the Tables entry.
  8. You can see a list of SCOTT's tables. Click the A_TABLE that you created earlier in this course.
  9. As soon as you select a table, you see a list of object privileges appear in the upper right corner of the tabbed window. To assign the SELECT privilege for this table to user DEMO (the user currently selected in the left-hand list box) click the SELECT entry in the list of privileges.
  10. To add the privilege for the DEMO user, simply click the down arrow in the middle of the tabbed list box.
  11. Once you click the arrow, you can see that the associated object privilege appears in the lower part of the tabbed window. To complete the assignment, simply click the Apply button in the bottom of the Object Privileges tab.
  12. The SELECT privilege for the A_TABLE in SCOTT's schema has been granted for the user DEMO. To check the results of your work, click the plus sign to the left of the DEMO entry in the left-hand list box.
  13. Then click the entry labeled Object Privileges for the user DEMO.
  14. You can see the privilege you just assigned in the right-hand list box. The privilege has not been granted with the GRANT OPTION, so the second entry in the box is NO.


Set Up the IT Security Manager Job Role

Provision the IT Security Manager job role with roles for user and role management.
  1. Using the OIM Administrator user name and password, sign in to Oracle Identity Manager (OIM).
  2. Open the IT Security Manager job role's attributes and use the Hierarchy tab to add the User Identity Administrators role and the Role Administrators role in the OIM Roles category using the Add action. Use the Delegated Administration menu to search for the Xellerate Users organization and assign it to the IT Security Manager role. Refer to the Oracle Fusion Middleware User's Guide for Oracle Identity Manager.

Prerequisite Tasks for Security Administration

Sign into Oracle Fusion Applications for the first time with the Installation Super User account to synchronize LDAP users with HCM user management and create an IT security manager user account and provision it with the IT Security Manager role. For environments that are not in Oracle Cloud, use the super user account that was created during installation to sign in for the first time.
  1. Installation establishes the super user account. Refer to the Oracle Fusion Applications Installation Guide.
  2. Oracle provides an initial user for accessing your services in Oracle Cloud. For more information, refer to "Oracle Cloud Application Services Security: Explained" in Oracle Cloud documentation.
  3. Synchronize LDAP users with HCM user management by performing the Run User and Roles Synchronization Process task. Monitor completion of the predefined Enterprise Scheduler process called Retrieve Latest LDAP Changes.
  4. Refer to information about creating person records in Oracle Fusion Applications Workforce Development Implementation Guide, or refer to the Oracle Fusion Middleware User's Guide for Oracle Identity Manager.
As a security guideline, provision a dedicated security professional with the IT Security Manager role as soon as possible after initial security setup and revoke that role from users provisioned with the Application Implementation Consultant role. If entitled to do so, see Security Tasks and Oracle Fusion Applications: How They Fit Together for details about provisioning the IT security manager.

Required Security Administration Tasks

Establish at least one implementation user and provision that user with sufficient access to set up the enterprise for all integrated Oracle Fusion Middleware and all application pillars or partitions.
  1. Perform the initial security tasks. If entitled to do so, see Initial Security Administration: Critical Choices.
    Sign in to Oracle Fusion Applications using the IT security manager's or administrator's user name and password, and create and provision users who manage your implementation projects and set up enterprise structures by performing the Create Implementation Users task. Refer to the Oracle Fusion Middleware User's Guide for Oracle Identity Manager.
    Create a data role for implementation users who will set up HCM that grants access to data in secured objects required for performing HCM setup steps. Provision the implementation user with this View All data role.
  2. For an overview of security tasks from the perspective of an applications administrator, refer to the Oracle Fusion Applications Administrator's Guide

The next lesson shows how to list the privileges granted on a table.