Definer rights
A routine stored in the database by default, is executed with the definer rights (owner of the routine), depending on the user who calls it. This is a good way of having the required code perform process logic in one place. It gives better control, preventing direct access to objects that belong to another user, which might result in security issues.
For example, table emp_new belongs to schema scott. User scott creates a procedure raise_sal allowing for updates of emp_new table. User HR is granted execute privileges on the procedure. Now user HR cannot access the table as no privileges have been granted, but can call the procedure to do the required process logic for updating the table.
Invoker Rights
Invoker rights is a new model for resolving references to database elements in a PL/SQL program unit. From Oracle 8i onwards, we can decide if a program unit should run with the authority of the definer or of the invoker. This means that multiple schemas, accessing only those elements belonging to the invoker, can share the same piece of code.
For example, let's take the above case. The table, emp_new , is created in HR schema also. Each of the schema will now own the same set of objects but different data, as they are being used for different purposes. Since the called procedure, raise_sal is owned by SCOTT, the ideal solution in Oracle 8 and earlier releases, was to compile it in user HR also, so that it will use the objects thereof.
With Oracle 8i, there is no need for this duplication of code. A single compiled program unit can be made to use SCOTT schema's objects when invoked by SCOTT and HR schema's objects when invoked by HR. This way, we have the option of creating a code repository in one place and sharing it with various production users. The owner of the routine must grant EXECUTE privilege to other users.
To enable code to run with Invoker rights, an AUTHID clause needs to be used before the IS or AS keyword in the routine header. The AUTHID clause tells Oracle whether the routine is to be run with the invoker rights (CURRENT_USER), or with the Owner rights (DEFINER). If you do not specify this clause, Oracle by default assumes it to be AUTHID DEFINER.
Restriction in using Invoker rights
1. When compiling a new routine, direct privileges are only considered to resolve any external references. Grants through roles are ignored. The same applies when executing a routine created with invoker rights.
2. AUTHID is specified in the header of a program unit. The same cannot be specified for individual programs or methods within a package or object type.
3. Definer rights will always be used to resolve any external references when compiling a new routine.
4. Maintain extra caution on privileges being assigned to a different user. If the wrong privileges are assigned, a routine with invoker rights may have a mind of its own! Such issues would be difficult to debug. So ensure that the grants are perfectly in place.
5. For an invoker rights routine referred in a view or a database trigger, the owner of these objects is always considered as the invoker, and not the user triggering it.
Invoker rights is a powerful option, to be used with caution. To reduce code maintenance, this option should be thought of in the design stage, based on the need to share code across schemas with a similar setup.
Let us consider the below example.
Screnario 1:
SCOTT : table : emp_new
SCOTT : Procedure : Raise_sal
Category : Authid Definer
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table not present
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Screnario 2:
SCOTT : table : emp_new
SCOTT SCHEMA : Procedure : Raise_sal
Category : Authid Definer
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table present but with diffent data set
Result :
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Screnario 3:
SCOTT : table : emp_new
SCOTT SCHEMA : Procedure : Raise_sal
Category : Authid current_user
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table not present
Result:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Screnario 4:
SCOTT : table : emp_new
SCOTT SCHEMA : Procedure : Raise_sal
Category : Authid current_user
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table present but with diffent data sets.
Result:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT : table : emp_new
SCOTT : Procedure : Raise_sal
Category : Authid Definer
Grant Execute to HR;
HR SCHEMA : emp_new table not present
Logged in as Scott user:
CREATE TABLE SCOTT.EMP_NEW
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
TEST_DATE DATE
);
Insert into SCOTT.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(7369, 'SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1870, 20);
Insert into SCOTT.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values
(7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1760, 300, 30);
Insert into SCOTT.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values
(7521, 'WARD', 'SALESMAN', 7698, TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1375, 500, 30);
Insert into SCOTT.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(7566, 'JONESSS', 'MANAGER', 7839, TO_DATE('04/02/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3272.5, 20);
COMMIT;
CREATE OR REPLACE PROCEDURE SCOTT.raise_sal
AUTHID DEFINER
IS
BEGIN
UPDATE emp_new
SET sal = sal * 1.10
WHERE empno IN (SELECT empno FROM emp_new);
END raise_sal;
/
Grant execute on raise_sal to HR;
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 1870 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1760 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1375 500 30
7566 JONESSS MANAGER 7839 02-APR-81 3272.5 20
Then Logged in as HR user
SQL> conn hr
Enter password: **
Connected.
SQL> select * from emp_new;
select * from emp_new
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> execute scott.raise_sal;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> conn scott Swap to scott user to see the changes in scott schema : Here HR user doen't have any access directly to the emp_new table in scott schema
but by calling the procedure it can update the data on emp_new table of scott Schema.
Enter password: *****
Connected.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 2057 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1936 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1512.5 500 30
7566 JONESSS MANAGER 7839 02-APR-81 3599.75 20
SQL>
Result of Scenario1:
Can update the table in grantee schema (i.e SCOTT) irrespective of DML privilege on the grantee table (i.e in emp_new of SCOTT schema) .
Scenario 2:
SCOTT : table : emp_new
SCOTT SCHEMA : Procedure : Raise_sal
Category : Authid Definer
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table present but with diffent data set
Logged in as HR user.
CREATE TABLE HR.EMP_NEW
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
TEST_DATE DATE
);
Insert into HR.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values
(7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1375, 1400, 30);
Insert into HR.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3135, 30);
Insert into HR.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(7782, 'CLARK', 'MANAGER', 7839, TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
2695, 10);
COMMIT;
SQL> show user
USER is "HR"
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7654 MARTIN SALESMAN 7698 28-SEP-81 1375 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 3135 30
7782 CLARK MANAGER 7839 09-JUN-81 2695 10
Connect as SCOTT
SQL> show user
USER is "SCOTT"
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 1870 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1760 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1375 500 30
7566 JONESSS MANAGER 7839 02-APR-81 3272.5 20
SQL> conn hr
Enter password: **
Connected.
SQL> exec scott.raise_sal;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7654 MARTIN SALESMAN 7698 28-SEP-81 1375 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 3135 30
7782 CLARK MANAGER 7839 09-JUN-81 2695 10
SQL> conn scott
Enter password: *****
Connected.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 2057 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1936 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1512.5 500 30
7566 JONESSS MANAGER 7839 02-APR-81 3599.75 20
Result of Scenario2 : It will update the table of scott schema only.
Screnario 3:
SCOTT : table : emp_new
SCOTT SCHEMA : Procedure : Raise_sal
Category : Authid current_user
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table not present
drop table hr.emp_new;
drop procedure scott.raise_sal;
CREATE OR REPLACE PROCEDURE scott.raise_sal authid current_user
IS
BEGIN
UPDATE emp_new
SET sal = sal * 1.10
WHERE empno IN (select empno FROM emp_new );
END raise_sal;
/
Grant execute on raise_sal to HR;
SQL> show user
USER is "HR"
SQL> select * from emp_new;
select * from emp_new
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> execute scott.raise_sal;
BEGIN scott.raise_sal; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCOTT.RAISE_SAL", line 4
ORA-06512: at line 1
Result of Scenario 3 : emp_new table is not present in HR schema now . The procedure can't update the table emp_new in SCOTT shema irrespective of execute priviledge .
Screnario 4: emp_new table present in HR schema but with different data.
SCOTT : table : emp_new
SCOTT SCHEMA : Procedure : Raise_sal
Category : Authid current_user
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table present but with diffent data sets.
SQL> conn hr
Enter password: **
Connected.
SQL> CREATE TABLE HR.EMP_NEW
2 (
3 EMPNO NUMBER(4),
4 ENAME VARCHAR2(10 BYTE),
5 JOB VARCHAR2(9 BYTE),
6 MGR NUMBER(4),
7 HIREDATE DATE,
8 SAL NUMBER(7,2),
9 COMM NUMBER(7,2),
10 DEPTNO NUMBER(2),
11 TEST_DATE DATE
12 );
Table created.
SQL>
SQL>
SQL> Insert into HR.EMP_NEW
2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
3 Values
4 (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
5 1375, 1400, 30);
1 row created.
SQL> Insert into HR.EMP_NEW
2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
3 Values
4 (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
5 3135, 30);
1 row created.
SQL> Insert into HR.EMP_NEW
2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
3 Values
4 (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
5 2695, 10);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7654 MARTIN SALESMAN 7698 28-SEP-81 1375 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 3135 30
7782 CLARK MANAGER 7839 09-JUN-81 2695 10
SQL> conn scott
Enter password: *****
Connected.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 2057 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1936 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1512.5 500 30
7566 JONESSS MANAGER 7839 02-APR-81 3599.75 20
SQL> conn hr
Enter password: **
Connected.
SQL> execute scott.raise_sal;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7654 MARTIN SALESMAN 7698 28-SEP-81 1512.5 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 3448.5 30
7782 CLARK MANAGER 7839 09-JUN-81 2964.5 10
SQL> conn scott
Enter password: *****
Connected.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 2057 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1936 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1512.5 500 30
7566 JONESSS MANAGER 7839 02-APR-81 3599.75 20
Result of Scenario 4: It has updated the emp_new table data of HR schema by referencing the raise_sal procedure of SCOTT schema. The emp_new table data of SCOTT schema remained unaffcted here.
Hope it clears all your doubt.
Cheers
Rajani
A routine stored in the database by default, is executed with the definer rights (owner of the routine), depending on the user who calls it. This is a good way of having the required code perform process logic in one place. It gives better control, preventing direct access to objects that belong to another user, which might result in security issues.
For example, table emp_new belongs to schema scott. User scott creates a procedure raise_sal allowing for updates of emp_new table. User HR is granted execute privileges on the procedure. Now user HR cannot access the table as no privileges have been granted, but can call the procedure to do the required process logic for updating the table.
Invoker Rights
Invoker rights is a new model for resolving references to database elements in a PL/SQL program unit. From Oracle 8i onwards, we can decide if a program unit should run with the authority of the definer or of the invoker. This means that multiple schemas, accessing only those elements belonging to the invoker, can share the same piece of code.
For example, let's take the above case. The table, emp_new , is created in HR schema also. Each of the schema will now own the same set of objects but different data, as they are being used for different purposes. Since the called procedure, raise_sal is owned by SCOTT, the ideal solution in Oracle 8 and earlier releases, was to compile it in user HR also, so that it will use the objects thereof.
With Oracle 8i, there is no need for this duplication of code. A single compiled program unit can be made to use SCOTT schema's objects when invoked by SCOTT and HR schema's objects when invoked by HR. This way, we have the option of creating a code repository in one place and sharing it with various production users. The owner of the routine must grant EXECUTE privilege to other users.
To enable code to run with Invoker rights, an AUTHID clause needs to be used before the IS or AS keyword in the routine header. The AUTHID clause tells Oracle whether the routine is to be run with the invoker rights (CURRENT_USER), or with the Owner rights (DEFINER). If you do not specify this clause, Oracle by default assumes it to be AUTHID DEFINER.
Restriction in using Invoker rights
1. When compiling a new routine, direct privileges are only considered to resolve any external references. Grants through roles are ignored. The same applies when executing a routine created with invoker rights.
2. AUTHID is specified in the header of a program unit. The same cannot be specified for individual programs or methods within a package or object type.
3. Definer rights will always be used to resolve any external references when compiling a new routine.
4. Maintain extra caution on privileges being assigned to a different user. If the wrong privileges are assigned, a routine with invoker rights may have a mind of its own! Such issues would be difficult to debug. So ensure that the grants are perfectly in place.
5. For an invoker rights routine referred in a view or a database trigger, the owner of these objects is always considered as the invoker, and not the user triggering it.
Invoker rights is a powerful option, to be used with caution. To reduce code maintenance, this option should be thought of in the design stage, based on the need to share code across schemas with a similar setup.
Let us consider the below example.
Screnario 1:
SCOTT : table : emp_new
SCOTT : Procedure : Raise_sal
Category : Authid Definer
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table not present
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Screnario 2:
SCOTT : table : emp_new
SCOTT SCHEMA : Procedure : Raise_sal
Category : Authid Definer
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table present but with diffent data set
Result :
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Screnario 3:
SCOTT : table : emp_new
SCOTT SCHEMA : Procedure : Raise_sal
Category : Authid current_user
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table not present
Result:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Screnario 4:
SCOTT : table : emp_new
SCOTT SCHEMA : Procedure : Raise_sal
Category : Authid current_user
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table present but with diffent data sets.
Result:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT : table : emp_new
SCOTT : Procedure : Raise_sal
Category : Authid Definer
Grant Execute to HR;
HR SCHEMA : emp_new table not present
Logged in as Scott user:
CREATE TABLE SCOTT.EMP_NEW
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
TEST_DATE DATE
);
Insert into SCOTT.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(7369, 'SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1870, 20);
Insert into SCOTT.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values
(7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1760, 300, 30);
Insert into SCOTT.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values
(7521, 'WARD', 'SALESMAN', 7698, TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1375, 500, 30);
Insert into SCOTT.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(7566, 'JONESSS', 'MANAGER', 7839, TO_DATE('04/02/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3272.5, 20);
COMMIT;
CREATE OR REPLACE PROCEDURE SCOTT.raise_sal
AUTHID DEFINER
IS
BEGIN
UPDATE emp_new
SET sal = sal * 1.10
WHERE empno IN (SELECT empno FROM emp_new);
END raise_sal;
/
Grant execute on raise_sal to HR;
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 1870 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1760 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1375 500 30
7566 JONESSS MANAGER 7839 02-APR-81 3272.5 20
Then Logged in as HR user
SQL> conn hr
Enter password: **
Connected.
SQL> select * from emp_new;
select * from emp_new
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> execute scott.raise_sal;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> conn scott Swap to scott user to see the changes in scott schema : Here HR user doen't have any access directly to the emp_new table in scott schema
but by calling the procedure it can update the data on emp_new table of scott Schema.
Enter password: *****
Connected.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 2057 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1936 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1512.5 500 30
7566 JONESSS MANAGER 7839 02-APR-81 3599.75 20
SQL>
Result of Scenario1:
Can update the table in grantee schema (i.e SCOTT) irrespective of DML privilege on the grantee table (i.e in emp_new of SCOTT schema) .
Scenario 2:
SCOTT : table : emp_new
SCOTT SCHEMA : Procedure : Raise_sal
Category : Authid Definer
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table present but with diffent data set
Logged in as HR user.
CREATE TABLE HR.EMP_NEW
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
TEST_DATE DATE
);
Insert into HR.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values
(7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1375, 1400, 30);
Insert into HR.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3135, 30);
Insert into HR.EMP_NEW
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(7782, 'CLARK', 'MANAGER', 7839, TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
2695, 10);
COMMIT;
SQL> show user
USER is "HR"
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7654 MARTIN SALESMAN 7698 28-SEP-81 1375 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 3135 30
7782 CLARK MANAGER 7839 09-JUN-81 2695 10
Connect as SCOTT
SQL> show user
USER is "SCOTT"
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 1870 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1760 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1375 500 30
7566 JONESSS MANAGER 7839 02-APR-81 3272.5 20
SQL> conn hr
Enter password: **
Connected.
SQL> exec scott.raise_sal;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7654 MARTIN SALESMAN 7698 28-SEP-81 1375 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 3135 30
7782 CLARK MANAGER 7839 09-JUN-81 2695 10
SQL> conn scott
Enter password: *****
Connected.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 2057 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1936 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1512.5 500 30
7566 JONESSS MANAGER 7839 02-APR-81 3599.75 20
Result of Scenario2 : It will update the table of scott schema only.
Screnario 3:
SCOTT : table : emp_new
SCOTT SCHEMA : Procedure : Raise_sal
Category : Authid current_user
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table not present
drop table hr.emp_new;
drop procedure scott.raise_sal;
CREATE OR REPLACE PROCEDURE scott.raise_sal authid current_user
IS
BEGIN
UPDATE emp_new
SET sal = sal * 1.10
WHERE empno IN (select empno FROM emp_new );
END raise_sal;
/
Grant execute on raise_sal to HR;
SQL> show user
USER is "HR"
SQL> select * from emp_new;
select * from emp_new
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> execute scott.raise_sal;
BEGIN scott.raise_sal; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCOTT.RAISE_SAL", line 4
ORA-06512: at line 1
Result of Scenario 3 : emp_new table is not present in HR schema now . The procedure can't update the table emp_new in SCOTT shema irrespective of execute priviledge .
Screnario 4: emp_new table present in HR schema but with different data.
SCOTT : table : emp_new
SCOTT SCHEMA : Procedure : Raise_sal
Category : Authid current_user
Grant execute on SCOTT.raise_sal to HR;
HR SCHEMA : emp_new table present but with diffent data sets.
SQL> conn hr
Enter password: **
Connected.
SQL> CREATE TABLE HR.EMP_NEW
2 (
3 EMPNO NUMBER(4),
4 ENAME VARCHAR2(10 BYTE),
5 JOB VARCHAR2(9 BYTE),
6 MGR NUMBER(4),
7 HIREDATE DATE,
8 SAL NUMBER(7,2),
9 COMM NUMBER(7,2),
10 DEPTNO NUMBER(2),
11 TEST_DATE DATE
12 );
Table created.
SQL>
SQL>
SQL> Insert into HR.EMP_NEW
2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
3 Values
4 (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
5 1375, 1400, 30);
1 row created.
SQL> Insert into HR.EMP_NEW
2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
3 Values
4 (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
5 3135, 30);
1 row created.
SQL> Insert into HR.EMP_NEW
2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
3 Values
4 (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
5 2695, 10);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7654 MARTIN SALESMAN 7698 28-SEP-81 1375 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 3135 30
7782 CLARK MANAGER 7839 09-JUN-81 2695 10
SQL> conn scott
Enter password: *****
Connected.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 2057 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1936 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1512.5 500 30
7566 JONESSS MANAGER 7839 02-APR-81 3599.75 20
SQL> conn hr
Enter password: **
Connected.
SQL> execute scott.raise_sal;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7654 MARTIN SALESMAN 7698 28-SEP-81 1512.5 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 3448.5 30
7782 CLARK MANAGER 7839 09-JUN-81 2964.5 10
SQL> conn scott
Enter password: *****
Connected.
SQL> select * from emp_new;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TEST_DATE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 2057 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1936 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1512.5 500 30
7566 JONESSS MANAGER 7839 02-APR-81 3599.75 20
Result of Scenario 4: It has updated the emp_new table data of HR schema by referencing the raise_sal procedure of SCOTT schema. The emp_new table data of SCOTT schema remained unaffcted here.
Hope it clears all your doubt.
Cheers
Rajani
No comments:
Post a Comment