user security administration

1. User Security Administration Oracle 10g

2. CAP364 2 Ebtisam Alabdulqader

3. Database User Accounts A database user account is a means to organize the ownership of and access to database objects. Database account used to: Avoid potential security holes. Allows individual actions to be audited. CAP364 3 Ebtisam Alabdulqader

4. Database User Accounts Each database user has a database account. To access the database, a user must: Specify a valid database user account. Successfully authenticate as required by that user account. Users accounts are usually created by a database administrator or a person specifically assigned to create accounts and administer security. CAP364 4 Ebtisam Alabdulqader Database User Accounts (continued) A database user is not necessarily a person. The database user can be a device, an application, or just a way to group database objects for security purposes. It is a common practice to create a user that owns the database objects of a particular application, such as HR. Database User Accounts (continued) A database user is not necessarily a person. The database user can be a device, an application, or just a way to group database objects for security purposes. It is a common practice to create a user that owns the database objects of a particular application, such as HR.

5. Database User Accounts Each database user account has: A unique username. An authentication method. (Password, External or Global) A default tablespace. A temporary tablespace. A user profile. A consumer group. A lock status. CAP364 5 Ebtisam Alabdulqader Database User Accounts (continued) Each user account has: A unique username: Usernames cannot exceed 30 bytes, cannot contain special characters, and must start with a letter. An authentication method: Authentication means verifying the identity of someone (a user, device, or other entity) who wants to use data, resources, or applications. Validating that identity establish a trust relationship for further interaction. Oracle Database 10g supports several authentication methods like password, external and global methods. Password is the most common authentication method and must be supplied when the user attempts to  establish a connection.  External authentication is authentication by the operating system and users can connect to the Oracle  database without specifying a username or password. Global authentication (which is a strong authentication) allows users to be identified through the use of  biometrics, certificates, token devices... A default tablespace: This is a place where the user creates objects if the user does not specify a tablespace in the creation of an object.  ( Note that having a default tablespace does not imply that the user has the privilege of creating objects in that  tablespace, nor does the user have a quota of space within that tablespace in which to create objects. Both these  are granted separately. ) A temporary tablespace: This is a place where temporary objects are created on behalf of the user by the instance, such as sorts and  temporary tables. A user profile: This is a set of resource and password restrictions assigned to the user. A consumer group: Consumer groups are groups of users, or sessions, that are grouped together based on their processing needs. A lock status: Users can access only “unlocked” accounts. (User Account status could be: Open – Locked – Expired )Database User Accounts (continued) Each user account has: A unique username: Usernames cannot exceed 30 bytes, cannot contain special characters, and must start with a letter. An authentication method: Authentication means verifying the identity of someone (a user, device, or other entity) who wants to use data, resources, or applications. Validating that identity establish a trust relationship for further interaction. Oracle Database 10g supports several authentication methods like password, external and global methods. Password is the most common authentication method and must be supplied when the user attempts to establish a connection. External authentication is authentication by the operating system and users can connect to the Oracle database without specifying a username or password. Global authentication (which is a strong authentication) allows users to be identified through the use of biometrics, certificates, token devices... A default tablespace: This is a place where the user creates objects if the user does not specify a tablespace in the creation of an object. ( Note that having a default tablespace does not imply that the user has the privilege of creating objects in that tablespace, nor does the user have a quota of space within that tablespace in which to create objects. Both these are granted separately. ) A temporary tablespace: This is a place where temporary objects are created on behalf of the user by the instance, such as sorts and temporary tables. A user profile: This is a set of resource and password restrictions assigned to the user. A consumer group: Consumer groups are groups of users, or sessions, that are grouped together based on their processing needs. A lock status: Users can access only “unlocked” accounts. (User Account status could be: Open – Locked – Expired )

6. Predefined Accounts: SYS and SYSTEM The SYS account is granted the DBA role and: Has all privileges with ADMIN OPTION. Owns the data dictionary. Is required for startup, shutdown, and some maintenance commands. (Granted the SYSDBA privilege) The SYSTEM account is granted the DBA role. CAP364 6 Ebtisam Alabdulqader Predefined Accounts: SYS and SYSTEM Certain user accounts are automatically included when you create your database (such as SYS and SYSTEM) but for security reasons, most of these accounts are locked and expired. Most of the predefined accounts are administrative accounts. The SYS and SYSTEM accounts are automatically created and granted the database administrator (DBA) role. The SYS account in addition has all privileges with ADMIN OPTION and owns the data dictionary. SYS account granted the SYSDBA, so to connect to the SYS account, you must connect AS SYSDBA. Only “privileged” users, who are granted the SYSDBA or SYSOPER privilege, are allowed to start up and shut down the database instance. The SYSTEM account is granted the DBA role by default, but not the SYSDBA privilege. The SYS and SYSTEM accounts are required accounts in the database; they cannot be dropped. Applying the principle of least privilege, these accounts are not used for routine operations. Predefined Accounts: SYS and SYSTEM Certain user accounts are automatically included when you create your database (such as SYS and SYSTEM) but for security reasons, most of these accounts are locked and expired. Most of the predefined accounts are administrative accounts. The SYS and SYSTEM accounts are automatically created and granted the database administrator (DBA) role. The SYS account in addition has all privileges with ADMIN OPTION and owns the data dictionary. SYS account granted the SYSDBA, so to connect to the SYS account, you must connect AS SYSDBA. Only “privileged” users, who are granted the SYSDBA or SYSOPER privilege, are allowed to start up and shut down the database instance. The SYSTEM account is granted the DBA role by default, but not the SYSDBA privilege. The SYS and SYSTEM accounts are required accounts in the database; they cannot be dropped. Applying the principle of least privilege, these accounts are not used for routine operations.

7. Schema Schema is a collection of database objects that are owned by a database user. Include tables, views, stored procedures, indexes, and everything that the user creates in the database. Has the same name as the user account. CAP364 7 Ebtisam Alabdulqader

8. Privileges A privilege is a right to execute a particular operation (type of SQL statement) or to access another user’s object. There are two types of user privileges: System: Enables users to perform particular actions in the database. Object: Enables users to access and manipulate a specific object. CAP364 8 Ebtisam Alabdulqader Privileges The Oracle database enables you to control what users can or cannot do within the database. Privileges are divided into two categories: System privileges allow a user to perform a particular database operation. (Granted only to and by administrators) ( For example, the privilege to create user is a system privilege. SYSDBA and SYSOPER: These system privileges allow you to shut  down, startup, and perform recovery and other administrative tasks in the database. SYSOPER privilege allows a user to perform basic  operational tasks, but without the ability to look at user data. SYSDBA privilege additionally authorizes incomplete recovery and the  deletion of a database. ) When given with ADMIN OPTION the user will be allowed to grant other users the same system privilege.  System privileges can be granted by the administrator or by someone who explicitly gives permission to administer the privilege. Object privileges allow a user to perform a particular action on a specific object; such as a table, view, procedure or function. Without specific permission, users can access only their own objects.  When given with GRANT OPTION the user will be allowed to grant other users the same object privilege. Object privileges can be granted by the owner of an object, by the administrator, or by someone who has been explicitly given permission  to grant privileges on the object.Privileges The Oracle database enables you to control what users can or cannot do within the database. Privileges are divided into two categories: System privileges allow a user to perform a particular database operation. (Granted only to and by administrators) ( For example, the privilege to create user is a system privilege. SYSDBA and SYSOPER: These system privileges allow you to shut down, startup, and perform recovery and other administrative tasks in the database. SYSOPER privilege allows a user to perform basic operational tasks, but without the ability to look at user data. SYSDBA privilege additionally authorizes incomplete recovery and the deletion of a database. ) When given with ADMIN OPTION the user will be allowed to grant other users the same system privilege. System privileges can be granted by the administrator or by someone who explicitly gives permission to administer the privilege. Object privileges allow a user to perform a particular action on a specific object; such as a table, view, procedure or function. Without specific permission, users can access only their own objects. When given with GRANT OPTION the user will be allowed to grant other users the same object privilege. Object privileges can be granted by the owner of an object, by the administrator, or by someone who has been explicitly given permission to grant privileges on the object.

9. Roles CAP364 9 Ebtisam Alabdulqader Roles In most systems, it is too time-consuming to grant necessary privileges to each user individually, and there is too great a chance of error. The Oracle software provides easy and controlled privilege management through roles. Predefined Roles are defined automatically for Oracle databases during database creation. e.g. CONNECT role allows users to connect to the database and create database objects in their own schemas, and granted automatically to any  user created with Enterprise Manager DBA role holds all system privileges, several other roles and does not include the SYSDBA or SYSOPER system privileges;  Do NOT grant to non-administrators. Roles can be granted with: Admin Option if this user is allowed to grant other users the same role. Default option if this user is allowed to make the role assigned to the user at connect time. Deselect it makes a role non-default and the user must explicitly enable the role before the role’s privileges can be exercised. Benefits of Roles: Easier privilege management: easier than granting the same set of privileges to several users, you can grant the privileges to a role,  and then grant that role to each user. Dynamic privilege management: If the privileges associated with a role are modified, all the users who are granted the role acquire the  modified privileges automatically and immediately. Selective availability of privileges: Roles can be enabled and disabled to turn privileges on and off temporarily.  Enabling a role can also be used to verify that a user has been granted that role.  A role can require a password to be enabled.Roles In most systems, it is too time-consuming to grant necessary privileges to each user individually, and there is too great a chance of error. The Oracle software provides easy and controlled privilege management through roles. Predefined Roles are defined automatically for Oracle databases during database creation. e.g. CONNECT role allows users to connect to the database and create database objects in their own schemas, and granted automatically to any user created with Enterprise Manager DBA role holds all system privileges, several other roles and does not include the SYSDBA or SYSOPER system privileges; Do NOT grant to non-administrators. Roles can be granted with: Admin Option if this user is allowed to grant other users the same role. Default option if this user is allowed to make the role assigned to the user at connect time. Deselect it makes a role non-default and the user must explicitly enable the role before the role’s privileges can be exercised. Benefits of Roles: Easier privilege management: easier than granting the same set of privileges to several users, you can grant the privileges to a role, and then grant that role to each user. Dynamic privilege management: If the privileges associated with a role are modified, all the users who are granted the role acquire the modified privileges automatically and immediately. Selective availability of privileges: Roles can be enabled and disabled to turn privileges on and off temporarily. Enabling a role can also be used to verify that a user has been granted that role. A role can require a password to be enabled.

10. Assigning Privileges to Roles andRoles to Users CAP364 10 Ebtisam Alabdulqader Assigning Privileges to Roles and Roles to Users In the slide example, the HR_CLERK role is granted the SELECT and UPDATE privileges on the employees table. The HR_MGR role is granted the DELETE and INSERT privileges on the employees table and the HR_CLERK role. The manager is granted the HR_MGR role and can now select, delete, insert, and update the employees table.Assigning Privileges to Roles and Roles to Users In the slide example, the HR_CLERK role is granted the SELECT and UPDATE privileges on the employees table. The HR_MGR role is granted the DELETE and INSERT privileges on the employees table and the HR_CLERK role. The manager is granted the HR_MGR role and can now select, delete, insert, and update the employees table.

11. Profiles and Users CAP364 11 Ebtisam Alabdulqader Profiles and Users User Profile impose a named set of resource limits on database usage and instance resources (CPU, Memory/Network and DISK I/O). Profiles cannot impose resource limitations on users unless the RESOURCE_LIMIT initialization parameter is set to TRUE. With RESOURCE_LIMIT at its default value of FALSE, profile limitations are ignored. User Profile also manage the account status and place limitations on users’ passwords (History, Complexity, Locking and expiration). The DEFAULT profile is quite liberal in its resource specifications and does not provide tight restrictions on password usage.Profiles and Users User Profile impose a named set of resource limits on database usage and instance resources (CPU, Memory/Network and DISK I/O). Profiles cannot impose resource limitations on users unless the RESOURCE_LIMIT initialization parameter is set to TRUE. With RESOURCE_LIMIT at its default value of FALSE, profile limitations are ignored. User Profile also manage the account status and place limitations on users’ passwords (History, Complexity, Locking and expiration). The DEFAULT profile is quite liberal in its resource specifications and does not provide tight restrictions on password usage.

12. Users Quota CAP364 12 Ebtisam Alabdulqader Users Quota By default, a user has no quota on any of the tablespaces. You do not need quota on an assigned temporary tablespace. Users Quota By default, a user has no quota on any of the tablespaces. You do not need quota on an assigned temporary tablespace.

13. Users Administration through the Enterprise Manager Oracle 10g Log in to Enterprise Manager Database Console using SYS account or user account that specifically assigned to create accounts and administer security.Log in to Enterprise Manager Database Console using SYS account or user account that specifically assigned to create accounts and administer security.

14. Administrating User Security CAP364 14 Ebtisam Alabdulqader Administrating User Security In Enterprise Manager Database Control click Administration tab,then from the Security section on the Administration page you can manage: Users – Roles – Profiles. Users: allows you manage the list of database users and their setting (i.e. users privileges, users quota, assigned roles and profiles). Roles: allows you manage the list of roles and their assigned privileges and roles. Profiles: allows you manage the list of profiles and their setting for resources consumption and password.Administrating User Security In Enterprise Manager Database Control click Administration tab,then from the Security section on the Administration page you can manage: Users – Roles – Profiles. Users: allows you manage the list of database users and their setting (i.e. users privileges, users quota, assigned roles and profiles). Roles: allows you manage the list of roles and their assigned privileges and roles. Profiles: allows you manage the list of profiles and their setting for resources consumption and password.

15. Users Property Page CAP364 15 Ebtisam Alabdulqader Users Property Page Click Users in the Security section on the Administration page to access the Users property page. From this page you can view, add, edit, or delete database users. In the Users property page, click Create. Then Create User General page appears.Users Property Page Click Users in the Security section on the Administration page to access the Users property page. From this page you can view, add, edit, or delete database users. In the Users property page, click Create. Then Create User General page appears.

16. Creating a User - General Tab CAP364 16 Ebtisam Alabdulqader Creating a User – General Tab In the Create User General page provide the required information: Mandatory information (marked with an asterisk), such as Name. Select a specific Profile (DEFAULT profile mostly used). Select authentication method to use (Password – External – General).  When setting up a password, you can expire the password immediately, which forces the user to change the password after  first logging in. Assign a default tablespace. If you do not choose one, then the system-defined default permanent tablespace is used. (Click the flashlight icon to browse for tablespaces.) Assign a temporary tablespace. if you do not specify one, then the system-defined temporary tablespace is used. (Click the flashlight icon to browse for tablespaces.) Click Roles tab when finished from General page, then the Roles page appears. Creating a User – General Tab In the Create User General page provide the required information: Mandatory information (marked with an asterisk), such as Name. Select a specific Profile (DEFAULT profile mostly used). Select authentication method to use (Password – External – General). When setting up a password, you can expire the password immediately, which forces the user to change the password after first logging in. Assign a default tablespace. If you do not choose one, then the system-defined default permanent tablespace is used. (Click the flashlight icon to browse for tablespaces.) Assign a temporary tablespace. if you do not specify one, then the system-defined temporary tablespace is used. (Click the flashlight icon to browse for tablespaces.) Click Roles tab when finished from General page, then the Roles page appears.

17. Creating a User - Roles Tab CAP364 17 Ebtisam Alabdulqader Creating a User - Roles Tab In the Create User Roles page appears. Click Modify. The Modify Roles page is displayed. Select the desired role under Available Roles list and move it under Selected Roles. When you have assigned all appropriate roles, click the OK. The Create User Roles page is displayed again with the selected roles. Creating a User - Roles Tab In the Create User Roles page appears. Click Modify. The Modify Roles page is displayed. Select the desired role under Available Roles list and move it under Selected Roles. When you have assigned all appropriate roles, click the OK. The Create User Roles page is displayed again with the selected roles.

18. Creating a User - Roles Tab CAP364 18 Ebtisam Alabdulqader Creating a User - Roles Tab (continued) In the Create User Roles page: Select the Admin Option check box if this user is allowed to grant other users the same role. Select the Default check box if this user is allowed to make the role assigned to the user at connect time. Click System Privileges tab, then the System Privileges page appears. Creating a User - Roles Tab (continued) In the Create User Roles page: Select the Admin Option check box if this user is allowed to grant other users the same role. Select the Default check box if this user is allowed to make the role assigned to the user at connect time. Click System Privileges tab, then the System Privileges page appears.

19. Creating a User - System Privileges CAP364 19 Ebtisam Alabdulqader Creating a User - System Privileges Tab In the System Privileges page appears. Click Modify. The Modify System Privileges page is displayed. Select the appropriate privilege from the list of available system privileges, and click Move to move it to the Selected System Privileges list. Then click OK, then the System Privileges page appears again with the selected system privileges. Note: Granting a privilege with the ANY clause means that the privilege crosses schema lines. For example, the CREATE TABLE privilege allows you to create a table but only within your own schema. The SELECT ANY TABLE privilege allows you to select from tables owned by other users.Creating a User - System Privileges Tab In the System Privileges page appears. Click Modify. The Modify System Privileges page is displayed. Select the appropriate privilege from the list of available system privileges, and click Move to move it to the Selected System Privileges list. Then click OK, then the System Privileges page appears again with the selected system privileges. Note: Granting a privilege with the ANY clause means that the privilege crosses schema lines. For example, the CREATE TABLE privilege allows you to create a table but only within your own schema. The SELECT ANY TABLE privilege allows you to select from tables owned by other users.

20. Creating a User - System Privileges CAP364 20 Ebtisam Alabdulqader Creating a User - System Privileges Tab (continued) Now in the System Privileges page. Selecting the Admin Option check box enables the user to administer that privilege and grant it to other users. Carefully consider security requirements before granting system permissions. Click Object Privileges tab, then the Object Privileges page appears.Creating a User - System Privileges Tab (continued) Now in the System Privileges page. Selecting the Admin Option check box enables the user to administer that privilege and grant it to other users. Carefully consider security requirements before granting system permissions. Click Object Privileges tab, then the Object Privileges page appears.

21. Creating a User - Object Privileges CAP364 21 Ebtisam Alabdulqader Creating a User - Object Privileges Tab In the Object Privileges page appears, select the Object Type you want to grant privileges on (e.g.Table) from the pull-down menu, and click Add. The Add Table Object Privileges page is displayed. Choose the objects you want to grant privileges on by either: entering <username/schemaname.objectname> or selecting them from the list (Click the flashlight icon to browse for objects.). Next, select the appropriate privileges from the Available Privileges list, and click Move. When you have finished selecting privileges, click OK. Then the Object Privileges page appears again with the selected Object privileges.Creating a User - Object Privileges Tab In the Object Privileges page appears, select the Object Type you want to grant privileges on (e.g.Table) from the pull-down menu, and click Add. The Add Table Object Privileges page is displayed. Choose the objects you want to grant privileges on by either: entering <username/schemaname.objectname> or selecting them from the list (Click the flashlight icon to browse for objects.). Next, select the appropriate privileges from the Available Privileges list, and click Move. When you have finished selecting privileges, click OK. Then the Object Privileges page appears again with the selected Object privileges.

22. Creating a User - Object Privileges CAP364 22 Ebtisam Alabdulqader Creating a User - Object Privileges Tab (continued) Now in the Object Privileges page, select the Grant check box if this user is allowed to grant other users the same access. Click Quotas tab, then the Quotas page appears.Creating a User - Object Privileges Tab (continued) Now in the Object Privileges page, select the Grant check box if this user is allowed to grant other users the same access. Click Quotas tab, then the Quotas page appears.

23. Creating a User - Quotas Tab CAP364 23 Ebtisam Alabdulqader Creating a User – Quotas Tab In the Quotas page appears, by default, a user has no quota on any of the tablespaces. You have two options for providing a user quota on a tablespace: Unlimited: This allows the user to use as much space as is available in the tablespace.  When this option selected, the quota value will set automatically to -1. Value: This is a number of kilobytes or megabytes that the user can use. This does not guarantee that the space is set aside for the user.  This value can be larger or smaller than the current space that is available in the tablespace. Notes: You must not provide quota to users on the SYSTEM or SYSAUX tablespace.  Typically, only the SYS and SYSTEM users must be able to create objects in the SYSTEM or SYSAUX tablespace. You do not need quota on an assigned temporary tablespace or any undo tablespaces. UNLIMITED TABLESPACE system privilege overrides all individual tablespace quotas and gives the user unlimited  quota on all tablespaces, including SYSTEM and SYSAUX. This privilege must be granted with caution. Click Ok when you have finished. The Users property page is displayed with an Update message confirming the creation of your user. Creating a User – Quotas Tab In the Quotas page appears, by default, a user has no quota on any of the tablespaces. You have two options for providing a user quota on a tablespace: Unlimited: This allows the user to use as much space as is available in the tablespace. When this option selected, the quota value will set automatically to -1. Value: This is a number of kilobytes or megabytes that the user can use. This does not guarantee that the space is set aside for the user. This value can be larger or smaller than the current space that is available in the tablespace. Notes: You must not provide quota to users on the SYSTEM or SYSAUX tablespace. Typically, only the SYS and SYSTEM users must be able to create objects in the SYSTEM or SYSAUX tablespace. You do not need quota on an assigned temporary tablespace or any undo tablespaces. UNLIMITED TABLESPACE system privilege overrides all individual tablespace quotas and gives the user unlimited quota on all tablespaces, including SYSTEM and SYSAUX. This privilege must be granted with caution. Click Ok when you have finished. The Users property page is displayed with an Update message confirming the creation of your user.

24. Creating a User - Confirmation CAP364 24 Ebtisam Alabdulqader Creating a User – Confirmation In the Users property page, you can see the new user listed on the page. Creating a User – Confirmation In the Users property page, you can see the new user listed on the page.

25. Unlocking a User Account CAP364 25 Ebtisam Alabdulqader Unlocking a User Account If an account is locked, the user cannot log in to the database. During installation, you were given the option to unlock and reset the Oracle supplied database user accounts. If you have not chosen to unlock the user accounts at that time, you can unlock the users and reset the passwords using Users page. To Unlock User: Select the user on the Users page and select Unlock User from the pull-down menu in the Actions box, then click Go. When the Confirmation page appears, click Yes to confirm the unlock operation. The Users page is displayed with an Update message confirming the unlocking of your user. Unlocking a User Account If an account is locked, the user cannot log in to the database. During installation, you were given the option to unlock and reset the Oracle supplied database user accounts. If you have not chosen to unlock the user accounts at that time, you can unlock the users and reset the passwords using Users page. To Unlock User: Select the user on the Users page and select Unlock User from the pull-down menu in the Actions box, then click Go. When the Confirmation page appears, click Yes to confirm the unlock operation. The Users page is displayed with an Update message confirming the unlocking of your user.

26. Unlocking a User Account andResetting the Password CAP364 26 Ebtisam Alabdulqader Unlocking a User Account and Resetting the Password Alternatively, to Unlock User and reset the password when its EXPIRED: Select the user and click Edit. The Edit User General page is displayed. Unlocking a User Account and Resetting the Password Alternatively, to Unlock User and reset the password when its EXPIRED: Select the user and click Edit. The Edit User General page is displayed.

27. Unlocking a User Account andResetting the Password CAP364 27 Ebtisam Alabdulqader Unlocking a User Account and Resetting the Password (continued) In the Edit User General page, enter a new password in the Password and Confirm Password fields. Select the Unlocked check box. Click Apply to reset the password and unlock the user account. Then the Edit User General page is displayed with an update message confirming the change to your user. Select the Users link to return to the Users page.Unlocking a User Account and Resetting the Password (continued) In the Edit User General page, enter a new password in the Password and Confirm Password fields. Select the Unlocked check box. Click Apply to reset the password and unlock the user account. Then the Edit User General page is displayed with an update message confirming the change to your user. Select the Users link to return to the Users page.

28. Roles Page CAP364 28 Ebtisam Alabdulqader Roles Page Click Roles in the Security section on the Administration page. The Roles page appears. There are a number of predefined roles. From this page you can create, edit, view, or delete roles. In Roles property page, click Create to create a new Role. The Create Role General page appears. Roles Page Click Roles in the Security section on the Administration page. The Roles page appears. There are a number of predefined roles. From this page you can create, edit, view, or delete roles. In Roles property page, click Create to create a new Role. The Create Role General page appears.

29. Creating a Role CAP364 29 Ebtisam Alabdulqader Creating a Role In the Create Role General page, enter role name in the Name field and you can add authentication method which will be require before it can be set. The default authentication for a role is None. Click Roles tab, to add sub-roles to this role. (As done during creating a User) Click System Privileges tab, to add system privileges to this role. (As done during creating a User) Click Object Privileges tab, to add object privileges to this role. (As done during creating a User) Finally, click OK to create the role with the selected privileges. The Roles page appears again with an update message confirming the creation of the role. Now the new Role is available to be assigned to any user.Creating a Role In the Create Role General page, enter role name in the Name field and you can add authentication method which will be require before it can be set. The default authentication for a role is None. Click Roles tab, to add sub-roles to this role. (As done during creating a User) Click System Privileges tab, to add system privileges to this role. (As done during creating a User) Click Object Privileges tab, to add object privileges to this role. (As done during creating a User) Finally, click OK to create the role with the selected privileges. The Roles page appears again with an update message confirming the creation of the role. Now the new Role is available to be assigned to any user.

30. Profiles Page CAP364 30 Ebtisam Alabdulqader Profiles Page Click Profiles in the Security section on the Administration page. The Profiles page appears. There are a some of predefined Profiles, such as DEFUALT profile. From this page you can create, edit, view, or delete profiles. In Profiles page, click Create to create a new profile. The Create Profiles General page appears. Profiles Page Click Profiles in the Security section on the Administration page. The Profiles page appears. There are a some of predefined Profiles, such as DEFUALT profile. From this page you can create, edit, view, or delete profiles. In Profiles page, click Create to create a new profile. The Create Profiles General page appears.

31. Creating a Profile - General Tab CAP364 31 Ebtisam Alabdulqader Creating a Profile - General Tab In the Create Profile General page, enter profile name in the Name field. Set the Resource limitations for the profile: (setting options: specified value, unlimited, or reference whatever setting is in the default profile) CPU: CPU resources may be limited on a per-session or per-call basis.  CPU/Session: if set to of 1,000 means that if any individual session that uses this profile consumes more than 10 seconds of CPU  time (CPU time limitations are in hundredths of a second.), then that session receives an error and is logged off: ORA-02392: exceeded session limit on CPU usage, you are being logged off. CPU/call limitation does the same thing, but instead of limiting the user’s overall session, it prevents any single command from  consuming too much CPU. If CPU/Call is limited and the user exceeds the limitation, the command aborts, and the user  gets an error message, such as the following: ORA-02393: exceeded call limit on CPU usage. Network/Memory: Each database session consumes system memory resources and (if the session is from a user who is not local to the  server) network resources. You can specify the following: Connect Time: Indicates for how many minutes a user can be connected before being automatically logged off. Idle Time: Indicates for how many minutes a user’s session can remain idle before being automatically logged off.  Idle time is calculated for the server process only. It does not take into account application activity. Concurrent Sessions: Indicates how many concurrent sessions can be created by using a database user account. Private SGA: Limits the amount of space consumed within the System Global Area (SGA) for sorting, merging  bitmaps, and so on. This restriction takes effect only if the session uses a shared server.  (Shared Server is out of our course scope) Disk I/O: This limits the amount of data a user can read either at the per-session or per-call level.  Reads/Session and Reads/Call place a limitation on the total number of reads from both memory and the disk.  This can be done to ensure that no input/output (I/O)-intensive statements overuse memory and disks. Composite limit limits the total resource cost for a session. A composite limit is expressed as a weighted sum of certain resources, measured in service units. A historical information should be gathered to determine the normal range of composite resource usage for a typical profile user. When done, click Password tab to manage the password. The Create Profile password page is displayed. Creating a Profile - General Tab In the Create Profile General page, enter profile name in the Name field. Set the Resource limitations for the profile: (setting options: specified value, unlimited, or reference whatever setting is in the default profile) CPU: CPU resources may be limited on a per-session or per-call basis. CPU/Session: if set to of 1,000 means that if any individual session that uses this profile consumes more than 10 seconds of CPU time (CPU time limitations are in hundredths of a second.), then that session receives an error and is logged off: ORA-02392: exceeded session limit on CPU usage, you are being logged off. CPU/call limitation does the same thing, but instead of limiting the user’s overall session, it prevents any single command from consuming too much CPU. If CPU/Call is limited and the user exceeds the limitation, the command aborts, and the user gets an error message, such as the following: ORA-02393: exceeded call limit on CPU usage. Network/Memory: Each database session consumes system memory resources and (if the session is from a user who is not local to the server) network resources. You can specify the following: Connect Time: Indicates for how many minutes a user can be connected before being automatically logged off. Idle Time: Indicates for how many minutes a user’s session can remain idle before being automatically logged off. Idle time is calculated for the server process only. It does not take into account application activity. Concurrent Sessions: Indicates how many concurrent sessions can be created by using a database user account. Private SGA: Limits the amount of space consumed within the System Global Area (SGA) for sorting, merging bitmaps, and so on. This restriction takes effect only if the session uses a shared server. (Shared Server is out of our course scope) Disk I/O: This limits the amount of data a user can read either at the per-session or per-call level. Reads/Session and Reads/Call place a limitation on the total number of reads from both memory and the disk. This can be done to ensure that no input/output (I/O)-intensive statements overuse memory and disks. Composite limit limits the total resource cost for a session. A composite limit is expressed as a weighted sum of certain resources, measured in service units. A historical information should be gathered to determine the normal range of composite resource usage for a typical profile user. When done, click Password tab to manage the password. The Create Profile password page is displayed.

32. Creating a Profile - Password Tab CAP364 32 Ebtisam Alabdulqader Creating a Profile - Password Tab In Create Profile password page, set the value of the password security features fields. Common values for each field can be chosen from a list of values (Click the flashlight icon to browse.) or you can enter a custom value. All time periods are expressed in days. Password aging and expiration: Enables user passwords to have a lifetime, after which the passwords expire and must be changed. The applications must catch the “password expired” warning message and handle the password change; otherwise, the grace period  expires and the user is locked out without knowing the reason. Password history: Checks the new password to ensure that the password is not reused for a specified amount of time or a specified  number of password changes. Password complexity verification: Makes a complexity check on the password to verify that it meets certain rules.  There are two options available for Complexity Function which are DEFAULT (set to NULL) or NULL. But there is a supplied Password Verification Function: VERIFY_FUNCTION provided by oracle that can be used as a template for your  customized password verification. The supplied password verification function enforces these password restrictions: The minimum length is four characters. The password cannot be the same as the username. The password must have at least one alphabetic, one numeric, and one special character. The password must differ from the previous password by at least three letters. Account locking: Enables automatic locking of accounts for a set duration when users fail to log in to the system in the specified number  of attempts. Finally, click OK to create the profile with the specified limitations. The Profiles page appears again with an update message confirming the creation of the profile. Now the created profile will be available to you when you want to create or update a user.Creating a Profile - Password Tab In Create Profile password page, set the value of the password security features fields. Common values for each field can be chosen from a list of values (Click the flashlight icon to browse.) or you can enter a custom value. All time periods are expressed in days. Password aging and expiration: Enables user passwords to have a lifetime, after which the passwords expire and must be changed. The applications must catch the “password expired” warning message and handle the password change; otherwise, the grace period expires and the user is locked out without knowing the reason. Password history: Checks the new password to ensure that the password is not reused for a specified amount of time or a specified number of password changes. Password complexity verification: Makes a complexity check on the password to verify that it meets certain rules. There are two options available for Complexity Function which are DEFAULT (set to NULL) or NULL. But there is a supplied Password Verification Function: VERIFY_FUNCTION provided by oracle that can be used as a template for your customized password verification. The supplied password verification function enforces these password restrictions: The minimum length is four characters. The password cannot be the same as the username. The password must have at least one alphabetic, one numeric, and one special character. The password must differ from the previous password by at least three letters. Account locking: Enables automatic locking of accounts for a set duration when users fail to log in to the system in the specified number of attempts. Finally, click OK to create the profile with the specified limitations. The Profiles page appears again with an update message confirming the creation of the profile. Now the created profile will be available to you when you want to create or update a user.

33. Practice on Users Administration Oracle 10g

34. Practice Invoke EM using this URL: http://10.144.20.250:5500/em Enter your username, password and select Normal as “Connect As”. Create a Role with the following specifications: CAP364 34 Ebtisam Alabdulqader

35. Practice Then, create a User with the following specifications: Delete the created role and user. CAP364 35 Ebtisam Alabdulqader

36. References Oracle Database 10g: Administration Workshop I, Student Guide. Oracle Database Security Guide10g Release 2 (10.2). [Online]. Available: http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/toc.htm CAP364 36 Ebtisam Alabdulqader