An owner referenced by user does not have REFERENCES access

Database
Enthusiast

An owner referenced by user does not have REFERENCES access

Hi,

I'm logged in with dbc user.

I'm ALTERing a table to add Foreign Key Constraint with Following statement

ALTER TABLE testdb.Master2 ADD CONSTRAINT fk_Master1 FOREIGN KEY (Master1ID) REFERENCES testdb.Master1(Master1ID);

its works fine.

But when i write this in a procedure

ALTER TABLE testdb.Master2 ADD CONSTRAINT fk_Master1 FOREIGN KEY (Master1ID) REFERENCES testdb.Master1(Master1ID);

I get following error :

Executed as Single statement.  Failed [5315 : HY000] DG_RECREATE_FK:An owner referenced by user does not have REFERENCES access to testdb.Master1.Master1ID.

Elapsed time = 00:00:00.055

STATEMENT 1: CALL  failed.

Can you tell me the reason and what should i do to solve this problem.

5 REPLIES
Enthusiast

Re: An owner referenced by user does not have REFERENCES access

Did you grant references on testdb.Master1 to the database/user containing the stored Proc?

Enthusiast

Re: An owner referenced by user does not have REFERENCES access

USER DBC has created that Procedure and even that Table.

do i need to explicitly grant reference permission to user dbc for every table.

Junior Contributor

Re: An owner referenced by user does not have REFERENCES access

Please read the topic on SQL SECURITY in the manuals, you might want to switch to CREATOR:

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_10/index.html#page/SQL_Reference/B035_1144_112A/Crea...

Enthusiast

Re: An owner referenced by user does not have REFERENCES access

I am having a difficult time granting references to our other DBAs.  I am the creator of the table and automaticlly have the reference priv.  I want other DBAs to have it as well.  I have tried this with my ID as well as DEB to no avail.  The command executes and completes without error.  However, the grantee does not get the requested PRIV.  Given  the intended grantee is DBUSER1 and the object is MYDB.PRODUCT.  Here is what I run: 

GRANT REFERENCES ON MYDB.PRODUCT TO DBUSER1 WITH GRANT OPTION;  This statement apparently does nothing.  Is it even possible to grant these PRIV to others?

Thanks in advance.

Enthusiast

Re: An owner referenced by user does not have REFERENCES access

With Creator privilege, you should be able to grant Reference privilege to others.

GRANT REFERENCES (reference columns) ON MYDB.PRODUCT TO DBUSER1; /*Only on references columns */

Thanks!!