Table of Contents

Overview DCL
GRANT - Grant object privileges
REVOKE - revoke rights
System Privileges
Roles in Oracle
WITH GRANT OPTION and WITH ADMIN OPTION
Typical authorization scenarios
Data Dictionary for Rights and Roles
Security Policies and Best Practices
Error images and troubleshooting
Practical example class project
Summary and outlook

DCL - Data Control Language: Rights and roles in Oracle

Subject: Information Technology - Database Systems School level: 11th grade - HTL Informatik Prerequisites: SQL introduction, DQL, DML, DDL, SCOTT schema Author: HTL Pinkafeld - IF/IT

1. Overview DCL

1.1 What is DCL?

DCL (Data Control Language) regulates in SQL who is allowed to carry out which actions on database objects.

DCL-Anweisungen:
|- GRANT  -> Rechte vergeben
|- REVOKE -> Rechte entziehen
\- Use roles to bundle rights

1.2 DCL compared to DDL and DML

Area	Purpose	Examples
DDL	Define structure	CREATE TABLE, ALTER TABLE
DML	Edit data	INSERT, UPDATE, DELETE
DCL	Control access	GRANT, REVOKE

Important: DCL is a central building block for database security, data protection and traceability.

1.3 Principle of Minimum Rights

Users should only be given the rights that they really need for their tasks:

Read instead of full access when writing is not necessary
Roles instead of individual rights per user
Prompt removal of rights that are no longer required

2. GRANT - Grant object privileges

2.1 Basic syntax

GRANT privileg [, privileg ...]
ON objektname
TO benutzer_oder_rolle;

2.2 Important object privileges

privilege	Meaning
SELECT	Read data
INSERT	Insert new lines
UPDATE	Modify existing lines
DELETE	Delete lines
REFERENCES	Set foreign key on table
EXECUTE	Execute procedure/function

2.3 Examples

-- Read permission to table EMP to user STUDENT1
GRANT SELECT
ON EMP
TO SCHUELER1;

-- Multiple rights at the same time
GRANT SELECT, INSERT, UPDATE
ON PROJEKT
TO TEAM_A;

-- Right to execute a function
GRANT EXECUTE
ON BERECHNE_NOTE
TO LEHRER_ROLLE;

2.4 Object privileges in the SCOTT schema

In class we often work with the user SCOTT. The tables EMP, DEPT, BONUS and SALGRADE belong to SCOTT in this case.

This is important for GRANT/REVOKE:

Only the owner (or a DBA) can grant object privileges to the object.
The object should be qualified outside of its own schema (Schema.Object).

-- From SCOTT's perspective: Read permission to EMP to APP_USER
GRANT SELECT
ON SCOTT.EMP
TO APP_USER;

-- APP_USER darf auch auf DEPT lesen
GRANT SELECT
ON SCOTT.DEPT
TO APP_USER;

2.5 Fine-grained object privileges (column level)

UPDATE can be restricted to certain columns in Oracle. For example, a user can: B. Maintain salary, but do not change name or department.

-- APP_USER is only allowed to change SAL and COMM in SCOTT.EMP
GRANT UPDATE (SAL, COMM)
ON SCOTT.EMP
TO APP_USER;

-- Optional additional reading rights
GRANT SELECT
ON SCOTT.EMP
TO APP_USER;

Practical benefit: Implement the minimal principle within a table.

2.6 Typical SCOTT scenario (teaching operation)

Initial situation:

SCOTT owns the master tables.
REPORT_USER is intended to read only.
HR_USER should be partially maintained in EMP.

-- 1) Reiner Lesebenutzer
GRANT SELECT ON SCOTT.EMP      TO REPORT_USER;
GRANT SELECT ON SCOTT.DEPT     TO REPORT_USER;
GRANT SELECT ON SCOTT.SALGRADE TO REPORT_USER;

-- 2) Maintenance user with limited rights
GRANT SELECT ON SCOTT.EMP TO HR_USER;
GRANT UPDATE (SAL, COMM) ON SCOTT.EMP TO HR_USER;

Control over Data Dictionary (as SCOTT or DBA):

-- Which object privileges have been granted to SCOTT objects?
SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE
FROM   ALL_TAB_PRIVS
WHERE  OWNER = 'SCOTT'
ORDER BY TABLE_NAME, GRANTEE, PRIVILEGE;

3. REVOKE - revoke rights

3.1 Basic syntax

REVOKE privileg [, privileg ...]
ON objektname
FROM benutzer_oder_rolle;

3.2 Examples

-- Schreibrechte entfernen
REVOKE INSERT, UPDATE
ON PROJEKT
FROM TEAM_A;

-- Leserecht entziehen
REVOKE SELECT
ON EMP
FROM SCHUELER1;

3.3 Note dependencies

When rights are revoked, there can be knock-on effects if rights have been passed on or objects depend on each other.

4. System Privileges

System privileges allow general actions in a schema or database.

4.1 Typical system privileges

privilege	Meaning
CREATE SESSION	Login to the database
CREATE TABLE	Create tables
CREATE VIEW	Create views
CREATE SEQUENCE	Create sequences
CREATE PROCEDURE	Create procedures/functions

4.2 Examples

-- Benutzer darf sich anmelden
GRANT CREATE SESSION TO SCHUELER1;

-- User is allowed to create tables and views in their own schema
GRANT CREATE TABLE, CREATE VIEW
TO SCHUELER1;

Note: System privileges are more powerful than object privileges and should be assigned restrictively.

5. Roles in Oracle

5.1 Why roles?

Roles combine rights and simplify administration.

-- Rolle erstellen
CREATE ROLE LESE_ROLLE;

-- Rechte an Rolle vergeben
GRANT SELECT
ON EMP
TO LESE_ROLLE;

-- Rolle an Benutzer vergeben
GRANT LESE_ROLLE
TO SCHUELER1;

5.2 Advantages

Less administrative effort
Uniform rights for groups
Faster adjustments when changing roles

5.3 Remove the roles again

REVOKE LESE_ROLLE
FROM SCHUELER1;

6. WITH GRANT OPTION and WITH ADMIN OPTION

6.1 WITH GRANT OPTION (object privileges)

GRANT SELECT
ON EMP
TO SCHUELER1
WITH GRANT OPTION;

This allows SCHUELER1 to pass on the object rights received to other users.

6.2 WITH ADMIN OPTION (system privileges and roles)

GRANT CREATE TABLE
TO SCHUELER1
WITH ADMIN OPTION;

GRANT LESE_ROLLE
TO SCHUELER1
WITH ADMIN OPTION;

This allows SCHUELER1 to grant or revoke the privilege or role to others.

Security aspect: These options should only be assigned specifically, otherwise rights can spread uncontrollably.

7. Typical authorization scenarios

7.1 Read access for reporting

GRANT SELECT
ON KUNDEN
TO REPORTING_ROLLE;

7.2 Write access for application

GRANT SELECT, INSERT, UPDATE
ON BESTELLUNG
TO APP_ROLLE;

7.3 Separation of developer and runtime rights

Developers receive additional CREATE rights in the test system
Production users only receive necessary runtime rights
DDL rights in production for administrators only

8. Data Dictionary for Rights and Roles

Important views for controlling permissions:

View	Contents
USER_TAB_PRIVS	Object rights of the current user
USER_SYS_PRIVS	System rights of the current user
USER_ROLE_PRIVS	Roles of the current user
ALL_TAB_PRIVS	Object rights to accessible objects
DBA_ROLE_PRIVS	Roles of all users (DBA)

8.1 Example queries

-- What roles does the current user have?
SELECT *
FROM   USER_ROLE_PRIVS;

-- What system privileges does the current user have?
SELECT *
FROM   USER_SYS_PRIVS;

-- What object privileges are there on your own tables?
SELECT *
FROM   USER_TAB_PRIVS;

9. Security Policies and Best Practices

Assigning rights via roles instead of directly to individual users
No blanket ALL PRIVILEGES awards
Regular eligibility reviews
Separation of development, test and production systems
Log critical actions (auditing)

-- Example: Only necessary rights for a reporting role
GRANT CREATE SESSION TO REPORTING_ROLLE;
GRANT SELECT ON KUNDEN TO REPORTING_ROLLE;
GRANT SELECT ON BESTELLUNG TO REPORTING_ROLLE;

10. Error images and troubleshooting

10.1 ORA-01045: user lacks CREATE SESSION privilege

The user cannot log in because the right is missing:

GRANT CREATE SESSION TO SCHUELER1;

10.2 ORA-00942: table or view does not exist

Common Causes:

Object name incorrect or different schema
No SELECT right on the object

GRANT SELECT ON LEHRER.EMP TO SCHUELER1;

10.3 ORA-01031: insufficient privileges

The action is basically known, but the user does not have enough rights.

11. Practical example class project

Initial situation:

User TEAM_READ should only read
User TEAM_EDIT is supposed to read and write
The KURS_ADMIN role should be allowed to create additional structures

-- Rollen anlegen
CREATE ROLE TEAM_READ;
CREATE ROLE TEAM_EDIT;
CREATE ROLE KURS_ADMIN;

-- Objektrechte vergeben
GRANT SELECT ON PROJEKT TO TEAM_READ;
GRANT SELECT, INSERT, UPDATE ON PROJEKT TO TEAM_EDIT;

-- System rights for admin role
GRANT CREATE TABLE, CREATE VIEW TO KURS_ADMIN;

-- Rollen an Benutzer vergeben
GRANT TEAM_READ TO USER_A;
GRANT TEAM_EDIT TO USER_B;
GRANT KURS_ADMIN TO USER_C;

Advantage: Roles can be adjusted as needed without having to manually change each individual user permission.

12. Summary and outlook

DCL controls access to database objects and system functions.
Rights are granted with GRANT and revoked with REVOKE.
Roles make permissions clear and maintainable.
The data dictionary helps control and analyze security.

In the next step, the topic can be deepened through practical exercises with roles, revocation of rights and error analysis.