This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português

English version:

Introduction

This article focus on a little known aspect of stored procedures or functions. That probably explains why it was the less voted in a recent poll I've conducted. Nonetheless it's (from my point of view) a very interesting topic. During this article I'll be referring to procedures, but I could use the term functions.
If we take a look at the sysprocedures table we'll see a field called mode. This field is just one character and the values it can contain are:

• D or d
  DBA
• O or o
  Owner
• P or p
  Protected
• R or r
  Restricted
• T or t
  Trigger

I'm not interested in all of these, but the lower case letters mean "protected" (created by the system), D is for DBA procedures. P is an old nomenclature for protected procedures. T is used for procedures defined as Trigger procedures. And then we have O and R. O for owner mode and R for restricted mode. What is the difference between them? Assume you're using informix user and you run:

CREATE PROCEDURE test()
END PROCEDURE

You'll have an OWNER mode procedure, owned by informix user. But if instead you run:

CREATE PROCEDURE myuser.test()
END PROCEDURE

You'll have a RESTRICTED mode procedure owned by myuser.
You need to have DBA privilege to create a procedure on behalfwith another user name.

Why RESTRICTED?

The reasons why the restricted mode procedures/functions were created are based on security. Let's imagine the following scenario:

1. You have two databases called db1 and db2
2. You have a user myuser with connect privileges on db1 and db2 and another user mydba with DBA privileges on db1
3. User myuser needs to be connected to db1 and run a distributed query to db2
4. The db2's DBA grants the required privileges on db2 to user myuser

Now, without the RESTRICTED mode procedures, mydba could create a local db1 procedure on behalf of myuser, and with that it could remotely access the data on db2. Note that the db2 DBA did not intend to give the privileges to anyone else beside myuser. So a local DBA could use it's privileges to abuse some of the remote privileges granted to some of the local users.
This is why the RESTRICTED mode was created. Every time we create a procedure on behalf of another user, it will be created as a RESTRICTED mode procedure. And as such any remote operatio...
{mosgoogle}

{mos_sb_discuss:4}

< Prev		Next >