program (function, procedure, or package) can begin execution only if any of the following are true:
|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
privilege has been granted to the PUBLIC
Whenever an SPL
program is created in Advanced Server
privilege is automatically granted to the PUBLIC
group by default, therefore, any user can immediately execute the program.
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
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.
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
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
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.
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.
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.
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
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
table. This step is required in Advanced Server
and is not compatible with Oracle databases
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
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
While connected as user, hr_mgr
, grant the privileges needed so sales_mgr
can execute the list_emp
function, and emp_admin
package. Note that the only data object sales_mgr
has access to is the emp
table in the sales_mgr
has no privileges on any table in the hr_mgr
Selecting from sales_mgr
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
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
table since the emp_admin
package has definer’s rights and hr_mgr