vsql, example: column-level access to table-valued functions (Oracle, Postrgress, MS SQL)

maxim-ge commented 1 month ago

Access Control for Table-Valued Functions (MS SQL), Table Functions (Oracle), and Set-Returning Functions (PostgreSQL)

In all three DBMS platforms (MS SQL, Oracle, and PostgreSQL), you can generally grant access to functions, but column-level access control is not natively supported within functions themselves. To achieve column-level access control, you would typically create views that expose only the specific columns you want to grant access to.

Below are examples of granting general access and column-level access through views in each system.

1. Microsoft SQL Server (MS SQL)

Granting General Access to Table-Valued Functions (TVFs)

To grant general access to a TVF in MS SQL, use the GRANT statement:

GRANT EXECUTE ON FUNCTION [schema].[FunctionName] TO [UserName];

This allows the user to execute the function.

Column-Level Access (via Views)

MS SQL does not support direct column-level security on functions. Instead, you can create views that return only the columns the user should see, then grant SELECT permissions on the view.

Create a View with Specific Columns:

CREATE VIEW [schema].[RestrictedView] AS
SELECT Column1, Column2
FROM [schema].[FunctionName](parameters);

Grant Access to the View:

GRANT SELECT ON [schema].[RestrictedView] TO [UserName];

This approach ensures the user can access only the allowed columns from the function.

2. Oracle Database

Granting General Access to Table Functions

In Oracle, you can grant execute permissions on table functions like this:

GRANT EXECUTE ON schema.FunctionName TO UserName;

This allows the user to execute the table function.

Column-Level Access (via Views)

Oracle also does not support column-level permissions on the result set of a table function. Similar to MS SQL, you can create a view that selects specific columns from the function and grant SELECT privileges on the view.

Create a View with Specific Columns:

CREATE VIEW RestrictedView AS
SELECT Column1, Column2
FROM TABLE(FunctionName(parameters));

Grant Access to the View:

GRANT SELECT ON RestrictedView TO UserName;

This method limits user access to specific columns from the table function result.

3. PostgreSQL

Granting General Access to Set-Returning Functions (SRFs)

In PostgreSQL, functions can be granted access with the GRANT command:

GRANT EXECUTE ON FUNCTION schema.FunctionName TO UserName;

This grants the user permission to execute the set-returning function (SRF).

Column-Level Access (via Views)

PostgreSQL also lacks direct column-level access control on function result sets. You can use views to control access at the column level.

Create a View with Specific Columns:

CREATE VIEW RestrictedView AS
SELECT column1, column2
FROM FunctionName(parameters);

Grant Access to the View:

GRANT SELECT ON RestrictedView TO UserName;

This way, users only have access to the columns exposed by the view.

Conclusion

General Access: You can grant general execution access to functions in MS SQL (TVFs), Oracle (table functions), and PostgreSQL (SRFs) using the GRANT EXECUTE command.
Column-Level Access: None of these DBMS systems support column-level security within the function itself. To achieve this, create a view that exposes only the necessary columns and grant SELECT access to the view. This provides an effective way to implement column-level security while using functions.

This approach ensures you maintain fine-grained control over which columns users can access from the results of these functions.

maxim-ge commented 1 month ago

The primary difference between stored procedures and functions lies in their purpose, return types, and how they are used within database systems like MS SQL, Oracle, and PostgreSQL. Both can return rows, but they have different characteristics in terms of structure, behavior, and usage.

Here’s a detailed breakdown:

1. Purpose and Use Cases

Stored Procedures:
- Primarily used to perform a series of actions or logic (e.g., updating records, complex computations, etc.).
- May return multiple result sets (rows or even nothing).
- Procedures are often used to modify data (e.g., INSERT, UPDATE, DELETE) or perform administrative tasks (e.g., batch processing).
- Can accept input parameters and sometimes return output parameters.
- Executed with an EXECUTE or CALL statement.
- Typically not used in SQL queries (e.g., SELECT), except under specific circumstances (e.g., using EXEC in T-SQL).
Functions:
- Primarily used to perform computations or return values (such as scalars, sets, or tables).
- More suited for retrieving data, either as single values or sets of rows.
- Commonly used in SELECT queries or other SQL expressions (e.g., WHERE, GROUP BY, ORDER BY).
- Functions are deterministic and expected to return a consistent result with the same inputs.
- Often cannot perform actions that modify data (depending on the DBMS).
- Called within SQL queries like SELECT, or used in views, constraints, or calculations.

2. Return Type

Stored Procedures:
- Can return nothing, a single result set, or multiple result sets (multiple sets of rows or even complex data structures).
- Procedures may return multiple rows or tables but are not typically designed to return values that are used directly in SELECT statements.
- In some DBMS (like MS SQL), stored procedures can return output parameters or the execution status.
Functions:
- Functions return a single value or a result set.
- Scalar Functions: Return a single value (e.g., INT, VARCHAR, DATE).
- Table-Valued Functions (MS SQL) or Set-Returning Functions (PostgreSQL): Return a set of rows (similar to what a query might return).
- Deterministic: Functions always return the same output given the same input, whereas procedures are not expected to be deterministic.

3. Syntax and Invocation

Stored Procedures:
- Invoked using an EXECUTE or CALL statement.
- Example in MS SQL:
```
EXEC MyStoredProcedure @Param1, @Param2;
```
- Example in Oracle:
```
EXEC MyStoredProcedure(Param1, Param2);
```
Functions:
- Invoked as part of a SQL expression or query, or in procedural logic (e.g., SELECT, WHERE, ORDER BY, FROM).
- Example in MS SQL:
```
SELECT dbo.MyFunction(@Param1);
```
- Example in PostgreSQL:
```
SELECT * FROM MyFunction(Param1);
```

4. Modification of Data

Stored Procedures:
- Can perform DML (Data Manipulation Language) operations like INSERT, UPDATE, DELETE, and MERGE on tables.
- Designed for performing side effects, including data modification or administrative operations.
- Commonly used for batch processing or complex data modifications.
Functions:
- Generally cannot modify data in most DBMS (especially in PostgreSQL or MS SQL functions used in SQL queries).
- Functions are often required to be pure or side-effect-free (i.e., they don't change the database state).
- In some systems, there are specific exceptions (e.g., in PostgreSQL, functions marked VOLATILE can modify data, but their use in queries is limited).

5. Usability in SQL Queries

Stored Procedures:
- Not designed to be used inside SELECT or other SQL queries.
- Generally executed on their own and return result sets or perform operations.
- Example (MS SQL):
```
EXEC MyStoredProcedure;
```
- Cannot be embedded in SQL queries like SELECT statements.
Functions:
- Can be embedded directly in SELECT, WHERE, ORDER BY, or FROM clauses.
- Table-valued or set-returning functions can return result sets similar to a query.
- Example (PostgreSQL):
```
SELECT * FROM MyFunction(Param1);
```

6. Flexibility in Logic and Flow Control

Stored Procedures:
- More flexible than functions for complex logic, error handling, and flow control.
- Can use procedural constructs like loops, conditionals, and transactions (BEGIN, COMMIT, ROLLBACK).
- Can call other procedures or functions.
Functions:
- More restrictive in terms of what they can do. For instance, in many DBMS, functions cannot start transactions.
- Functions are primarily focused on returning values and typically avoid extensive control flow logic.

7. Performance Considerations

Stored Procedures:
- Typically optimized for performance in scenarios where multiple actions or queries are executed together (e.g., batch processing).
- May be slower than functions when used purely for fetching data because they can involve complex operations or side effects.
Functions:
- Usually optimized for quick data retrieval, especially in the case of table-valued or set-returning functions.
- Often cached or optimized for repeated executions with the same parameters (depending on the DBMS).

Summary of Key Differences

Feature	Stored Procedures	Functions
Purpose	Perform actions, modify data	Return values or sets of data
Return Types	Can return multiple result sets	Return scalar or table/set values
Invocation	`EXECUTE` or `CALL`	Used in `SELECT`, `FROM`, `WHERE`, etc.
Data Modification	Can modify data (e.g., `INSERT`, `UPDATE`, `DELETE`)	Generally cannot modify data (exceptions exist)
Use in SQL Queries	Not usable in `SELECT` queries	Usable in SQL queries
Control Flow	Flexible, can use loops, conditions, transactions	Limited, primarily return values
Performance Focus	Optimized for complex operations or batch processing	Optimized for fast data retrieval

In general, stored procedures are better for performing complex operations, especially those that involve modifying data, while functions are more appropriate for retrieving data, performing computations, or returning results in SQL queries.

maxim-ge commented 1 month ago

Below are examples of stored procedures that return multiple result sets in MS SQL, Oracle, and PostgreSQL.

1. Microsoft SQL Server (MS SQL)

In MS SQL, a stored procedure can return multiple result sets by executing multiple SELECT queries within the procedure.

Example: Stored Procedure Returning Multiple Result Sets

CREATE PROCEDURE dbo.GetMultipleResultSets
AS
BEGIN
    -- First result set
    SELECT ProductID, ProductName FROM Products WHERE CategoryID = 1;

    -- Second result set
    SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE OrderDate > '2023-01-01';

    -- Third result set
    SELECT EmployeeID, FirstName, LastName FROM Employees;
END;

Executing the Stored Procedure:

EXEC dbo.GetMultipleResultSets;

When this stored procedure is executed, it will return three separate result sets: one for products, one for orders, and one for employees.

2. Oracle Database

In Oracle, stored procedures do not return result sets directly, but you can return multiple result sets by using REF CURSOR.

Example: Stored Procedure Returning Multiple Result Sets Using REF CURSOR

CREATE OR REPLACE PROCEDURE GetMultipleResultSets(
    p_cursor1 OUT SYS_REFCURSOR,
    p_cursor2 OUT SYS_REFCURSOR,
    p_cursor3 OUT SYS_REFCURSOR
)
AS
BEGIN
    -- First result set
    OPEN p_cursor1 FOR
    SELECT ProductID, ProductName FROM Products WHERE CategoryID = 1;

    -- Second result set
    OPEN p_cursor2 FOR
    SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE OrderDate > TO_DATE('2023-01-01', 'YYYY-MM-DD');

    -- Third result set
    OPEN p_cursor3 FOR
    SELECT EmployeeID, FirstName, LastName FROM Employees;
END;

Executing the Stored Procedure:

DECLARE
    cursor1 SYS_REFCURSOR;
    cursor2 SYS_REFCURSOR;
    cursor3 SYS_REFCURSOR;
BEGIN
    -- Call the stored procedure
    GetMultipleResultSets(cursor1, cursor2, cursor3);

    -- Fetch the first result set
    FETCH cursor1 INTO ...

    -- Fetch the second result set
    FETCH cursor2 INTO ...

    -- Fetch the third result set
    FETCH cursor3 INTO ...
END;

Here, you would fetch and process each result set one by one using the cursors.

3. PostgreSQL

In PostgreSQL, stored procedures (introduced in version 11) can return multiple result sets using the RETURN QUERY statement in combination with OUT parameters or by using set-returning functions for more complex cases.

Example: Stored Procedure Returning Multiple Result Sets

CREATE OR REPLACE PROCEDURE GetMultipleResultSets()
LANGUAGE plpgsql
AS $$
BEGIN
    -- First result set
    RETURN QUERY
    SELECT ProductID, ProductName FROM Products WHERE CategoryID = 1;

    -- Second result set
    RETURN QUERY
    SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE OrderDate > '2023-01-01';

    -- Third result set
    RETURN QUERY
    SELECT EmployeeID, FirstName, LastName FROM Employees;
END;
$$;

Executing the Stored Procedure:

CALL GetMultipleResultSets();

When you call the procedure, PostgreSQL will return multiple result sets, each one as a separate result set.

Summary of Differences in Behavior

MS SQL: Allows direct execution of multiple SELECT statements in the procedure, and all result sets are returned when the procedure is executed.
Oracle: Uses REF CURSOR to return multiple result sets, requiring the use of cursors to fetch each result set individually.
PostgreSQL: Uses RETURN QUERY to return multiple result sets, allowing each RETURN QUERY to emit a new result set during execution.

Each DBMS has its own mechanism, but in all cases, the stored procedures are capable of returning multiple result sets to the caller.

voedger / kb