3.2.8 Program Security

Table of Contents Previous Next


3 Stored Procedure Language : 3.2 SPL Programs : 3.2.8 Program Security

Security over what user may execute an SPL program and what database objects an SPL program may access for any given user executing the program is controlled by the following:
Privileges granted on the database objects (including other SPL programs) which a program attempts to access.
An SPL program (function, procedure, or package) can begin execution only if any of the following are true:
The current user has been granted EXECUTE privilege on the SPL program, or
The current user inherits EXECUTE privilege on the SPL program by virtue of being a member of a group which does have such privilege, or
EXECUTE privilege has been granted to the PUBLIC group.
Whenever an SPL program is created in Advanced Server, EXECUTE privilege is automatically granted to the PUBLIC group by default, therefore, any user can immediately execute the program.
This default privilege can be removed by using the REVOKE EXECUTE command. The following is an example:
Explicit EXECUTE privilege on the program can then be granted to individual users or groups.
Now, user, john, can execute the list_emp program; other users who do not meet any of the conditions listed at the beginning of this section cannot.
A database object inside an SPL program may either be referenced by its qualified name or by an unqualified name. A qualified name is in the form of schema.name where schema is the name of the schema under which the database object with identifier, name, exists. An unqualified name does not have the “schema.” portion. When a reference is made to a qualified name, there is absolutely no ambiguity as to exactly which database object is intended – it either does or does not exist in the specified schema.
$user in the above search path is a generic placeholder that refers to the current user so if the current user of the above session is enterprisedb, an unqualified database object would be searched for in the following schemas in this order – first, enterprisedb, then public, then sys, and finally, dbo.
Note: The concept of the search path is not compatible with Oracle databases. For an unqualified reference, Oracle simply looks in the schema of the current user for the named database object. It also important to note that in Oracle, a user and his or her schema is the same entity while in Advanced Server, a user and a schema are two distinct objects.
Once an SPL program begins execution, any attempt to access a database object from within the program results in a check to ensure the current user has the authorization to perform the intended action against the referenced object. Privileges on database objects are bestowed and removed using the GRANT and REVOKE commands, respectively. If the current user attempts unauthorized access on a database object, then the program will throw an exception. See Section 3.5.7 for information about exception handling.
When an SPL program is about to begin execution, a determination is made as to what user is to be associated with this process. This user is referred to as the current user. The current user’s database object privileges are used to determine whether or not access to database objects referenced in the program will be permitted. The current, prevailing search path in effect when the program is invoked will be used to resolve any unqualified object references.
The selection of the current user is influenced by whether the SPL program was created with definer’s right or invoker’s rights. The AUTHID clause determines that selection. Appearance of the clause AUTHID DEFINER gives the program definer’s rights. This is also the default if the AUTHID clause is omitted. Use of the clause AUTHID CURRENT_USER gives the program invoker’s rights. The difference between the two is summarized as follows:
If a program has definer’s rights, then the owner of the program becomes the current user when program execution begins. The program owner’s database object privileges are used to determine if access to a referenced object is permitted. In a definer’s rights program, it is irrelevant as to which user actually invoked the program.
If a program has invoker’s rights, then the current user at the time the program is called remains the current user while the program is executing (but not necessarily within called subprograms – see the following bullet points). When an invoker’s rights program is invoked, the current user is typically the user that started the session (i.e., made the database connection) although it is possible to change the current user after the session has started using the SET ROLE command. In an invoker’s rights program, it is irrelevant as to which user actually owns the program.
In the following example, a new database will be created along with two users – hr_mgr who will own a copy of the entire sample application in schema, hr_mgr; and sales_mgr who will own a schema named, sales_mgr, that will have a copy of only the emp table containing only the employees who work in sales.
The procedure list_emp, function hire_clerk, and package emp_admin will be used in this example. All of the default privileges that are granted upon installation of the sample application will be removed and then be explicitly re-granted so as to present a more secure environment in this example.
Programs list_emp and hire_clerk will be changed from the default of definer’s rights to invoker’s rights. It will be then illustrated that when sales_mgr runs these programs, they act upon the emp table in sales_mgr’s schema since sales_mgr’s search path and privileges will be used for name resolution and authorization checking.
Programs get_dept_name and hire_emp in the emp_admin package will then be executed by sales_mgr. In this case, the dept table and emp table in hr_mgr’s schema will be accessed as hr_mgr is the owner of the emp_admin package which is using definer’s rights. Since the default search path is in effect with the $user placeholder, the schema matching the user (in this case, hr_mgr) is used to find the tables.
As user enterprisedb, create the hr database:
Create a subset of the emp table owned by sales_mgr in sales_mgr’s schema.
In the above example, the GRANT USAGE ON SCHEMA command is given to allow sales_mgr access into hr_mgr’s schema to make a copy of hr_mgr’s emp table. This step is required in Advanced Server and is not compatible with Oracle databases since Oracle does not have the concept of a schema that is distinct from its user.
While connected as user, hr_mgr, add the AUTHID CURRENT_USER clause to the list_emp program and resave it in Advanced Server. When performing this step, be sure you are logged on as hr_mgr, otherwise the modified program may wind up in the public schema instead of in hr_mgr’s schema.
While connected as user, hr_mgr, add the AUTHID CURRENT_USER clause to the hire_clerk program.
Also, after the BEGIN statement, fully qualify the reference, new_empno, to hr_mgr.new_empno in order to ensure the hire_clerk function call to the new_empno function resolves to the hr_mgr schema.
When resaving the program, be sure you are logged on as hr_mgr, otherwise the modified program may wind up in the public schema instead of in hr_mgr’s schema.
While connected as user, hr_mgr, grant the privileges needed so sales_mgr can execute the list_emp procedure, hire_clerk function, and emp_admin package. Note that the only data object sales_mgr has access to is the emp table in the sales_mgr schema. sales_mgr has no privileges on any table in the hr_mgr schema.
Connect as user, sales_mgr, and run the following anonymous block:
The table and sequence accessed by the programs of the anonymous block are illustrated in the following diagram. The gray ovals represent the schemas of sales_mgr and hr_mgr. The current user during each program execution is shown within parenthesis in bold red font.
Selecting from sales_mgr’s emp table shows that the update was made in this table.
The following diagram shows that the SELECT command references the emp table in the sales_mgr schema, but the dept table referenced by the get_dept_name function in the emp_admin package is from the hr_mgr schema since the emp_admin package has definer’s rights and is owned by hr_mgr. The default search path setting with the $user placeholder resolves the access by user hr_mgr to the dept table in the hr_mgr schema.
While connected as user, sales_mgr, run the hire_emp procedure in the emp_admin package.
This diagram illustrates that the hire_emp procedure in the emp_admin definer’s rights package updates the emp table belonging to hr_mgr since the object privileges of hr_mgr are used, and the default search path setting with the $user placeholder resolves to the schema of hr_mgr.
Now connect as user, hr_mgr. The following SELECT command verifies that the new employee was added to hr_mgr’s emp table since the emp_admin package has definer’s rights and hr_mgr is emp_admin’s owner.

3 Stored Procedure Language : 3.2 SPL Programs : 3.2.8 Program Security

Table of Contents Previous Next