Checking Privileges when Executing SQL Statements

Privileges and Privilege Subjects

Ways of Logging In

User Privileges when Executing Stored Procedures and Evaluating Queries

When a user executes stored procedures or evaluates queries, his user privileges are being checked. However there are cases when it's necessary to bypass these privileges to a certain extent:

• Some action may be carried out only in a context of a certain procedure. E.g. an application may contain a log of executed actions. User don't have the privileges to add arbitrary records to the log, however it's necessary to log a call to a certain procedure. It's therefore desired to allow this procedure to be logged even if the calling user don't have sufficient privileges to do so.
• An user needs to evaluate a query to select some records that he or she does have sufficient privileges to read, however he or she puts conditions on column values that he's not allowed to read. Such a query tampers the access privileges, however there should be a way how to evaluate this query.

The application author has the possibility to mark procedures and queries whose execution or evaluation won't be restricted by access privileges in a scheme. It's called running the procedure or query as an admin mode object. Privileges to call such a procedure or a query will still be checked, however privileges to objects belonging to the same scheme won't be checked during its evaluation.

These procedures and queries are marked by calling the Admin_mode function or by selecting the checkbox Admin mode object on the Properties tab of the Output window.

Only a user assigned to the Author role may mark this procedure or query. This mark is persistent and therefore is saved when the server is shut down or when exporting and importing the application. In a locked application it's not allowed to set the admin mode object specification (a locked application doesn't allow to assign anyone to the Author role).

The following query selects those documents from the DOCTAB table, that the user can read and that contain the word "Mississippi":

SELECT Contents FROM Doctab WHERE Has_select_privil(Contents,2) AND Position("Mississippi" In Contents)>0

This query cannot be evaluated directly, because the user doesn't have the global privilege to access the Contents column and cannot use it in the WHERE condition in the Position function. If the query is marked to be evaluated without privileges checking then it works and doesn't tamper the access privileges.

A Survey of Admin Mode Privileges

When calling a procedure in an admin mode object users are allowed to:

• freely access all data in the same scheme (read, write, record inserting and deleting),
• use all objects from the same scheme, such as calling stored procedure and evaluating queries,
• assign users and user groups to the roles defined in the same scheme,
• execute the ALTER SEQUENCE command for all sequences in the scheme,
• assign data privileges and privileges to execute procedures in the same scheme.

If a procedure being run as a admin mode object opens and returns a cursor, full access to data in this cursor is not guaranteed. These privileges are evaluated according to the subject working with this cursor. Admin mode object is valid only inside the procedure body, a resulting cursor passed out of the procedure body won't inherit it.

Checking Privileges when Executing SQL Statements

Privileges and Privilege Subjects

Ways of Logging In