Metadata, Privileges, Roles and Views

Overview

• How Oracle organizes database objects.
• How metadata is stored and queried through the data dictionary.
• How system and object privileges and roles control access.
• How views provide abstraction, security and sometimes update paths.

1. Database Objects

Oracle databases contain many types of objects:

Tables
Store data as rows and columns; each column has a data type.

CREATE TABLE employees (
  emp_id    NUMBER PRIMARY KEY,
  name      VARCHAR2(100),
  salary    NUMBER,
  dept_id   NUMBER,
  hire_date DATE
);

Common operations: INSERT, UPDATE, DELETE, SELECT.

Indexes
Speed up access to rows.
Common types: B‑tree (default), bitmap (for low‑cardinality columns).

CREATE INDEX idx_emp_id ON employees (emp_id);
DROP INDEX idx_emp_id;

Constraints
Primary key, foreign key, UNIQUE, CHECK, NOT NULL.

CREATE TABLE departments (
  dept_id   NUMBER PRIMARY KEY,
  dept_name VARCHAR2(100) UNIQUE
);

ALTER TABLE employees
  ADD CONSTRAINT fk_dept
  FOREIGN KEY (dept_id)
  REFERENCES departments(dept_id);

Views
Virtual tables defined by queries; do not store data themselves.

Triggers
PL/SQL blocks that run automatically on table/view events (INSERT, UPDATE, DELETE).

---

2. Metadata and the Data Dictionary

Metadata is “data about data”.

In Oracle it includes:
- Which tables, columns, indexes, constraints and views exist,
- Privileges and roles,
- Storage details (tablespaces, datafiles),
- Many other structural and configuration details.

Whenever you run DDL like:

ALTER TABLE students ADD test NUMBER;

Oracle updates its data dictionary, which is exposed through system views:

• USER_TABLES, ALL_TABLES, DBA_TABLES,
• USER_TAB_COLUMNS, DBA_TAB_COLUMNS,
• DBA_SYS_PRIVS, DBA_TAB_PRIVS, DBA_ROLE_PRIVS,
• USER_SYS_PRIVS, USER_TAB_PRIVS, USER_ROLE_PRIVS.

Examples:

-- Tables owned by the current user
SELECT table_name FROM user_tables;

-- System privileges of a user
SELECT * FROM dba_sys_privs WHERE grantee = 'USER1';

The dictionary is essential for:
Validating SQL (checking table/column names), enforcing constraints, and enforcing privileges and roles.

---

3. Privileges and Roles

Oracle security is based on privileges and roles.

3.1 System vs Object Privileges

System privileges
Allow operations on the database as a whole. Examples include:
- CREATE SESSION – connect to the database.
- CREATE TABLE, ALTER TABLE, DROP TABLE – manage tables.
- CREATE USER, DROP USER, CREATE ROLE – manage users and roles.
- CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE.

-- Granting system privileges
GRANT CREATE SESSION, CREATE TABLE TO user1;
GRANT CREATE USER, CREATE ROLE TO admin_user;

-- Revoking system privileges
REVOKE CREATE TABLE FROM user1;

Object privileges
Apply to specific tables, views, sequences, procedures, etc. Common privileges are:
- SELECT, INSERT, UPDATE, DELETE, EXECUTE.

GRANT SELECT ON clients TO analyste;
GRANT SELECT, INSERT ON employees TO user1;
REVOKE INSERT ON employees FROM user1;

Column‑level SELECT is not available directly in Oracle; instead, views are used to expose only certain columns.

3.2 Roles

A role is a named collection of privileges.

Benefits:
Group privileges logically (per job function), simplify administration, and make security policies easier to maintain.

Typical roles:
Reader: only SELECT on specific views/tables.
Editor: SELECT plus INSERT/UPDATE/DELETE on specific tables.
Administrator: powerful system and object privileges.

Basic role management:

-- Create a role
CREATE ROLE manager;

-- Grant privileges to the role
GRANT CREATE SESSION TO manager;
GRANT SELECT, UPDATE ON employees TO manager;

-- Grant/revoke the role for users
GRANT manager TO user1;
REVOKE manager FROM user1;

-- Drop a role
DROP ROLE manager;

• Reference: Users, roles and profiles in Oracle

3.3 Metadata About Privileges and Roles

Key dictionary views:

• DBA_SYS_PRIVS – system privileges granted to users/roles.
• DBA_TAB_PRIVS – object privileges granted to users/roles.
• DBA_ROLE_PRIVS – roles granted to users or other roles.
• ROLE_SYS_PRIVS – system privileges contained in a role.
• ROLE_TAB_PRIVS – object privileges contained in a role.
• DBA_ROLES – all roles in the database.

For the current user:
- USER_SYS_PRIVS, USER_TAB_PRIVS, USER_ROLE_PRIVS.

These views are heavily used in the workshop to verify that users have the correct permissions.

---

4. View Mechanism

4.1 Concept and Types

A view is a virtual table defined by a SELECT query.
The definition is stored as metadata; data is read from underlying tables.

Example:

CREATE VIEW vue_client AS
SELECT nom, adresse
FROM clients
WHERE pays = 'Algérie';

Two main categories:
Simple view – based on a single table, no aggregates or set operators, often updatable.
Complex view – based on multiple tables or uses joins, aggregates, etc., usually read‑only.

4.2 Advantages

1. Data security
2. Hide sensitive columns or rows.
3. Grant privileges on the view instead of the base table.
   ```sql
   CREATE VIEW employes_public AS
   SELECT id, nom, prenom FROM employes;

GRANT SELECT ON employes_public TO some_user;
```

1. Simplification

2. Encapsulate joins and filters.
   sql CREATE VIEW ventes_detaillees AS SELECT c.nom AS client, p.nom AS produit, SUM(o.quantite) AS quantite_vendue FROM commandes o JOIN clients c ON o.id_client = c.id JOIN produits p ON o.id_produit = p.id GROUP BY c.nom, p.nom;

3. Consistency and abstraction

4. Provide a stable interface independent of physical storage or table reorganization.
5. Reference: Oracle SQL views

4.3 Updatable Views

A view is generally updatable if:
- It is based on a single table,
- It has no GROUP BY, DISTINCT, aggregates or set operators,
- Primary key columns of the base table appear in the view,
- It is not declared WITH READ ONLY.

Example updatable view:

CREATE VIEW vue_employes_salaire AS
SELECT emp_id, name, salary
FROM   employees;

UPDATE vue_employes_salaire
SET    salary = salary + 1000
WHERE  emp_id = 101;

Example non‑updatable view:

CREATE VIEW emp_dept AS
SELECT e.name, d.dept_name
FROM   employees e
JOIN   departments d ON e.dept_id = d.dept_id;

UPDATE emp_dept
SET    dept_name = 'Sales'
WHERE  name = 'Dupont';  -- fails: derived from multiple tables

---

5. Key Takeaways

System privileges control what users can do at the database level; object privileges control access to specific objects.
Roles bundle privileges and simplify security administration.
Views provide abstraction, security and sometimes updatable virtual tables, and are central in real‑world designs and in the HR workshop.