4.批量收回用户对象权限,收回用户chenmh在架构zhang下的所有权限 SELECT 'REVOKE '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' FROM CHENMH;' FROM DBA_Tab_Privs WHERE GRANTEE='CHENMH' AND OWNER='ZHANG' ORDER BY TABLE_NAME,PRIVILEGE; 备注:oracle用户的视图权限比较特殊,需要单独授予resource角...
4.批量收回用户对象权限,收回用户chenmh在架构zhang下的所有权限 SELECT 'REVOKE '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' FROM CHENMH;' FROM DBA_Tab_Privs WHERE GRANTEE='CHENMH' AND OWNER='ZHANG' ORDER BY TABLE_NAME,PRIVILEGE; 备注:oracle用户的视图权限比较特殊,需要单独授予resource角...
授权语句还可以增加WITH ADMIN OPTION选项,表示被授权的用户可以将它所得权限赋给其它用户,如: SQL>GRANT CREATE TABLE,CREATE VIEW TO USER1,USER2 WITH ADMIN OPTION; 若要了解各用户所拥有的系统权限,可以查询数据字典USER_SYS_PRIVS、ROLE_SYS_PRIVS。 若要回收权限,则使用REVOKE命令,如: SQL>REVOKE CREATE T...
Oracle常见授权与回收权限——grant和revoke 1.GRANT 赋于权限 常⽤的系统权限集合有以下三个:CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)常⽤的数据对象权限有以下五个:ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名,DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER...
REVOKE SELECT ON 表名 FROM 用户名; REVOKE SELECT, INSERT, DELETE ON 表名 FROM 用户名1, 用户名2; 3.查看用户权限 可参照另一篇文章:oracle用户具有的权限和角色 select * from dba_sys_privs; select * from dba_tab_privs; SELECT DISTINCT(grantee) FROM dba_tab_privs; ...
You can GRANT and REVOKE privileges on various database objects in Oracle. We'll first look at how to grant and revoke privileges on tables and then how to grant and revoke privileges on functions and procedures in Oracle.Grant Privileges on Table You can grant users various privileges to tab...
REVOKEALLPRIVILEGESONzhang.personFROMchenmh; revoke图表: 五、批量操作 1.批量授予权限,授予用户chenmh拥有zhang下所有表的增删改查权限。生产批量执行sql 代码语言:javascript 复制 SELECT'GRANT SELECT,DELETE,UPDATE,INSERT ON '||OWNER||'.'||TABLE_NAME||' TO CHENMH;'FROMdba_tablesWHEREOWNER='ZHANG'; ...
--revoke insert on xezf.cfg_alarm from wcz1; 收回wcz1角色的权限 --grant wcz1 to wcz ; 将角色的权限授权给wcz; -- create role wcz2; --grant wcz1 to wcz2; 将角色wcz1授权给wcz2; --alter user wcz default wcz1,wcz2; 修改用户默认角色 ...
GRANT :Use to grant privileges to other users or roles.REVOKE :Use to take back privileges granted to other users and roles.Privileges are of two types :System Privileges Object privileges System Privileges are normally granted by a DBA to users. Examples of system privileges are CREATE ...
REVOKEALLONmy_tableFROMadmin; Conclusion In this article, we learned how to use the Revoke command in Oracle SQL to revoke privileges from users or roles. It is important to properly manage user privileges to ensure the security and integrity of the database....