EXTERNALLY CREATE USER username IDENTIFIED { BY password GLOBALLY AS 'external name' } [ DEFAULT TABLESPACE tablespace) [ TEMPORARY TABLESPACE { tablespace tablespace_group_name } ] [QUOTA { integer { K M} ON tablespace ( PROFILE profile ] ( PASSWORD EXPIRE] [ ACCOUNT { LOCK UNLOCK } ] UNLIMITED ] First of all, the CREATE USER statement is a SQL statement that is supported by all rela- tional databases. The CREATE USER statement is part of a Data Definition Language (DLL), which is a component of the SQL language. This statement enables database administrators to create a database user account. When granted the proper privileges, this user account can perform various tasks and own different database objects. The various Oracle options examined can be found in: here, and the textbook. IDENTIFIED clause BY Password option EXTERNALLY option GLOBALLY AS external name option DEFAULT TABLESPACE tablespace name clause TEMPORARY TABLESPACE tablespace_identifier clause QUOTA clause PROFILE clause PASSWORD EXPIRE clause ACCOUNT clause Even though all clauses of the CREATE USER statement are optional, you should include clauses according to your company policies. At this point you have been exposed to a number of options, and it is time to pull them all together. Consider the following company policy, which states the requirements for creating a new user (Figure 1): clauses according to your company policies. At this point you have been exposed to a number of options, and it is time to pull them all together. Consider the following company policy, which states the requirements for creating a new user (Figure 1): Company Policy for Creating New Database Users - New users must have their application form approved by their manager and database manager; then the form can be submitted to the database adminis- tration group. The application form must describe the role and purpose of the user account. The application form must be authenticated by the database. The first time the user logs on, the user must be prompted to enter a new pass- word. - Storage for all users is assigned to USERS tablespace unless specified otherwise. - All users must be assigned to the TEMP tablespace unless specified otherwise. - The storage quota is 25 MB. If the new account is not used immediately, the account is locked within three days from the time the account was established. - When the holder owner of the account is terminated or the account is not used for a period of 30 days, the account is locked. SQL> CREATE USER safyouni IDENTIFIED BY safyouni01 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 25M ON users PROFILE default PASSWORD EXPIRE ACCOUNT UNLOCK 8 / User created. Figure 1 Once a user is created, you can modify a user account with an ALTER USER statement using the same clauses listed in the previous sample. 1. Launch SQL*Plus. Enter password for system account to connect to your database. Create a username "your first namel with a password "your first namel-pass. For example, my new user name is mind, and the password is min1-pass. Use users as the default tablespace. (Refer to this for differences among tablespace, database, and table). Submit a screenshot showing both your SQL statement(s) and the output (result) screen after your SQL statements are entered. 2. Write an SQL statement to show that the new user has been successfully added in the ALL_USERS table. (Refer to Homework 1 for the SQL statement.) 1. Launch SQL*Plus. Enter password for system account to connect to your database. Create a username "your first namel" with a password "your first namel_pass. For example, my new user name is mini, and the password is mini_pass. Use users as the default tablespace. (Refer to this for differences among tablespace, database, and table). Submit a screenshot showing both your SQL statement(s) and the output (result) screen after your SQL statements are entered. 2. Write an SQL statement to show that the new user has been successfully added in the ALL_USERS table. (Refer to Homework 1 for the SQL statement.) Submit a screenshot showing both your SQL statement and the output (result) screen after your SQL statements are entered. 3. Note that when an Oracle user account is created, the new user cannot log in to the account until the database administrator provides the CREATE SESSION system privilege (which will be discussed later in Chapter 4) to allow the account to connect to the database. So, I cannot log in with mini, yet. Submit a screenshot showing an error message after you try to log in with the new account and password on SQL*Plus. Page 3 (a) You can exit the current session with command exit or disconnect, and log in with the new ID (yourname1) and password. (b) Or, while being connected as system, enter command conn yourname1/yourname1_pass to log in as yourname1. The previous examples showed how to to issue an SQL statement to create an Oracle user using SQL*Plus. The graphical interface of Oracle Enterprise Manager (EM) also provides The previous examples showed how to to issue an SQL statement to create an Oracle user using SQL*Plus. The graphical interface of Oracle Enterprise Manager (EM) also provides a function for creating a user. This function is called Security Management. 4. Launch EM. Enter password for system account to connect to your database. Select Users under the [Security) function, and click the Create User) button under the [Action) function. When this button is clicked, a dialog box appears offering a choice of objects that can be created. Create a username "your first name2" with a password "your first name2-pass. For example, my new user name is min2, and the password is min2_pass. Use users as the default tablespace. Submit a screenshot showing the output (result) screen i.e., EM showing the new user) after you create the new user. 5. Note again that when an Oracle user account is created, the new user cannot log in to the account until the database administrator provides some privileges to allow the account to connect to the database. How to grant privileges will be covered in Chapter 4. So, I cannot log in with min2, yet. Submit a screenshot showing an error message after you try to log in with the new account and password on EM. EXTERNALLY CREATE USER username IDENTIFIED { BY password GLOBALLY AS 'external name' } [ DEFAULT TABLESPACE tablespace) [ TEMPORARY TABLESPACE { tablespace tablespace_group_name } ] [QUOTA { integer { K M} ON tablespace ( PROFILE profile ] ( PASSWORD EXPIRE] [ ACCOUNT { LOCK UNLOCK } ] UNLIMITED ] First of all, the CREATE USER statement is a SQL statement that is supported by all rela- tional databases. The CREATE USER statement is part of a Data Definition Language (DLL), which is a component of the SQL language. This statement enables database administrators to create a database user account. When granted the proper privileges, this user account can perform various tasks and own different database objects. The various Oracle options examined can be found in: here, and the textbook. IDENTIFIED clause BY Password option EXTERNALLY option GLOBALLY AS external name option DEFAULT TABLESPACE tablespace name clause TEMPORARY TABLESPACE tablespace_identifier clause QUOTA clause PROFILE clause PASSWORD EXPIRE clause ACCOUNT clause Even though all clauses of the CREATE USER statement are optional, you should include clauses according to your company policies. At this point you have been exposed to a number of options, and it is time to pull them all together. Consider the following company policy, which states the requirements for creating a new user (Figure 1): clauses according to your company policies. At this point you have been exposed to a number of options, and it is time to pull them all together. Consider the following company policy, which states the requirements for creating a new user (Figure 1): Company Policy for Creating New Database Users - New users must have their application form approved by their manager and database manager; then the form can be submitted to the database adminis- tration group. The application form must describe the role and purpose of the user account. The application form must be authenticated by the database. The first time the user logs on, the user must be prompted to enter a new pass- word. - Storage for all users is assigned to USERS tablespace unless specified otherwise. - All users must be assigned to the TEMP tablespace unless specified otherwise. - The storage quota is 25 MB. If the new account is not used immediately, the account is locked within three days from the time the account was established. - When the holder owner of the account is terminated or the account is not used for a period of 30 days, the account is locked. SQL> CREATE USER safyouni IDENTIFIED BY safyouni01 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 25M ON users PROFILE default PASSWORD EXPIRE ACCOUNT UNLOCK 8 / User created. Figure 1 Once a user is created, you can modify a user account with an ALTER USER statement using the same clauses listed in the previous sample. 1. Launch SQL*Plus. Enter password for system account to connect to your database. Create a username "your first namel with a password "your first namel-pass. For example, my new user name is mind, and the password is min1-pass. Use users as the default tablespace. (Refer to this for differences among tablespace, database, and table). Submit a screenshot showing both your SQL statement(s) and the output (result) screen after your SQL statements are entered. 2. Write an SQL statement to show that the new user has been successfully added in the ALL_USERS table. (Refer to Homework 1 for the SQL statement.) 1. Launch SQL*Plus. Enter password for system account to connect to your database. Create a username "your first namel" with a password "your first namel_pass. For example, my new user name is mini, and the password is mini_pass. Use users as the default tablespace. (Refer to this for differences among tablespace, database, and table). Submit a screenshot showing both your SQL statement(s) and the output (result) screen after your SQL statements are entered. 2. Write an SQL statement to show that the new user has been successfully added in the ALL_USERS table. (Refer to Homework 1 for the SQL statement.) Submit a screenshot showing both your SQL statement and the output (result) screen after your SQL statements are entered. 3. Note that when an Oracle user account is created, the new user cannot log in to the account until the database administrator provides the CREATE SESSION system privilege (which will be discussed later in Chapter 4) to allow the account to connect to the database. So, I cannot log in with mini, yet. Submit a screenshot showing an error message after you try to log in with the new account and password on SQL*Plus. Page 3 (a) You can exit the current session with command exit or disconnect, and log in with the new ID (yourname1) and password. (b) Or, while being connected as system, enter command conn yourname1/yourname1_pass to log in as yourname1. The previous examples showed how to to issue an SQL statement to create an Oracle user using SQL*Plus. The graphical interface of Oracle Enterprise Manager (EM) also provides The previous examples showed how to to issue an SQL statement to create an Oracle user using SQL*Plus. The graphical interface of Oracle Enterprise Manager (EM) also provides a function for creating a user. This function is called Security Management. 4. Launch EM. Enter password for system account to connect to your database. Select Users under the [Security) function, and click the Create User) button under the [Action) function. When this button is clicked, a dialog box appears offering a choice of objects that can be created. Create a username "your first name2" with a password "your first name2-pass. For example, my new user name is min2, and the password is min2_pass. Use users as the default tablespace. Submit a screenshot showing the output (result) screen i.e., EM showing the new user) after you create the new user. 5. Note again that when an Oracle user account is created, the new user cannot log in to the account until the database administrator provides some privileges to allow the account to connect to the database. How to grant privileges will be covered in Chapter 4. So, I cannot log in with min2, yet. Submit a screenshot showing an error message after you try to log in with the new account and password on EM