NEBC Database Course 2008 Database Users And Security ...

newshumansvilleΔιαχείριση Δεδομένων

16 Δεκ 2012 (πριν από 4 χρόνια και 10 μήνες)

138 εμφανίσεις

NEBC Database Course 2008
Database Users And Security
Backing-Up Data
Tim Booth : tbooth@ceh.ac.uk
Overview
Areas covered:

Controlling who can connect

Table-level privileges

Defining user groups

Remote connections to PostgreSQL running on Bio-Linux

Backups

Recovery
Importance of Security
Unless you are running a major, multi-user database, some of
the security facilities are overkill. However, you should consider
the following:

As the super-user, you are only a typo away from disaster.
DROP TABLE can't be undone!

If you are considering public browsing of the database, you
should at least have a read-only account.

Is there sensitive/unpublished information in the database?
Creating Users
Administrative users may add other users (aka
user roles
).
In PostgreSQL this is done either by issuing an SQL command or
with the
createuser
command-line program.
SQL version: note that everything after the first line is optional.
CREATE USER 
goldie
WITH ENCRYPTED PASSWORD '
n14hcniK001
'
CREATEDB
NOCREATEUSER
VALID UNTIL '
16 Mar 2004
'
User Privileges
A administrator or 'super-user' has full rights to all database
objects.
The owner of the object also has this ability.
A new user must be granted rights to view or modify objects in
the database.
Rights may be assigned with the GRANT and REVOKE commands:
eg:
GRANT UPDATE ON
customer
TO
goldie;
Our new user may now update the information held in the
customer table.
Privileges on Tables
For a given user, four main privileges apply to each table, view or
sequence. These correspond to the four SQL statements which
manipulate data.
SELECT
: Ability to see the data
UPDATE
/
DELETE
: Ability to modify the data
INSERT
: Ability to append new rows
To allow our user to see data about movies, and alter or delete
reservations:
GRANT
SELECT
ON
movie
TO
goldie
;
GRANT
ALL
ON
reservation
TO
goldie
;
Scope of privileges

User accounts are global to the database
server.

Server can have many databases

Database can have many schemas

Schema can have many objects

(Table can have many rows)
User Groups

Setting individual privileges for every user on every table is
tiresome.

As well as
user roles
you can define
group roles
. All users in
the group, including any added later, will inherit the group
rights.

A user can be a member of any number of groups.

You can grant privileges to
PUBLIC
so that all users have the
specified permissions.
Modifying Groups

CREATE ROLE
staff
;

GRANT
staff
TO
goldie
;

GRANT INSERT ON
video
TO
staff
;

CREATE USER
tim
IN ROLE
staff;
Sack Goldie (possibly for problems of bad attitude):

REVOKE
staff
FROM
goldie;

DROP USER
goldie;
Make our video list public:

GRANT SELECT ON
video
TO
PUBLIC;
MySQL
See the MYSQL documentation. Briefly:

Users are created with GRANT
GRANT ALL on 
mydb
.* TO 
goldie
@% 
IDENTIFIED BY “
password
”;
DELETE FROM user WHERE User = “
goldie
”;

MySQL does not have groups, but you can assign privileges at
the table or column level. You can also set different privileges
when the same user connects from different hosts.

Always remember to
FLUSH PRIVILEGES;
Remote Connections to
PostgreSQL

For security reasons, non-local access is disabled by default

When you do enable it, PostgreSQL defaults to using encrypted
connections.

You need to modify
postgresql.conf 
and
 pg_hba.conf
User Authentication

pg_hba.conf

controls user authentication. Modify the
examples in the file to your needs.
Basic methods:
trust =
No authentication at all – use only for debugging!
ident sameuser =
The default. You can connect as the user with
the same name as your Linux login.
md5 =
Users will be challenged for a password, which will be
transmitted securely. Recommended for all remote
connections.
Backups

You do backup your files, don't you?

Most sites offer some form of backup facility, or you can use
the CD writer on the machine.

You can lose data for any number of reasons.

Human error

Hardware failure

Malicious action/hacking

Or you might simply want to copy your database to another
machine
Backups
It is possible to backup a database by simply saving the
/home/db/postgres
working directories as part of your regular
backups. This is fine for disaster recovery, but not ideal
because:

These files cannot be read by newer or older versions of the
PostgreSQL server.

There is no way to control what data you backup or restore.

There is no way to see what a backup file contains.

If you accidentally leave PostgreSQL running when you restore
then
bad things
will happen.
The one-shot command (as user manager):
sudo ­u postgres pg_dumpall > dump.sql
Selective Backup and Restoring
Y
ou can also back up a single database using pg_dump:
pg_dump ­­create mydb > mydb.sql
If you want to restore the database onto a new machine, you also
need to backup the system tables, to get back all the users and
groups – and you will need to restore these first!:
pg_dumpall ­g > globdump.sql

To restore backups, use
psql ­f dumpfile.sql 
eg.
:
psql ­f globdump.sql
Regaining Control

It is possible to lock yourself out of your own database, even if
you are the manager/root user on your machine!

If you have have set a password but forgotten it, you can
temporarily modify the pg_hba.conf file to 'trust' all local users.
Then:
sudo pg_ctl reload
psql ­U postgresql

On MySQL, you can stop the server and restart giving the
­­
skip

grant

tables
option. You will be permitted to login
without a password. Set a new password, then
FLUSH
PRIVILEGES
.
References and Further
Information
Any database administrator needs the manual to hand!
http://www.postgresql.org/docs/8.3/interactive/sql-grant.html
If you have a PostgreSQL book, such as Worsley/Drake, be aware
that there have been several changes to the details of user
admin in recent releases of PG – it is always best to consult the
specific documentation for your version.
Also on Bio-Linux see:
zless /usr/share/doc/postgresql-8.3/README.Debian.gz