假设用户scott授予ken对emp表的all权限,同时有WITH GRANT OPTION权限,并且用户ken又将对emp表的all权限授予了用户tom。现只想收回用户ken的WITH GRANT OPTION权限不收回其他权限应该怎么做??等待解决。。SQL> conn scott/tiger;
SQL> grant all on emp to ken with grant option;
SQL> conn ken/ken;
SQL> grant all on scott.emp to tom with grant option;如上 scott怎么只收回tom和ken的grant option。谢谢~~~
SQL> grant all on emp to ken with grant option;
SQL> conn ken/ken;
SQL> grant all on scott.emp to tom with grant option;如上 scott怎么只收回tom和ken的grant option。谢谢~~~
上面的命令只需要grant改为revoke,to改为from。
SQL> conn scott/tiger;
SQL> revoke all on emp from ken
这样ken连访问emp的权限都没了。
但是问题是我只想收回ken的grant option 权限不让ken再向其他人授权访问emp表但并不收回ken 对emp表的增删改查。
SQL> revoke all on emp from ken;
SQL> grant all on emp to ken;
这样全部收回再重新授权,如果授予权限很多岂不是要一个个重新来过?没有直接只收回grant option的方法吗?
对于使用WITH ADMIN OPTION 子句分配的系统权限,在回收时不会回收已级联分配的权限。
SQL> show user
USER is "SYS"
SQL> create user ken identified by ken;User created.SQL> grant create session to ken;Grant succeeded.SQL> conn scott/tiger;
Connected.
SQL> grant all on emp to ken with grant option;Grant succeeded.SQL> conn sys as sysdba;
Enter password:
Connected.
SQL> create user tom identified by tom;User created.SQL> grant create session to tom;Grant succeeded.SQL> conn ken/ken;
Connected.
SQL> grant all on scott.emp to tom with grant option;Grant succeeded.SQL> conn scott/tiger;
Connected.SQL> revoke all on emp from ken;Revoke succeeded.SQL> conn tom/tom;
Connected.
SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
更多参考;
Oracle 用户、对象权限、系统权限
Oracle 角色、配置文件