ORA-01720 and ORA-01031 errors when GRANT/SELECT View, ora-01720ora-01031

ORA-01720 and ORA-01031 errors when GRANT/SELECT View, ora-01720ora-01031

For information about ORA-01031 errors when creating a view, refer to an article I 've previously compiled: Create view failed with ORA-01031: insufficient privileges, which I thought had covered a lot of cases, however, I encountered a special case today and it took me a long time. The following uses a few examples to demonstrate the ins and outs of ORA-01031.

In the test environment, prepare two users (dm and ods) and grant certain permissions. Prepare the demo cases.

SQL> show user;

USER is "SYS"

SQL> create user dm identified by dm;

User created.

SQL> create user ods identified by ods;

User created.

SQL> grant connect , resource to dm;

Grant succeeded.

SQL> grant create view to dm;

Grant succeeded.

SQL> grant connect , resource to ods;

Grant succeeded.

Case 1: A ORA-01720 error occurred while granting view select permissions to other users

SQL> conn ods/ods

Connected.

SQL> create table department

  2  (

  3      dept_id        number(10)  ,

  4      dept_name      varchar2(12) 

  5  );

Table created.

SQL> grant select on department to dm;

Grant succeeded.

SQL> conn dm/dm

Connected.

SQL> create table employee

  2  (

  3      employee_id    number(10)  ,

  4      employee_name  varchar2(32),

  5      dept_id        number(10)

  6  );

Table created.

SQL> show user

USER is "DM"

SQL> create table employee

  2  (

  3      employee_id      number(10)  ,

  4      employee_name    varchar2(32),

  5      dept_id          number(10)

  6  );

Table created.

SQL> select * from v_test;

no rows selected

SQL> grant select on dm.v_test to ods;

grant select on dm.v_test to ods

                   *

ERROR at line 1:

ORA-01720: grant option does not exist for 'ODS.DEPARTMENT'

This error occurs because ods grants the SELECT permission of table DEPARTMENT to user dm, while dm also tries to grant the query permission of table DEPARTMENT to user ods when trying to grant the query permission of v_test, however, dm does not have this permission (is it difficult to bypass). In fact, it is very easy to solve this problem, that is, to use with grant option during authorization, as shown below:

SQL> conn ods/ods 

Connected.

SQL> grant select on department to dm with grant option;

Grant succeeded.

SQL> conn dm/dm;

Connected.

SQL> grant select on dm.v_test to ods;

Grant succeeded.

SQL> conn ods/ods

Connected.

SQL> select * from dm.v_test;

no rows selected

Case 2: authorizing a VIEW to another user (under the sys account) with a ORA-01031: insufficient privileges

SQL> show user;

USER is "ODS"

SQL> create or replace function get_deptcode( departname varchar2) return varchar2

  2  as

  3  dept_code varchar2(2);

  4  begin

  5     select substr(departname,1,1) into dept_code from dual;

  6     return dept_code;

  7  end;

  8  /

Function created.

SQL> grant execute on get_deptcode to dm;

Grant succeeded.

SQL> conn dm/dm

Connected.

SQL> create or replace view v_test

  2  as

  3     select e.employee_id

  4           ,e.employee_name

  5           ,(select ods.get_deptcode(d.dept_name) from dual) dept_code

  6     from employee e

  7     inner join ods.department d on e.dept_id =d.dept_id;

View created.

SQL> select * from v_test;

no rows selected

Log On with sys to authorize ods to have the query view permission. The reason why sys is used for authorization instead of the corresponding Account dm is that when I publish a script, the sys account is generally used for publishing, the result was a relatively hidden ORA-01031 error.

SQL> conn sys as sysdba

Enter password: 

Connected.

SQL> grant select on dm.v_test to ods;

Grant succeeded.

SQL> conn ods/ods

Connected.

SQL> select * from dm.v_test;

select * from dm.v_test

                 *

ERROR at line 1:

ORA-01031: insufficient privileges

If you log on with the dm account and grant the view v_test permission to ods, you can find this error. If you operate with the sys account, this error is hidden. As shown below

SQL> conn dm/dm

Connected.

SQL> grant select on v_test to ods;

grant select on v_test to ods

                *

ERROR at line 1:

ORA-01720: grant option does not exist for 'ODS.GET_DEPTCODE'

SQL> conn ods/ods

Connected.

SQL> grant execute on ODS.GET_DEPTCODE to dm with grant option;

Grant succeeded.

SQL> conn dm/dm

Connected.

SQL> grant select on v_test to ods;

Grant succeeded.