oracle回收public默认权限,Oracle回收权限报错ORA-01952

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

来源：51CTO

作者：love8度幸福

链接：https://blog.51cto.com/8355320/2530178