Oracle Database Administration/User Security

This lesson introduces Oracle database user security.

Objectives and Skills
Objectives and skills for the user security portion of Oracle Database Administration I certification include:
 * Administering User Security
 * Create and manage database user accounts
 * Grant and revoke privileges
 * Create and manage roles
 * Create and manage profiles

Readings

 * 1) Oracle: Database 2-Day DBA Administering User Accounts and Security

Multimedia

 * 1) YouTube: User, Role, Privileges, Password Policy Administration
 * 2) Oracle Enterprise User Security

Tutorial

 * 1) Complete the tutorial Oracle: Administering Users and Security.

Database Authentication

 * 1) Review Oracle: Administering Authentication.
 * 2) Display existing users.
 * 3) Use Enterprise Manager Database Control / Server / Users to display existing users.
 * 4) Use the following query to describe the DBA_USERS table:
 * 5) Use the following query to display existing users:
 * 6) Add users.
 * 7) Use Enterprise Manager Database Control / Server / Users to add a new user named USER1 with a password of password.
 * 8) Use the following command to connect to the server as USER1:
 * 9) Use the following query as SYS or SYSTEM to add a new user named USER2 with a password of password:
 * 10) Use the following command to attempt to connect to the server as USER2:
 * 11) Use the following query as SYS or SYSTEM to grant a connection to USER2:
 * 12) Use the following command to connect to the server as USER2:
 * 13) Grant access to resources.
 * 14) Review Oracle: Configuring Privilege and Role Authorization.
 * 15) As USER1 or USER2, attempt to select data from the HR schema using the following query:
 * 16) As SYS or SYSTEM, use the following query to grant SELECT access to HR.REGIONS:
 * 17) Use the following query to confirm permissions:
 * 18) As USER2, select data from the HR schema using the following query:
 * 19) Modify users.
 * 20) Use Enterprise Manager Database Control / Server / Users to change the password for USER1 to newpass.
 * 21) Use the following query to change the password for USER2 to newpass:
 * 22) Use Enterprise Manager Database Control / Server / Users to lock the account for USER1.
 * 23) Use the following query to lock the account for USER2:
 * 24) Use the following query to unlock the accounts for USER1 and USER2:

Database Administrator Authentication

 * 1) Review Oracle: Authentication of Database Administrators.
 * 2) Add administrator users.
 * 3) Add an operating system account for USER1 and USER2. In Windows use Computer Management / Local Users and Groups / Users. In Linux, use useradd.
 * 4) Log off and log on as USER1 or USER2. Attempt to connect to the database using the following commands:
 * 5) Log off and log on as Administrator. Connect to the database using the following command:
 * 6) Use Enterprise Manager Database Control / System / Users to edit USER1 and edit the System Privileges list to add the SYSDBA privilege and select the Admin Option checkbox.
 * 7) Use the following query to grant SYSOPER privilege to USER2:
 * 8) Use the following query to confirm the changes:
 * 9) Log off and log on as USER1 or USER2. Connect to the database using the following commands:
 * 10) Log off and log on as Administrator. Connect to the database using the following command:

External Authentication

 * 1) Review Oracle: Administering Authentication.
 * 2) Configure external authentication.
 * 3) Use the following query to set the Oracle user authentication prefix to an empty string:
 * 4) Use the following query to shutdown and startup the database so that the change takes effect:
 * 5) Add external users.
 * 6) Use the following query to identify the server host name:
 * 7) Use the host name in following query to add a new user named USER3 authenticated by the operating system:
 * 8) Use the following query to grant a connection to \USER3:
 * 9) Add an operating system account for USER3. In Windows use Computer Management / Local Users and Groups / Users. In Linux, use.
 * 10) Test external authentication.
 * 11) Log off and log onto the system as USER3. Use the following command to connect to Oracle as USER3:
 * 12) Log off and log onto the system as Administrator. Use the following command to connect to Oracle as SYS
 * 13) Delete external users.
 * 14) Use the following query to delete USER3:
 * 15) Delete USER3 from the system using Computer Management or.

Global Authentication

 * 1) Review Oracle: Global Authentication and Authorization
 * 2) Add global users.
 * 3) Add a global directory account for USER4. Note the distinguished directory service name for the user.
 * 4) Add USER4 using the distinguished name in the following query:
 * 5) Use the following query to grant a connection to USER4:
 * 6) Test global users.
 * 7) Log off and log onto the system as USER4. Use the following command to connect to Oracle as USER4:
 * 8) Log off and log onto the system as Administrator. Use the following command to connect to Oracle as SYS:
 * 9) Delete global users.
 * 10) Use the following query to delete USER4:
 * 11) Delete the global directory account for USER4.

Roles

 * 1) Create roles.
 * 2) Review Oracle: Configuring Privilege and Role Authorization.
 * 3) Use the following queries to create roles for the HR schema:
 * 4) Manage roles.
 * 5) Use the following queries to grant access to the HR schema roles:
 * 6) Use the following queries to confirm roles and permissions:
 * 7) Connect as USER1 and USER2 and test access for each using the following queries:
 * 8) Delete roles.
 * 9) Use the following queries to remove roles:
 * 10) Use the following queries to confirm roles and permissions:

Profiles

 * 1) Review Oracle: CREATE PROFILE.
 * 2) View existing profile settings.
 * 3) Use the following queries to view existing profile settings:
 * 4) Create a profile.
 * 5) Use the following query to create a profile:
 * 6) Assign a profile.
 * 7) Use the following query to assign the profile to USER1:
 * 8) Attempt to connect as USER1 with multiple concurrent sessions to test the profile.
 * 9) Delete a profile.
 * 10) Use the following query to delete the RESTRICTED profile:

Cleanup

 * 1) Delete users.
 * 2) Use Enterprise Manager to delete USER1.
 * 3) Use the following query to delete USER2:
 * 4) Use the following query to confirm the changes: