17.10 - Example: Specifying a SQL SECURITY—OWNER - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

When you specify SQL SECURITY OWNER, Vantage verifies and applies the privileges of the owner of the procedure, which means those of its containing database or user.

The SQL SECURITY OWNER clause is not a valid option for SQL procedure creation unless you have been granted the CREATE OWNER PROCEDURE privilege to permit you to create an SQL procedure in another database. CREATE OWNER PROCEDURE is an explicit right that must be granted explicitly to a user or database.

Vantage uses the owner identifier as the default qualifier for implicit qualification of any unqualified object references within the SQL statements in the procedure.

The following example shows the OWNER case for static SQL. In this example, user_1 creates the SQL procedure dyn_dml in the SYSLIB database, and user_2 calls SYSLIB.dyn_dml.

Vantage verifies the following privileges for this static SQL example.

AT this time … Vantage verifies the following privileges in the order indicated …
compilation user_1 has the CREATE OWNER PROCEDURE privilege on the SYSLIB database.

The SYSLIB database has the INSERT privilege on table SYSLIB.t1.

execution user_2 has the EXECUTE PROCEDURE privilege on the SQL procedure SYSLIB.dyn_dml.

The SYSLIB database has the INSERT privilege on table SYSLIB.t1.

     .LOGON user_1,user_1
     .COMPILE FILE sp.spl
     /* sp.spl file
     CREATE PROCEDURE SYSLIB.static_dml()
     SQL SECURITY OWNER
     BEGIN
       INSERT INTO t1
       SELECT 1,1;
     END;
     /*
     .LOGON user_2,user_2
     CALL SYSLIB.static_dml;

For the dynamic SQL case, Vantage verifies the following privileges:

AT this time … Vantage verifies the following privileges in the order indicated …
compilation user_1 has either the CREATE OWNER PROCEDURE privilege or the CREATE PROCEDURE privilege on the SYSLIB database.
execution user_2 has the EXECUTE PROCEDURE privilege on the SQL procedure SYSLIB.dyn_dml.

The SYSLIB database has the INSERT privilege on table SYSLIB.t1.

     .LOGON user_1,user_1
     .COMPILE FILE sp.spl
     /* sp.spl file
     CREATE PROCEDURE syslib.dyn_dml()
     SQL SECURITY OWNER
     BEGIN
         CALL dbc."sysexecsql" ('INSERT INTO t1 (1,1);');
     END;
     /*
     .LOGON user_2,user_2
     CALL SYSLIB.dyn_dml();