Advanced SQL Queries-Lesson: Users and Schemas

Advanced SQL Queries

Lesson: Users and Schemas

Welcome to our free Advanced Oracle SQL Queries tutorial. This tutorial is based

 on Webucator's Advanced Oracle SQL Queries course.

When you interact with an Oracle database, you do so with a given identity that 

can take certain actions. This identity is represented by an Oracle user. The

actions the user takes occur within a subsection of the database that can have 

database objects associated with it. This subsection is known as a schema.

The Oracle HR Schema contains Human Resources data for a fictional 

company. The tables and data provide an area where you can experiment 

with Oracle Database features without concern of damaging production data. 

The tables and relationships are structured in a way that allows for the use of 

most of the features of SQL. In addition, most of the examples in Oracle's 

own documentation utilize data from the HR Schema.

Lesson Goals

• Learn what Oracle Users and Schemas are.
• Learn about the Oracle HR Schema.
• Learn about Schema Authorization.
• Learn how to Create An Application Schema.

Introduction to Users and Schemas

• A database user is identified by a unique user name and password combination. 
• A user has a set of assigned security permissions which allow access and control of 
• database resources.
• A schema is a logical collection of database objects (tables, views, triggers, etc.) 
• owned by a database user.
• A schema has the same name as the user.
• SQL Developer is a graphical interface for Oracle Databases.
• SQL*Plus is a command line interface for Oracle Databases.

• Double click on the menu option or SQL Developer icon.

• Wait while the splash screen displays.

• If you have not yet configured your connection:

• Right-click on "Connections"
1. Choose New Connection
2. Enter the connection information
3. Test the connection and make any needed corrections
• Choose your connection and log in, using your oracle username (HR for instance) 
• and password

• sqlplus <user>/<password>@<database>
• Note that queries need to be ended with a semi-colon or a forward-slash.

Definitions and Client Tools

A database user is identified by a unique user name and password combination. 

A user has a set of assigned security permissions which allow access and control of 

database resources.

A schema is a logical collection of database objects (tables, views, triggers, etc.) 

owned by a database user.

A schema has the same name as the user.

SQL Developer is a graphical interface for Oracle Databases.

SQL*Plus is a command line interface for Oracle Databases.

SQL Developer Login

Double click on the menu option or SQL Developer icon.

Wait while the splash screen displays.

If you have not yet configured your connection:

Right-click on "Connections"

1. Choose New Connection
2. Enter the connection information
3. Test the connection and make any needed corrections

Choose your connection and log in, using your oracle username (HR for instance) and 

password

To Log on to the HR schema using SQL*Plus.

sqlplus hr/hr@xe

You can start SQL*Plus without logging in to a database.

sqlplus /nolog

However, it is more common to connect using a connection string.

sqlplus <user>/<password>@<database>

About the HR Schema

Sample Schemas

Oracle provides sample schemas as a common platform for examples.

Oracle documentation is based on the sample schemas.

The HR (Human Resources) schema will be used for this course.

• Employees
• Departments
• Jobs
• Job History
• Locations
• Countries
• Regions

A view (emp_details_view) is also provided as a useful summarization of commonly joined 

tables.

Creating An Application Schema

Code Sample:

Users-and-Schemas/Demos/create_user.sql

CREATE USER myusername IDENTIFIED BY mypassword;

DROP USER myusername CASCADE;

Code Explanation

Users are created by executing a CREATE USER statement as a privileged user. 

(Oracle users are assigned security related permissions based upon the requirements 

of a person's job and the sensitivity of the data. A privileged user is often a DBA - in this 

case it is a user who has the ability to create another user and grant the permissions listed. 

The simplest way to do this is to log on as a DBA user such as the system user).

You can drop a user by executing the DROP USER statement.

A user's characteristics can be modified using the ALTER statement. In the following 

example, we assign some permissions associated with a tablespace.

Code Sample:

Users-and-Schemas/Demos/alter_user.sql

ALTER USER myusername DEFAULT TABLESPACE users;

ALTER USER myusername QUOTA UNLIMITED ON users;

Code Explanation

A tablespace is a storage unit that contains data files that physically reside on a server. 

Each table and index created can be assigned to a specific tablespace. There are actually 

several types of table spaces - permanent, undo, and temporary. In this case, the USERS 

tablespace is a permanent tablespace where application user objects and data are stored.

DBAs have a challenging task of managing limited resources for a user base that frequently

 expresses unlimitted needs. One way a user can be limited is the amount of space they 

can use. This is done by limiting their "quota." By default, no space is allocated. 

The statement in question is used to allow a user the ability to take up space - and we 

are not concerned with using up this space in a classroom situation.

A user's access to the database itself and specific objects is set using the GRANT statement.

Code Sample:

Users-and-Schemas/Demos/grant_user.sql

GRANT CREATE SESSION, RESOURCE, CREATE SYNONYM, CREATE VIEW 
	TO myusername;

GRANT SELECT ON sometable TO myusername;

GRANT INSERT ON sometable TO myusername;

GRANT DELETE ON anothertable TO myusername;

Code Explanation

There are a number of other grants available, such as the ability to create database objects.

 These statements will be covered in the admin course.

There are numerous variations related to security depending upon your Oracle version and 

environment. Previous to Oracle 10g Release 2 you would also be required to "grant 

connect." Also note that additional privileges are needed to create and alter the objects 

described in this course. You will not need to be concerned with this in class, but should 

be aware of this if you plan to duplicate the HR environment at some later point.

A list of users associated with the database can be found by querying the ALL_USERS 

data dictionary table.

SELECT * FROM all_users;

Create a User

Duration: 5 to 10 minutes.

1. Log in as a DBA user.
2. Create a new user.
3. Set the user's default table space to USERS.
4. Grant the user RESOURCE and CREATE SESSION privileges.
5. Grant the user the ability to select from the HR departments table.
6. Grant the user the ability to insert into the HR departments table.
7. Grant the user the ability to delete from the HR departments table.
8. Connect as the new user.
9. Execute the following: SELECT * FROM HR.DEPARTMENTS;

Solution:

Users-and-Schemas/Solutions/user_solution.sql

--
--  Connect as privileged user (system/<password>) and execute
--  the following commands
--

CREATE USER myusername IDENTIFIED BY mypassword;

GRANT CREATE SESSION, RESOURCE, CREATE SYNONYM, CREATE VIEW
	TO myusername;

GRANT SELECT ON hr.departments TO myusername;

GRANT INSERT ON hr.departments TO myusername;

GRANT DELETE ON hr.departments TO myusername;

--
--  Connect as the new user and execute a query to validate
--  that the user has been created and permissions granted
--  successfully
--
 

SELECT * FROM hr.departments;