db2db2-luwtoad-data-point

Why I get this error? SQL0551N The statement failed because the authorization ID

I want to create a view from another view that I have select statement privilege. However, I can't and I am getting this error. Do you know why? Do I need other type of Select privilege?

SET CURRENT SCHEMA = SCHEMA1;

CREATE VIEW NEWSCHEMA.MYVIEW AS SELECT  * FROM DB1.SCHEMA1.VIEW1

WITH NO ROW MOVEMENT;

SET CURRENT SCHEMA = NEWSCHEMA;

COMMIT;

full error msg:

Category Line Position Timestamp Duration Message Error 3 0 01/27/2023 11:24:05 AM 0:00:00.007 - DB2 Database Error: ERROR [42501] [IBM][DB2/AIX64] SQL0551N The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: "NEWSCHEMA". Operation: "SELECT". Object: "SCHEMA1.VIEW1".

SELECT GRANTEE, GRANTEETYPE, CONTROLAUTH, SELECTAUTH FROM SYSCAT.TABAUTH WHERE (TABSCHEMA, TABNAME) = ('SCHEMA1', 'VIEW1') AND GRANTEETYPE IN ('U', 'R')

Result: enter image description here

Solution

  • The reason is highly like because of the following CREATE VIEW authorization requirement:

    Authorization
    The privileges held by the authorization ID of the statement must include at least one of the following authorities:

    IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the view does not exist
    CREATEIN privilege on the schema, if the schema name of the view refers to an existing schema
    SCHEMAADM authority on the schema, if the schema name of the view refers to an existing schema DBADM authority

    and at least one of the following authorities for each table, view, or nickname identified in any fullselect:

    CONTROL privilege on that table, view, or nickname
    SELECT privilege on that table, view, or nickname
    SELECTIN privilege on the schema containing the table, view, or nickname DATAACCESS authority on the schema containing the table, view, or nickname
    DATAACCESS authority
    ...
    Group privileges are not considered for any table or view specified in the CREATE VIEW statement.

    So, you may really have an ability to SELECT from this view, but you probably have it via some group membership, but not personally or via some role.
    This is the reason you get this error.
    You may ask your SECADM or view owner grant your authorization id a SELECT privilege to resolve the problem.