I'm trying to REVOKE a GRANT.
Why must I drop constraints?
See below.
SQL> revoke all on agcy_cls from public;
> revoke all on agcy_cls from public
> *
> ERROR at line 1:
> ORA-01981: CASCADE CONSTRAINTS must be specified to perform this revoke
>
SQL> !oerr ora 1981
> 01981, 00000, "CASCADE CONSTRAINTS must be specified to perform this
> revoke"
> // *Cause: During this revoke some foreign key contraints will be removed.
> // In order to perform this automatically, CASCADE CONSTRAINTS
> must
> // be specified.
> // *Action: Remove the constraints or specify CASCADE CONSTRAINTS.
>
SQL> @$SCRIPTS/user_cons_and_cols
> Enter value for table_name: AGCY
>
CONSTRAINT_NM TNAME R_CONSTRANT_NM COLUMN_NAME
-------------- ------ -------------- --------------
FK_AGCY_AYCS AGCY PK_AGCY_CLS AGCY_CLS_CD
FK_AGCY_AYGP AGCY PK_AGCY_GRP AGCY_GRP_CD
FK_AGCY_AYRP AGCY PK_AGCY_REP REP_NBR
FK_AGCY_AYRN AGCY PK_AGCY_RSTN AGCY_RSTN_CD
FK_AGCY_AYST AGCY PK_AGCY_STAT AGCY_STAT_CD
FK_AGCY_ST AGCY PK_ST AGCY_LIC_ST_CD
FK_AGCY_ST2 AGCY PK_ST AMS_MAIL_ST_CD
FK_AGCY_ZPCDAA AGCY PK_ZIP_CD_AREA ZIP_CD
FK_T_AGCY_AY T_AGCY PK_AGCY AGCY_NBR
> 9 rows selected.
>
> -- THE FOLLOWING ALTERS WERE SUCCESSFUL
> alter table AGCY drop constraint FK_AGCY_AYCS;
> alter table AGCY drop constraint FK_AGCY_AYGP;
> alter table AGCY drop constraint FK_AGCY_AYRP;
> alter table AGCY drop constraint FK_AGCY_AYRN;
> alter table AGCY drop constraint FK_AGCY_AYST;
> alter table AGCY drop constraint FK_AGCY_ST;
> alter table AGCY drop constraint FK_AGCY_ST2;
> alter table AGCY drop constraint FK_AGCY_ZPCDAA;
> alter table T_AGCY drop constraint FK_T_AGCY_AY;
>
SQL> revoke all on agcy_cls from public;
> revoke all on agcy_cls from public
> *
> ERROR at line 1:
> ORA-01981: CASCADE CONSTRAINTS must be specified to perform this revoke
> DIDN'T I JUST GET RID OF THE FKs INVOLVED?
> Sorry I don't get it, but what does a GRANT have to do with a FK
> constraint?
> I could specify CASCADE CONSTRAINTS, but I don't trust what else it might
> drop.
>
> Any ideas?
>
Thanks,
Alan Martin
Database Administrator
GMAC Insurance Personal Lines
Received on Thu Jul 13 2000 - 11:10:52 CDT
