Friday, April 12, 2013

Role is deactive inside PL/SQL procedure/function

Summary:

I was asked if needs to grant direct access to other schema table inside a PL/SQL stored procedure/function. so I test it , which shows that role is deactivated, hence inside PL/SQL explicitly grant is needed.

1. The PL/SQL Function

CREATE OR REPLACE FUNCTION COPY_T1
    (pKongNr IN NUMBER)
    RETURN NUMBER
    AUTHID CURRENT_USER
IS
BEGIN
   INSERT INTO LIQY.T1
      SELECT *
       FROM OWNER1.T1
       WHERE f1 = pKongNr;
    return 0;
END;

2. Create the test table OWNER1.T1

ORACLE1@CUST1> conn OWNER1
Connected.

OWNER1@CUST1> create table t1 (f1 number);

Table created.

OWNER1@CUST1> insert into t1 values(1);

1 row created.

OWNER1@CUST1> commit;

Commit complete.

3. Create application id LIQY , liqy_role to access OWNER1.T1 table

OWNER1@CUST1> conn / as sysdba
Connected.

SYS@CUST1> create role liqy_role;

Role created.

SYS@CUST1> grant select on OWNER1.t1 to liqy_role;

Grant succeeded.

SYS@CUST1> grant liqy_role to liqy;

Grant succeeded.

SYS@CUST1> conn liqy
Connected.

LIQY@CUST1> create table liqy.t1 (f1 number);

Table created.

LIQY@CUST1> select * from liqy.t1;

no rows selected

LIQY@CUST1> select * from OWNER1.t1;

        F1
----------
         1

LIQY@CUST1> set role none;

Role set.

LIQY@CUST1> select * from OWNER1.t1;
select * from OWNER1.t1
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

LIQY@CUST1> conn liqy
Enter password:
Connected.
LIQY@CUST1> select * from OWNER1.t1;

        F1
----------
         1

LIQY@CUST1> conn OWNER1
Enter password:
Connected.
OWNER1@CUST1> insert into t1 values (2);

1 row created.

OWNER1@CUST1> insert into t1 values (3);

1 row created.

OWNER1@CUST1> insert into t1 values (4);

1 row created.

OWNER1@CUST1> insert into t1 values (5);

1 row created.

OWNER1@CUST1> conn liqy
Enter password:
Connected.
LIQY@CUST1> select * from OWNER1.t1;

        F1
----------
         1
         2
         3
         4
         5

5. Compile the function

LIQY@CUST1> CREATE OR REPLACE FUNCTION COPY_T1
2      (pKongNr IN NUMBER)
3      RETURN NUMBER
4      AUTHID CURRENT_USER
5 IS
6 BEGIN
7     INSERT INTO LIQY.T1
8        SELECT *
9         FROM OWNER1.T1
10         WHERE f1 = pKongNr;
11 END;
12 /

Warning: Function created with compilation errors.

LIQY@CUST1> SHOW ERRORS
Errors for FUNCTION COPY_T1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/4      PL/SQL: SQL Statement ignored
9/19     PL/SQL: ORA-00942: table or view does not exist

6. grant explicitly to fix the error

SYS@CUST1> conn OWNER1
Enter password:
Connected.
OWNER1@CUST1> grant select on t1 to liqy;

Grant succeeded.

OWNER1@CUST1> conn liqy
Enter password:
Connected.
LIQY@CUST1> CREATE OR REPLACE FUNCTION COPY_T1
     2    (pKongNr IN NUMBER)
3      RETURN NUMBER
4      AUTHID CURRENT_USER
5 IS
6 BEGIN
7     INSERT INTO LIQY.T1
8        SELECT *
9         FROM OWNER1.T1
10         WHERE f1 = pKongNr;
11 END;
12 /

Function created.

7. But hit new error, so I googled and learned .

LIQY@CUST1> select copy_t1(2) from dual;
select copy_t1(2) from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "LIQY.COPY_T1", line 7

LIQY@CUST1> exec copy_t1(2);
BEGIN copy_t1(2); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'COPY_T1' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

LIQY@CUST1> var r number

LIQY@CUST1> select copy_t1(2) into :r from dual;
select copy_t1(2) into :r from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "LIQY.COPY_T1", line 7

LIQY@CUST1> call copy_t1(2) into :r ;
call copy_t1(2) into :r
     *
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "LIQY.COPY_T1", line 11

LIQY@CUST1> select * from liqy.t1;

no rows selected

LIQY@CUST1> CREATE OR REPLACE FUNCTION COPY_T1
2      (pKongNr IN NUMBER)
3      RETURN NUMBER
4      AUTHID CURRENT_USER
5 IS
6 BEGIN
7     INSERT INTO LIQY.T1
8        SELECT *
9         FROM OWNER1.T1
10         WHERE f1 = pKongNr;
11     return 0;
12 END;
13 /

Function created.

LIQY@CUST1> call copy_t1(2) into :r ;

Call completed.

LIQY@CUST1> select * from liqy.t1;

        F1
----------
         2

LIQY@CUST1> call copy_t1(5) into :r ;

Call completed.

LIQY@CUST1> call copy_t1(6) into :r ;

Call completed.

LIQY@CUST1> select * from liqy.t1;

        F1
----------
         2
         5

References: