Oracle操作回收权限时，报错ORA-01952: system privileges not granted to CRM
解决记录：
查询发现系统存在crm与CRM用户，所以当回收crm权限时需要添加双引号，否则系统会报ORA-01952错误。 1、查询crm具有哪些 dba_role 与 dba_sys 权限
SQL> select * from dba_role_privs where GRANTEE='crm';
no rows selected
SQL> select * from dba_sys_privs where GRANTEE='crm';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
crm ALTER ANY SEQUENCE NO
crm CREATE SESSION NO
2、查询CRM具有哪些 dba_role 与 dba_sys 权限
SQL> select * from dba_role_privs where GRANTEE='CRM';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
CRM RESOURCE NO YES
CRM CONNECT NO YES
SQL> select * from dba_sys_privs where GRANTEE='CRM';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CRM UNLIMITED TABLESPACE NO
CRM DROP PUBLIC SYNONYM NO
CRM CREATE VIEW NO
CRM CREATE PUBLIC SYNONYM NO
3、回收crm权限错误操作
SQL> revoke CREATE SESSION from crm;
revoke CREATE SESSION from crm
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'CRM'
SQL> revoke ALTER ANY SEQUENCE from crm;
revoke ALTER ANY SEQUENCE from crm
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'CRM'
4、回收crm权限正确操作如下
SQL> revoke CREATE SESSION from "crm";
Revoke succeeded.
SQL> revoke ALTER ANY SEQUENCE from "crm";
Revoke succeeded.
SQL> grant connect,resource to "crm";
Grant succeeded.
SQL> grant create view to "crm";
Grant succeeded.
SQL> grant create public synonym to "crm";
Grant succeeded.
SQL> grant drop public synonym to "crm";
Grant succeeded.
SQL> grant unlimited tablespace to "crm";
Grant succeeded.
5、再次查询crm具有哪些 dba_role 与 dba_sys 权限
SQL> select * from dba_role_privs where GRANTEE='crm';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
crm RESOURCE NO YES
crm CONNECT NO YES
SQL> select * from dba_sys_privs where GRANTEE='crm';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
crm CREATE PUBLIC SYNONYM NO
crm CREATE VIEW NO
crm DROP PUBLIC SYNONYM NO
crm UNLIMITED TABLESPACE NO
