with grant option 重建table丢失 with admin option

Symptoms

ORA-01720 occurs when re-creating a VIEW that grants the VIEW select privilege to other users.

SQL> grant select any table to test_user2;

SQL> create table test_tab (col1 number, col2 varchar2(200));

Table created.

SQL> insert into test_tab values(1,'tes');

1 row created.

SQL> commit;

Commit complete.

SQL> grant select on test_tab to test_user2 with grant option;

Grant succeeded.

SQL> conn test_user2
Enter password:
Connected.

SQL> CREATE OR REPLACE VIEW test_v (
2 col1,
col2
) as
select
col1,
col2
from
test_tab
; 3 4 5 6 7 8 9 10

View created.

SQL> select count(*) from test_v;

COUNT(*)
----------
1

SSQL> grant select on test_v to test_user3;　　　　　　　<<<< grant select on test_v to other user.

Grant succeeded.


SQL> conn test_user1
Enter password:

SQL> drop table test_tab;

Table dropped.

SQL> create table test_tab (col1 number, col2 varchar2(200));

Table created.

SQL> insert into test_tab values (2,'test');

1 row created.

SQL> conn test_user2
Enter password:
Connected.

SQL> CREATE OR REPLACE VIEW test_v (
2 col1,
col2
) as
select
col1,
col2
from
test_tab
; 3 4 5 6 7 8 9 10
test_tab
*
ERROR at line 9:
ORA-01720: grant option does not exist for 'TEST_USER1.TEST_TAB'              <<<<<<<<

Changes

Cause

No select with grant option privilege was granted by re-creating the table.

SQL> select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE,GRANTABLE from USER_TAB_PRIVS;

GRANTEE OWNER TABLE_NAME PRIVILEGE GRANTABLE

-------------- ------------ ------------------------------ ------------------------ ---------

TEST_USER3 TEST_USER2 TEST_V SELECT NO

TEST_USER2 TEST_USER1 BIN$HWkn4/lxcdzgYwEAAH/YVg==$0 SELECT YES                <<<<<<<<<<<<<

Solution

Grant the VIEW owner the select with grant option privilege on the table and re-create the VIEW.

SQL> grant select on test_tab to test_user2 with grant option;

SQL> CREATE OR REPLACE VIEW test_v (
2 col1,
col2
) as
select
col1,
col2
from
test_tab
;
3 4 5 6 7 8 9 10

View created.

模拟如下
create user a identified by a;
create user b identified by b;
create user c identified by c;

grant connect,resource to a;
grant connect,resource to b;
grant connect,resource to c;
grant create view to b;

conn a/a
create table t1 as select rownum rn from dual connect by level<10;
grant select on t1 to b;

conn b/b
create table t2 as select rownum rn from dual connect by level<10;
create view v1 as select * from a.t1 union all select * from t2;
grant select on v1 to c;

conn c/c
select * from b.v1;

用户a有一个基表,
用户b有用户a基表的查询权限,并创建了一个视图,
现在需要把用户b创建的视图,授权给用户c.

实际上grant select on v1 to c;命令执行会报错(ORA-01720).
那是因为a.t1的查询授权不能传递.

解决这个问题也很容易,在给b用户a.t1的授权中增加with grant option
create user a identified by a;
create user b identified by b;
create user c identified by c;

grant connect,resource to a;
grant connect,resource to b;
grant connect,resource to c;
grant create view to b;

conn a/a
create table t1 as select rownum rn from dual connect by level<10;
grant select on t1 to b with grant option;

conn b/b
create table t2 as select rownum rn from dual connect by level<10;
create view v1 as select * from a.t1 union all select * from t2;
grant select on v1 to c;

conn c/c

select * from b.v1;

-------------------------------------------------------------------------------------------

On R11.5.10.2, when attempting to apply patch 6241631, the following error occurs:

ERROR
-----------------------
Grant failed while running patch 6241631.
Sql being run at the time is ademusr.sql.


AD Worker error:
The following ORACLE error:
ORA-01720: grant option does not exist for 'SYS.GV_$SESSION'
occurred while executing the SQL statement:
GRANT select on FND_FORM_SESSIONS_V to em_oam_monitor_role
Error occurred in file
h:\ORACLE\xxxxx\ad\11.5.0\patch\115\sql\ademusr.sql
with arguments '&systempwd &un_fnd &pw_fnd &un_apps &pw_apps'.

Running the grant from a SQLPLUS prompt login in as APPS, fails with the following error:

grant select on FND_FORM_SESSIONS_V to em_oam_monitor_role;
ORA-01720: grant option does not exist for 'SYS.GV_$SESSION'

Cause

Missing select with grant option privilege on sys.GV_$SESSION.

Solution

1. As SYSTEM user, run the following from SQL Plus:

grant select on sys.GV_$SESSION to apps with grant option

all_tab_cols 这些默认是给public的，所以谁都可以用，放在view里面再授权也可以（如果没有with  grant option 可能这个view其他用户不能访问）

2. Then, see if you are now able to run the following:

grant select on fnd_form_sessions_v to em_oam_monitor_role

-------------------------------------

with admin option

使用with admin option，被授权用户可将所获得的权限再次授予其它用户或角色，而且取消授权时不级联。例如:

grant create session to user_a with admin option;

则用户user_a用户拥有了“create session”权限，然后用户user_a操作：

grant create session to user_b;

则user_b也拥有了“create session”权限。 
如果系统管理员要回收user_a的权限，则user_b的权限仍然保留，但管理员可以显式回收user_b的权限：

revoke create session from user_b;

with grant option

使用with grant option。被授权用户可将所获得的权限再次授予其它用户或角色，并且权限的取消是级联的。级联的意思是，如果user_a使用“with grant option”语句将权限又授予了user_b，当管理员回收user_a的权限时，则user_b的权限也会被回收。但管理员不可以显式回收用户user_b的权限。