授权with admin option和with grant option:

本文探讨了Oracle和MySQL中不同权限(系统权限与对象权限)的授予与回收机制。Oracle中系统权限的WITH ADMIN OPTION不会级联回收,而WITH GRANT OPTION则会;MySQL中的WITH GRANT OPTION不会级联回收。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

oracle:

当给一个用户授予系统权限时带上with admin option,他可以把此权限授予给其他用户、角色,而在revoke授予给这个用户的系统权限时不会级联回收他授予给其他用户的权限。如授予给A用户p系统权限时带有with admin option参数,此时A把系统权限p授予给用户B,然后revoke收回了授予A用户的p权限,这时A授予给B的p权限不会被级联收回,就是说B还拥有p系统权限。

而对象权限的参数with grant option却会级联收回对象权限。如授予给A用户o对象权限时带有withgrant option参数,A把o对象权限grant给用户B,此时如果revoke授予给A的o对象权限,会把B被授予的o对象权限也revoke。

 mysql

mysql中只有with grant option,对A用户进行的授权,A可以授予给其他用户,当收回对A的授权时,A授予给其他用户的权限不会被级联收回。注意with grant option也可以被授予给其他用户。

CREATE USER cedar IDENTIFIED BY cedar DEFAULT TABLESPACE CEDAR_DATA TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; -- 5 Roles for cedar GRANT DBA TO cedar; GRANT RESOURCE TO cedar WITH ADMIN OPTION; GRANT AQ_ADMINISTRATOR_ROLE TO cedar; GRANT CONNECT TO cedar WITH ADMIN OPTION; GRANT AQ_USER_ROLE TO cedar; ALTER USER cedar DEFAULT ROLE ALL; -- 44 System Privileges for cedar GRANT CREATE ANY DIRECTORY TO cedar WITH ADMIN OPTION; GRANT CREATE PUBLIC SYNONYM TO cedar WITH ADMIN OPTION; GRANT EXECUTE ANY CLASS TO cedar WITH ADMIN OPTION; GRANT DROP ANY VIEW TO cedar WITH ADMIN OPTION; GRANT CREATE CLUSTER TO cedar; GRANT ALTER SYSTEM TO cedar; GRANT UPDATE ANY TABLE TO cedar; GRANT INSERT ANY TABLE TO cedar; GRANT LOCK ANY TABLE TO cedar; GRANT CREATE EXTERNAL JOB TO cedar WITH ADMIN OPTION; GRANT EXECUTE ANY PROGRAM TO cedar WITH ADMIN OPTION; GRANT CREATE JOB TO cedar WITH ADMIN OPTION; GRANT DROP ANY DIRECTORY TO cedar WITH ADMIN OPTION; GRANT ALTER ANY TRIGGER TO cedar; GRANT CREATE DATABASE LINK TO cedar; GRANT DROP ANY TABLE TO cedar WITH ADMIN OPTION; GRANT CREATE TABLE TO cedar WITH ADMIN OPTION; GRANT QUERY REWRITE TO cedar; GRANT ANALYZE ANY TO cedar; GRANT DROP ANY TRIGGER TO cedar; GRANT EXECUTE ANY PROCEDURE TO cedar; GRANT SELECT ANY TABLE TO cedar WITH ADMIN OPTION; GRANT ALTER ANY TABLE TO cedar; GRANT UNLIMITED TABLESPACE TO cedar WITH ADMIN OPTION; GRANT CREATE SESSION TO cedar; GRANT CREATE ANY TRIGGER TO cedar; GRANT DROP ANY PROCEDURE TO cedar WITH ADMIN OPTION; GRANT ALTER ANY PROCEDURE TO cedar; GRANT DROP PUBLIC SYNONYM TO cedar WITH ADMIN OPTION; GRANT DROP ANY SYNONYM TO cedar WITH ADMIN OPTION; GRANT MANAGE SCHEDULER TO cedar WITH ADMIN OPTION; GRANT CREATE ANY PROCEDURE TO cedar; GRANT CREATE PROCEDURE TO cedar WITH ADMIN OPTION; GRANT CREATE SEQUENCE TO cedar; GRANT CREATE VIEW TO cedar WITH ADMIN OPTION; GRANT CREATE SYNONYM TO cedar; GRANT DROP ANY INDEX TO cedar; GRANT DELETE ANY TABLE TO cedar; GRANT CREATE ANY TABLE TO cedar; GRANT CREATE ANY JOB TO cedar WITH ADMIN OPTION; GRANT SELECT ANY DICTIONARY TO cedar WITH ADMIN OPTION; GRANT CREATE ROLE TO cedar; GRANT CREATE ANY SYNONYM TO cedar WITH ADMIN OPTION; GRANT ALTER SESSION TO cedar WITH ADMIN OPTION; GRANT EXECUTE ON sys.dbms_aq TO cedar; GRANT EXECUTE ON sys.dbms_aqadm TO cedar; GRANT EXECUTE ON sys.dbms_network_acl_admin TO cedar; GRANT ALL ON sys.DBMS_LOCK TO cedar; 这个SQL在oracle 能用吗
06-08
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值