Database management systems - Part 4/4: Stuff and things

newshumansvilleData Management

Dec 16, 2012 (4 years and 5 months ago)

118 views

Database management systems
Database management systems
Part 4/4:Stuff and things
David.Teller@univ-orleans.fr
http://www.univ-orleans.fr/lifo/Members/David.Teller/
2006-16-02
Database management systems
The program
The program
Client/server
Connexion
Security
Privileges
The program
Client/server
Connexion
Security
Privileges
Database management systems
The program
The story so far
What we have seen so far:
Notions
￿
DBMS
￿
relations
￿
keys
￿
domains
￿
constraints
￿
operations
Applications
￿
little bits of Access,little bits of OOBase
￿
CREATE TABLE (and other CREATE)
￿
SELECT
Database management systems
The program
Things break
Sorry
Database management systems
The program
Today
￿
connexion
￿
users
￿
meta-informations
￿
security.
Database management systems
The program
By the way
I hope you succeeded with all the exercises.
Any questions?
Keep in mind that the instructions I give here must be tested.SQL is a
standard but it is hardly implemented by everyone.
Database management systems
The program
By the way
I hope you succeeded with all the exercises.
Any questions?
Keep in mind that the instructions I give here must be tested.SQL is a
standard but it is hardly implemented by everyone.
Database management systems
The program
By the way
I hope you succeeded with all the exercises.
Any questions?
Keep in mind that the instructions I give here must be tested.SQL is a
standard but it is hardly implemented by everyone.
Database management systems
Client/server
Connexion
The program
Client/server
Connexion
Security
Privileges
Database management systems
Client/server
Connexion
Client?Server?
So far,for technical reasons,you’ve been working with local databases
only.
In real databases,the server is on a computer and the user is on a
different computer.They interact either through terminals or with a
client-server architecture.
Database management systems
Client/server
Connexion
Client?Server?
So far,for technical reasons,you’ve been working with local databases
only.
In real databases,the server is on a computer and the user is on a
different computer.They interact either through terminals or with a
client-server architecture.
Database management systems
Client/server
Connexion
Terminal
Database management systems
Client/server
Connexion
How-to
That’s basically a command-line,just like MS-DOS.
This kind of thing works with
￿
MySQL
￿
PostgreSQL
￿
Oracle
￿
Microsoft SQL Server
￿
...
As they all have different syntax,here’s a hint:help.
Database management systems
Client/server
Connexion
How-to
That’s basically a command-line,just like MS-DOS.
This kind of thing works with
￿
MySQL
￿
PostgreSQL
￿
Oracle
￿
Microsoft SQL Server
￿
...
As they all have different syntax,here’s a hint:help.
Database management systems
Client/server
Connexion
How-to
That’s basically a command-line,just like MS-DOS.
This kind of thing works with
￿
MySQL
￿
PostgreSQL
￿
Oracle
￿
Microsoft SQL Server
￿
...
As they all have different syntax,here’s a hint:help.
Database management systems
Client/server
Connexion
Connectivity
Q What are Access or OOBase?
A They are just graphical interfaces,used to connect to database on
servers.Essentially,they’re going to use the terminal but hide it from
you.They happen to contain their own mini-server for testing and
learning purposes.
In the case of OOBase,it’s a crappy one (HSQLDB).
Q How do they cope with different syntax and semantics?
A A module called ODBC (Open DataBase Connectivity) or JDBC (Java
DataBase Connectivity) contains drivers for each DBMS.
Database management systems
Client/server
Connexion
Connectivity
Q What are Access or OOBase?
A They are just graphical interfaces,used to connect to database on
servers.Essentially,they’re going to use the terminal but hide it from
you.They happen to contain their own mini-server for testing and
learning purposes.
In the case of OOBase,it’s a crappy one (HSQLDB).
Q How do they cope with different syntax and semantics?
A A module called ODBC (Open DataBase Connectivity) or JDBC (Java
DataBase Connectivity) contains drivers for each DBMS.
Database management systems
Client/server
Connexion
Connectivity
Q What are Access or OOBase?
A They are just graphical interfaces,used to connect to database on
servers.Essentially,they’re going to use the terminal but hide it from
you.They happen to contain their own mini-server for testing and
learning purposes.
In the case of OOBase,it’s a crappy one (HSQLDB).
Q How do they cope with different syntax and semantics?
A A module called ODBC (Open DataBase Connectivity) or JDBC (Java
DataBase Connectivity) contains drivers for each DBMS.
Database management systems
Client/server
Connexion
Connectivity
Q What are Access or OOBase?
A They are just graphical interfaces,used to connect to database on
servers.Essentially,they’re going to use the terminal but hide it from
you.They happen to contain their own mini-server for testing and
learning purposes.
In the case of OOBase,it’s a crappy one (HSQLDB).
Q How do they cope with different syntax and semantics?
A A module called ODBC (Open DataBase Connectivity) or JDBC (Java
DataBase Connectivity) contains drivers for each DBMS.
Database management systems
Client/server
Connexion
Connectivity
Q What are Access or OOBase?
A They are just graphical interfaces,used to connect to database on
servers.Essentially,they’re going to use the terminal but hide it from
you.They happen to contain their own mini-server for testing and
learning purposes.
In the case of OOBase,it’s a crappy one (HSQLDB).
Q How do they cope with different syntax and semantics?
A A module called ODBC (Open DataBase Connectivity) or JDBC (Java
DataBase Connectivity) contains drivers for each DBMS.
Database management systems
Client/server
Connexion
Connecting?
I would have like to let you connect.
OOBase seems to work.
Access doesn’t.I just couldn’t get Access to work.
Server
193.49.89.10 – that’s my laptop
Database
master2
User
master_student
Password
berruyer
Database management systems
Client/server
Connexion
Connecting?
I would have like to let you connect.
OOBase seems to work.
Access doesn’t.I just couldn’t get Access to work.
Server
193.49.89.10 – that’s my laptop
Database
master2
User
master_student
Password
berruyer
Database management systems
Client/server
Connexion
Connecting?
I would have like to let you connect.
OOBase seems to work.
Access doesn’t.I just couldn’t get Access to work.
Server
193.49.89.10 – that’s my laptop
Database
master2
User
master_student
Password
berruyer
Database management systems
Client/server
Connexion
Connecting?
I would have like to let you connect.
OOBase seems to work.
Access doesn’t.I just couldn’t get Access to work.
Server
193.49.89.10 – that’s my laptop
Database
master2
User
master_student
Password
berruyer
Database management systems
Client/server
Connexion
Go ahead
1.
Connect,using whatever you can.
2.
Add a field date to Wanted and make it part of the Key.
3.
Add some information about Phil DeFer the Outlaw.
4.
Add the necessary to the database to specify the kind of hat used by
each character.
Any comments?
You may have had problems of concurrency.
That’s one of the reasons why we need control.
Database management systems
Client/server
Connexion
Go ahead
1.
Connect,using whatever you can.
2.
Add a field date to Wanted and make it part of the Key.
3.
Add some information about Phil DeFer the Outlaw.
4.
Add the necessary to the database to specify the kind of hat used by
each character.
Any comments?
You may have had problems of concurrency.
That’s one of the reasons why we need control.
Database management systems
Client/server
Connexion
Go ahead
1.
Connect,using whatever you can.
2.
Add a field date to Wanted and make it part of the Key.
3.
Add some information about Phil DeFer the Outlaw.
4.
Add the necessary to the database to specify the kind of hat used by
each character.
Any comments?
You may have had problems of concurrency.
That’s one of the reasons why we need control.
Database management systems
Client/server
Connexion
Go ahead
1.
Connect,using whatever you can.
2.
Add a field date to Wanted and make it part of the Key.
3.
Add some information about Phil DeFer the Outlaw.
4.
Add the necessary to the database to specify the kind of hat used by
each character.
Any comments?
You may have had problems of concurrency.
That’s one of the reasons why we need control.
Database management systems
Security
Privileges
The program
Client/server
Connexion
Security
Privileges
Database management systems
Security
Privileges
Security
Security is all about preventing the wrong user from doing the wrong
things.
Any suggestion for a mechanism?
Database management systems
Security
Privileges
Security
Security is all about preventing the wrong user from doing the wrong
things.
Any suggestion for a mechanism?
Database management systems
Security
Privileges
MAC
Unix-style Mandatory Access Control:for a user u to be allowed to
perform an operation o on some entity e,someone must have given u the
right to perform exactly this operation o on exactly that entity e.
Of course,giving the right to perform an operation is an operation by
itself.
Database management systems
Security
Privileges
MAC
Unix-style Mandatory Access Control:for a user u to be allowed to
perform an operation o on some entity e,someone must have given u the
right to perform exactly this operation o on exactly that entity e.
Of course,giving the right to perform an operation is an operation by
itself.
Database management systems
Security
Privileges
Operations
Privilege Context Comment
Alter Tables To alter the table
Create Databases,Tables,Indexes To create new databases and tables
Create view Tables To create new views
Create user Server Admin To create new users
Insert Tables To insert data into tables
Delete Tables To delete existing rows
Drop Databases,Tables To drop databases,tables,and views
File File access on server To read and write files on the server
Grant option Databases,Tables,Functions,Procedures To give to other users those privileges you possess
References Databases,Tables To have references on tables
Shutdown Server Admin To shut down the server
Show view Tables To see views with SHOW CREATE VIEW
Update Tables To update existing rows
Usage Server Admin No privileges - allow connect only
Database management systems
Security
Privileges
More operations!
Create routine Functions,Procedures To use CREATE FUNCTION/PROCEDURE
Alter routine Functions,Procedures To alter or drop stored functions/procedures
Create temporary tables Databases To use CREATE TEMPORARY TABLE
Execute Functions,Procedures To execute stored routines
Index Tables To create or drop indexes
Lock tables Databases To use LOCK TABLES (together with SELECT privilege)
Process Server Admin To view the plain text of currently executing queries
Reload Server Admin To reload or refresh tables,logs and privileges
Replication client Server Admin To ask where the slave or master servers are
Replication slave Server Admin To read binary log events from the master
Select Tables To retrieve rows from table
Show databases Server Admin To see all databases with SHOW DATABASES
Super Server Admin To use KILL thread,SET GLOBAL,CHANGE MASTER,etc.
Database management systems
Security
Privileges
Example
GRANT ALL ON TABLE master2.* TO master_student;
Grants all table-manipulation rights for database master2 to user
master_student.