3

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

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.

Andrew
  • 3,632
  • 24
  • 64
  • 113

1 Answers1

2

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

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;

And I have simply executed the script.

[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>
atokpas
  • 3,231
  • 1
  • 11
  • 22