ORA-01749:您可能无法授予/撤销自己的特权 [英] ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

问题描述

我正在从RATOR_MONITORING架构运行以下查询，该查询正在将RATOR_MONITORING_CONFIGURATION.SMSC_GATEWAY表的引用权限授予RATOR_MONITORING架构.

I am running the below query from RATOR_MONITORING schema which is granting the reference permission from RATOR_MONITORING_CONFIGURATION.SMSC_GATEWAY table to RATOR_MONITORING schema.

GRANT REFERENCES ON "RATOR_MONITORING_CONFIGURATION"."SMSC_GATEWAY" TO "RATOR_MONITORING";  
ALTER TABLE "RATOR_MONITORING"."SMSC_GATEWAY_STATUS" ADD CONSTRAINT "SMSC_GATEWAY_STATUS_FK1" FOREIGN KEY ("SMSC_GATEWAY_ID")  
   REFERENCES "RATOR_MONITORING_CONFIGURATION"."SMSC_GATEWAY" ("ID") ON DELETE CASCADE ENABLE; 

当我运行以下查询时，我收到以下错误消息:

When i run the below query i am getting error as :

SQL Error: ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

我还有其他SQL语句，也只想从RATOR_MONITOR模式运行.该sql语句存储在sql文件中.而且我正在sql plus中运行此sql文件.因此，有什么方法可以使用匿名块或任何其他方法来连接到架构RATOR_MONITORING_CONFIGURATION架构并授予引用权限，然后再次连接到rator_monitor架构并运行alter table语句以及其他sql语句.

I have other sql statements as well which i want to run from RATOR_MONITOR schema only. This sql statement are stored in a sql file. And i am running this sql file in sql plus. So is there any way using anonymous block or any other approch where i can connect to schema RATOR_MONITORING_CONFIGURATION schema and grant the reference permission and again connect to rator_monitor schema and run the alter table statement and also other sql statements.

推荐答案

如果您有SQL脚本来执行此操作，则只需添加connect语句即可切换用户，如下所示.

If you have SQL script to do this then you can simply add connect statement to switch the user as shown below.

我有test.sql文件，其中包含grant和alter语句.我有两个用户sh和hr.

I have test.sql file which contains grant and alter statements. I have two user sh and hr.

conn sh/sh@orcl
grant REFERENCES on sh.customers to hr;
conn hr/hr@orcl
ALTER TABLE cust ADD CONSTRAINT fk1 FOREIGN KEY (ID) REFERENCES sh.customers(CUST_ID) ON DELETE CASCADE ENABLE; 

我只是执行了脚本.

[oracle@ora12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 17 15:19:40 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> @test.sql
Connected.

Grant succeeded.

Connected.

Table altered.

SQL> 

这篇关于ORA-01749:您可能无法授予/撤销自己的特权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！