Network Data Bases

4- Distributed Databases
University of Algiers 1 -
Benyoucef Benkhedda
Introduction
• A distributed database system allows applications to access data from local and remote
databases in which each database is a relational Database.

• A distributed database system is a network of two or more relational Databases that reside
on one or more systems.

• When all databases are created and managed in the same DBMS, we are talking about
homogenous distributed database system,.

• If at least one of the databases uses a different DBMS, we are talking about heterogeneous
distributed database system.

2
• Distributed databases use a client/server architecture to process information requests.
Homogenous Distributed Database Systems
• includes only databases using the same DBMS

• A single query can have access to a set or all

existing databases in a transparent way in client’s
application interface

• You can also create synonyms for remote objects in

the distributed system so that users can access
them with the same syntax as local objects. This
will allow the execution of local SQL queries.

3
Homogenous Distributed Database Systems:
Distributed Databases Vs. Distributed Processing

Distributed database is Distributed processing is

• A set of databases in a • The operations that occurs
distributed system that can when an application distributes
appear to applications as a its tasks among different
single data source computers in a network.

• A distributed database application processing system is more commonly referred to

as a client/server database application system.

4
Homogenous Distributed Database Systems:
Distributed Databases Vs. Replicated Databases

Distributed database Replicated Database

• The system manages a single • refers to the operation of copying
copy of all data and supporting and maintaining database objects
database objects in multiple databases belonging
to a distributed system.

• Distributed database can, partly or totally, be a replicated database when a part of it

or totally replicated between entities
• Most commonly, replication is used to improve local database performance and
protect the availability of applications because alternate data access options exist.
5
Heterogeneous Distributed Database Systems
• In a heterogeneous distributed database system, at least one of the databases uses a different
DBMS. To the application, the heterogeneous distributed database system appears as a single
local Database using a single DBMS e. i.: Oracle.

• The Database server accesses other DBMS’s databases using Heterogeneous Services with an
agent

• For example: If you access the non-Oracle Database data store using an Oracle Transparent
Gateway, then the agent is a system-specific application. Alternatively, you can use generic
connectivity to access non-Oracle Database data stores so long as the non-Oracle Database
system supports the ODBC or OLE DB protocols.

6
Heterogeneous Distributed Database Systems:
Heterogeneous Services
• Heterogeneous Services (HS) is an integrated component within the Oracle Database server
and the enabling technology for the current suite of Oracle Transparent Gateway products.

Transparent Gateway Agents

• For each Database system that you access, Heterogeneous Services can use a transparent
gateway agent to interface with the specified heterogeneous Database system.

• The agent is specific to the second Database system, so each type of system requires a
different agent.

• The agent executes SQL and transactional requests at the second Database system on
behalf of the main Database server.

7
Heterogeneous Distributed Database Systems:
Heterogeneous Services
• Heterogeneous Services (HS) is an integrated component within the Oracle Database server
and the enabling technology for the current suite of Oracle Transparent Gateway products.

Generic Connectivity
• Generic connectivity enables you to connect to any Database data stores by using either
a Heterogeneous Services ODBC agent or a Heterogeneous Services OLE DB agent.

• The advantage to generic connectivity is that it may not be required for you to purchase
and configure a separate system-specific agent. You use an ODBC or OLE DB driver that
can interface with the agent.

• Some data access features are only available with transparent gateway agents.

8
Client/Server Database Architecture
• A database server is the software managing a
database

• A client is an application that requests

information from a server.

• Each computer in a network is a node that can

host one or more databases.

• Each node in a distributed database system who

can act as a client, a server, or both, depending on
the situation.
9
Client/Server Database Architecture
• A client can connect directly or indirectly to a database server.

• A direct connection occurs when a client connects to a server and accesses information from
a database contained on that server. In this case classic SQL queries remains the same (select
* from dept)

• an indirect connection occurs when a client connects to a server and then accesses
information contained in a database on a different server. In this case the queries must
contain the database name on it (select * from emp@sales)

10
Database Links
• The central concept of implementing distributed
databases is a database link

• A database link is a connection between two

physical database servers that allows a client to
access them as one logical database

• A database link is a pointer that defines a one-

way communication path between databases.

• each database in the distributed system must

have a unique global database name in the
network domain 11
Database Links
• Database links are either private or public:
• If they are private, then only the user who created the link has access;
• if they are public, then all database users have access.

12
Shared Database Links
• A shared database link is a link between a local server process and the remote database.
The link is shared because multiple client processes can use the same link simultaneously.

• When a local database is connected to a remote database through a database link, either
database can run in dedicated or shared server mode.
Local database Remote database
Dedicated Dedicated
Shared server Dedicated
Dedicated Shared server
Shared server Shared server

• So why use database links?

• Simply, to ensure that a local user can access a link to a remote database without having to be a user on
the remote database
13
Global Database Names in Database Links
• Each database in a distributed database is uniquelyidentified by its global database name.

• The database forms a global database name by prefixing the database network domain,
specified by the DB_DOMAIN initialization parameter at database creation, with the
individual database name, specified by the DB_NAME initialization parameter.

DB_DOMAIN for mfg is:

mfg.division3.example_tools.com

14
Global Database Names in Database Links
• We can use the global name of a database as a loopback database link without explicitly
creating a database link.
For example, considering a user at the local database at [Link].example_auto.com
database, the following SQL query can be executed without creating explicitly a database
link:
Select * from [Link]@HQ.division1.example_tools.com

• Typically, a database link has the same name as the global database name of the remote
database that it references

• When you set the initialization parameter GLOBAL_NAMES to TRUE, the database ensures
that the name of the database link is the same as the global database name of the remote
database.

• For more information: [Link]

[Link]#REFRN-GUID-FD266F6F-D047-4EBB-8D96-
B51B1DCA2D61 15
Types of Database Links

16
Users of Database Links

17
Users of Database Links
Connected user
• Connected user links have no connect string associated with them.

• The advantage of a connected user link is that a user referencing the link connects to the
remote database as the same user (logging), and credentials do not have to be stored in
the link definition in the data dictionary.

• These links require users to have accounts and privileges on the remote databases to
which they are attempting to connect.

• Giving users more privileges than they need violates the fundamental security concept of
least privilege: we will see it in Security module .

18
Users of Database Links
Fixed user
• Fixed user links have a user name and password associated with the connect string.

• A benefit of a fixed user link is that it connects a user in a primary database to a remote
database with the security context of the user specified in the connect string.

• User name and password are stored with other link information in data dictionary tables
(multiple users of multiple links can cause a storage problems especially with extremely
large systems)

19
Users of Database Links
Current user
• Current user database links make use of a global user. A global user must be
authenticated by an X.509 certificate or a password, and be a user on both databases
involved in the link.

• The user invoking the CURRENT_USER link does not have to be a global user.

• Some consequences:
• If the current user database link is not accessed from within a stored object, then the current user is
the same as the connected user accessing the link.
• When executing a stored object such as a procedure, view, or trigger that accesses a database link,
the current user is the user that owns the stored object, and not the user that calls the object.
• If the stored object is an invoker's rights function, procedure, or package, then the invoker's
authorization ID is used to connect as a remote user.
• You cannot connect to a database as an enterprise user and then use a current user link in a stored
procedure that exists in a shared, global schema.
20
Examples of creation of Database Links

21
Quick break: access rights in database
Queries for access control
vTo give the right to access
GRANT <rights_list> | ALL ON <component> TO <users_list> | PUBLIC [WITH GRANT
OPTION ] ;

v ALL : all privileges will be granted (select, insert, delete and update)
v PUBLIC : all users of the system will have the same rights
v WITH GRANT OPTION : grant the right with the possibility of transmitting it to another user

vDeleting a right from a user

REVOKE [GRANT OPTION FOR] <rights_list> | ALL ON <component> FROM <users_list> |
PUBLIC [RESTRICT | CASCADE] ;

v CASCADE : revoking the right from the cited user within FROM clause and every single user who got the right
from that user
v RESTRICT: revoking the right only from the cited user within FROM clause
v [GRANT OPTION FOR] no right will be revoked but only the possibility of granting it
Quick break: access rights in database
Role based access control
• Used to minimize number and size of grant queries
• A role is considered as a user and a right in the same time

vCreating a role
CREATE ROLE <role_name> [IDENTIFIED BY <password> ] ;

vDeleting a role
DROP ROLE <role_name>;
Quick break: access rights in database
Role based access control
vGranting rights to a role
GRANT <rights_list> | ALL ON <component> TO <role_name> [WITH GRANT OPTION ] ;

vRevoking rights from a role

REVOKE [GRANT OPTION FOR] <rights_list> | ALL ON <component> FROM <role_name>
[RESTRICT | CASCADE] ;

vGranting a role to users

GRANT <roles_list> TO <users_list> [WITH ADMIN OPTION] ;

vRevoking a role from users

REVOKE [GRANT OPTION FOR] <roles_list> FROM <users_list> [RESTRICT | CASCADE] ;