Oracle 10g Free Training - Managing Roles and Privileges : This tutorial provides the ways and steps of managing database roles and privileges using SQL Plus and Enterprise Manager .

Managing Roles

Similar to creating users, user accounts can be managed either through EM or directly through sqlplus.

Managing Roles through sqlplus

You can use following command to change the password of the user

Figure 9. Change Password Sqlplus Window

Managing Roles through EM

Following window which is similar to figure 5 can be used to modify the user through EM.

Figure 10. Manager Role EM Window

Following window as in figure 11 can be used to change password of role.

Figure 11. Manage Role General Window

Click on Roles Tab in the window to get the following window as in figure 12.

Figure 12. Manage Role Roles Window

Click on the modify button to modify any roles to DBA_ROLE . Following window would appear as shown in figure 12.1.

Figure 12.1 Managing Role Roles Window

Select any of the roles you want to assign to DBA_ROLE and Move button. You can also use Move ALL button to grant all the roles to DBA_ROLE role.
Once you click “OK” button, following window would appear.

Figure 12.2 Managing Role Roles Window

Click on Apply button to grant the selected roles to DBA_ROLE. If you check the Admin Option Checkbox, then the role would be granted with ADMIN OPTION so users with DBA_ROLE can grant Connect Role to other users.

You can also click Show Sql button to actually see the sql query which EM will run to grant these roles.

Figure 12.3 Manage Role SQL Window

Once you click Apply button on figure 12.2, following screen will appear in front of you.

Figure 12.4 Manage Role Granted Window

The Highlighted message above shows that Connect role has been granted to DBA_ROLE.
Similarly you can click on system privileges link in figure 12.4 to grant any system privileges to DBA_ROLE.
Following window will appear in front of you when you click on system privileges link.

Figure 13 Manage Role System Privileges Window

Figure 13.1 Manage Role System Privileges Window

Select any of the system privileges listed in Available System Privileges area and click on move button to move it to selected system privileges area. You can also move all the system privileges using move all button.

Figure 13.2 Manage Role System Privileges Window

We have selected Create Any Procedure privilege in figure13.2. Once you click OK Button following screen will appear in front of you.

Figure 13.3 Manage Role System Privileges Window

Click on Apply button to grant the selected privileges to DBA_ROLE. If you check the Admin Option Checkbox, then the system privilege would be granted with ADMIN OPTION so users with DBA_ROLE can grant Create any procedure system privilege to other users.
You can also click Show Sql button to actually see the sql query which EM will run to grant these privileges.

Figure 13.4 Manage Role System Privileges SQL Window

Once you click on Apply button in figure 13.3 following window will appear in front of you.

Figure 13.4 Manage Role System Privileges Granted Window

Next Page: Oracle 10g Free Training - Managing Roles and Privileges - Page 2