Security

Physical modelIn today's connected world, where access to many databases is through Web pages or over networks, data security and privacy are increasingly important issues. This topic covers:

  • What is security?:  provides an overview of security issues
  • Data security plan: examines how to manage security

 

What is security?

Web link
The Spy in your computer

Security is the protection of data against unauthorized access whether that access involves retrieval, updating or corruption of the data. Security violations can give rise to serious ethical, legal and financial problems.

The following news article, The Spy in your Computer illustrates just how much care is needed to protect privacy.

Textbook reading
Ch11 pp 506-511

The next reading, Managing Data Security, gives an overview of data security.

Have a go: risk management

 

Data security plan

Textbook reading
Ch6 pp 311-315

A data security plan encompasses

  • software protections
  • administrative policies and procedures
  • physical protections
Textbook reading
Ch11 pp 511-519

The next two readings concentrate on the software and administrative aspects. The first reading, Using and Defining Views, introduces VIEWs whose role in security is explained in the second reading Database Software Data Security Features and Security Policies and Procedures.

Have a go: security at CDU

 

Getting your hands dirty


Grant and Revoke

Oracle provides the GRANT and REVOKE statements to allow or disallow access to database objects, an example of a software protection.

Start by reading about these two commands: GRANT and REVOKE

Form yourself into groups of two to five. Try the following:

1. Try accessing another member of your groups INVOICE table. What happens?

Remember

To access sXXXXXX INVOICE table you will need to qualify it, namely
     SELECT * FROM sXXXXXX.INVOICE

 

2. As an owner of a table GRANT permission to another user to use that table. Try granting different privileges such as SELECT, UPDATE etc. What happens if you have for example DELETE and not SELECT? When could this be useful?

3. An owner grants permission to another say B who is turn grants permission to C. What happen if the owner revokes B permission? Where does this leave C? Again consider when each of these options could be useful.

For this activity you are not expected to learn the syntax of the GRANT and REVOKE statements. Rather you should get a feeling for the level and type of control it allows and ways that it could be used in various situations.

System tables

Textbook reading
Ch7 pp 352-353
Oracle features only

A DBMS such as Oracle stores information about the databases (e.g. tables created, users, privileges, access times etc) in system-created tables. These systems tables as well as helping maintain security are also a valuable resource. The following 1-page reading, Data Dictionary Facilities, list some useful systems tables.

Using the list of tables on p. 353 in your textbook, write these queries:

Remember

You cannot use DBA as you do not have databse administrator privileges. Replace DBA with:
USER: lists only the information relevant to you
ALL: lists the information relevant for all users

 

1. Describe all the tables you have created in the database

View the answer

SELECT * FROM USER_TABLES;
The output from this can be hard to read as so many columns are returned. The following is much easier to decipher:
SELECT table_name FROM USER_TABLES;

Remember

To find out about a system table use can use the DESCRIBE command.
For example: DESCRIBE USER_TABLES;

 

2. Can you find the names of all the tables for another student?

View the answer

No. As you are not a DBA you are not allowed to look at another student's tables. To verify this find all the owners you can see using SELECT DISTINCT owner FROM ALL_TABLES;

3. Find the privileges you have granted on your tables? Have they been granted to anyone else?

View the answer

SELECT * FROM USER_TAB_PRIVS;
and look at the Grantor column.

Views

1.In the previous tab you used the USER tables. Each user gets their own information when using these tables. How do you think this could be implemented?

View the answer

This could be implemented using VIEWs. For example the statement could look something like:

CREATE VIEW USER_TABLES
AS
SELECT <list of columns>
FROM DBA_TABLES
WHERE OWNER = <current user id>;

This is an example of using VIEWs to implement security.

2. Using the Student Tables s

a) Create a VIEW showing the details of all coordinators who are currently advising at least one student. View the answer

CREATE VIEW ActiveCoords AS
SELECT DISTINCT c.coord_no, c.name
FROM Coordinator c, Student s
WHERE c.coord_no=s.coord_no;

b) Write a query to find the names of these coordinators View the answer

SELECT name
FROM ActiveCoords;

c) Could you add another coordinator by using an INSERT statement on this VIEW? View the answer

No, this VIEW is not updateable as it includes the keyword DISTINCT and involves a join acrross mutltiple tables. It also would not make sense - an active coordinator is one who advises a student and adding a new coordinator without a student can't by definition be active.