Creating and Configuring Users - Practices for Lesson 6: Overview

Practices for Lesson 6: Overview

Practice 6-3: Creating and Configuring Users

In this practice, you create the following users and assign appropriate profiles and roles to these users:

Name Username Description

David Hamby DHAMBY A new HR Clerk

Rachel Pandya RPANDYA A new HR Clerk

Jenny Goodman JGOODMAN A new HR Manager

1. Create an account for David Hamby, a new HR clerk.

Step Window/Page Description Choices or Values

a. EM Express Select Security > Users

b. Users Click Create User.

c. Create User: User Account Name: DHAMBY

Authentication: Select Password Password: newuser

Profile: HRPROFILE Select Password expired Click the Next icon.

Note: This user will have to change the password. d. Create User: Tablespace Verify

Default Tablespace: USERS Temporary Tablespace: TEMP Click the Next icon.

e. Create User: Privilege Select Connect and move it to the right pane. Click Show SQL.

f. Confirmation Click OK.

g. Create User: Privilege Select HRCLERK and move it to the right pane. Hint: Enter HR in the search/filter box.

Click Show SQL.

h. Confirmation Copy and paste the SQL statements into a gedit window.

i. Linux Desktop

(see screenshot below)

Click Applications > Accessories > gedit Text Editor

In gedit, click File > Save as

Enter P6script.sql as the file name.

Save in the default location of /home/oracle. Click Save.

Click File > Quit

Step Window/Page Description Choices or Values

j. Confirmation Click OK.

k. Create User: Privilege Click OK.

l. Confirmation Click OK.

2. Create an account for Rachel Pandya, another new HR clerk. Modify the P6script.sql script to create the RPANDYA user.

a. Open the /home/oracle/P6script.sql file in an editor (instructions assume you are using gedit).

b. Substitute RPANDYA for DHAMBY in every occurrence of DHAMBY. c. Specify the password as newuser.

d. Check the script for SQL end-of-command delimiters “;” (semicolon). Add semicolons as necessary for correct syntax.

e. Add an exit command to the end of the file. f. Save and close the file.

g. In a terminal window, execute the P6script.sql script in SQL*Plus as the DBA1 user with the SYSDBA role connected to the orcl database.

$ sqlplus dba1/oracle_4U as sysdba @/home/oracle/P6script.sql … Connected to: … User created. Grant succeeded. Grant succeeded.

Disconnected …

h. Use EM Express to check that user RPANDYA has been created as expected. Hint: You may have to refresh EM Express to see the RPANDYA user.

3. Create an account for Jenny Goodman, the new HR manager. Modify the P6script.sql script to take parameters for the username and role. Execute the script to create the JGOODMAN user with the HRMANAGER role.

a. In gedit or an editor of your choice, open the script /home/oracle/P6script.sql.

b. Change every occurrence of RPANDYA to &&username. c. Change every occurrence of HRCLERK to &&role. d. Save and close the file.

e. Execute the SQL script in SQL*Plus as the DBA1 user with the SYSDBA role connected to the orcl database instance.

$ sqlplus dba1/oracle_4U as sysdba @/home/oracle/P6script.sql …

Connected to:

…

Enter value for username: JGOODMAN

old 1: create user "&&username" identified by newuser profile "HRPROFILE" password expire account unlock default tablespace "USERS" temporary tablespace "TEMP"

new 1: create user "JGOODMAN" identified by newuser profile "HRPROFILE" password expire account unlock default tablespace "USERS" temporary tablespace "TEMP"

User created.

Enter value for role: HRMANAGER old 1: grant &&role to &&username new 1: grant HRMANAGER to JGOODMAN

Grant succeeded.

old 1: grant "CONNECT" to &&username new 1: grant "CONNECT" to JGOODMAN

Grant succeeded.

Disconnected …

Note: The double ampersand && indicates to SQL*Plus to keep the value of this variable and use the same value each time it sees this variable. If you had used a single ampersand &, SQL*Plus would have prompted you to enter the value each time the substitution

variable occurred in the script.

4. Test the new users in SQL*Plus. Connect to the orcl database as the DHAMBY user. Use oracle_4U as the new password. Select the row with EMPLOYEE_ID=197 from the HR.EMPLOYEES table. Then attempt to delete it. You should get the “insufficient privileges” error.

a. In a terminal window, enter: $ . oraenv

ORACLE_SID = [oracle] ? orcl …

$ sqlplus dhamby

Or, if you already have a SQL*Plus session started, use the CONNECT command. If you reconnect as dhamby in SQL*Plus, the login and change-of-password session look like this:

SQL> CONNECT dhamby

In either case, the next line will be a prompt for the password.

Enter password: newuser <<<Password does not appear on screen ERROR:

ORA-28001: the password has expired

b. Change the password to oracle_4U. Changing password for dhamby

New password: oracle_4U <<<Password does not appear

Retype new password: oracle_4U <<<Password does not appear Password changed

…

Connected to: …

SQL>

c. Select the salary for employee 197 from the HR.EMPLOYEES table.

SQL> SELECT salary FROM hr.employees WHERE EMPLOYEE_ID=197;

SALARY --- 3000

d. Now attempt to delete the same row from the HR.EMPLOYEES table. SQL> DELETE FROM hr.employees WHERE EMPLOYEE_ID=197; DELETE FROM hr.employees WHERE EMPLOYEE_ID=197 *

ERROR at line 1:

ORA-01031: insufficient privileges

5. Repeat the test as the JGOODMAN user. Use oracle_4U as the new password. After deleting the row, issue a rollback, so that you still have the original 107 rows.

a. Connect to the orcl database as the JGOODMAN user. SQL> connect jgoodman

Enter password: ERROR:

ORA-28001: the password has expired Changing password for jgoodman New password: *******

Retype new password: ******* Password changed

Connected. SQL>

b. Select the row for employee 197 from the HR.EMPLOYEES table.

SQL> SELECT salary FROM hr.employees WHERE EMPLOYEE_ID=197;

SALARY --- 3000

c. Now delete the same row from the HR.EMPLOYEES table.

SQL> DELETE FROM hr.employees WHERE EMPLOYEE_ID=197;

1 row deleted.

d. Roll back the delete operation (because this was just a test). SQL> rollback;

Rollback complete.

e. Confirm that you still have 107 rows in this table. SQL> SELECT COUNT(*) FROM hr.employees;

COUNT(*) --- 107

SQL>

Question: You did not grant the CREATE SESSION system privilege to any of the new

users, but they can all connect to the database. Why?

Answer: CREATE SESSION is one of the privileges of the CONNECT role.

6. Use SQL*Plus to connect to the orcl database as the RPANDYA user. Change the password to oracle_4U. (You must change the password, because this is the first connection as RPANDYA.) Leave RPANDYA connected during the next lesson or at the end of the day. HRPROFILE specifies that users whose sessions are inactive for more than 15 minutes will automatically be logged out. Verify that the user was automatically logged out by trying to select from the HR.EMPLOYEES table again.

SQL> SELECT salary FROM hr.employees WHERE EMPLOYEE_ID=197; ERROR at line 1:

ORA-02396: exceeded maximum idle time, please connect again

In document D78846GC20_ag (Page 57-63)