CODEWITHSUNDEEP
viewdb2ddl
jason satya
jason satya

Reputation: 1

DB2- how to prevent user viewing view DDL

i'm having this situation whereby user are using Dbeaver to access to DB2. There is some views created. At the moment user have the ability to use the Dbeaver to see the view DDL (back end code).

Question : how/is there any way to prevent the user see the view DDL?

much appreciate you advice

Upvotes: 0

Views: 394

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12314

Look at the Db2 Obfuscation facility.

CALL DBMS_DDL.CREATE_WRAPPED ('CREATE OR REPLACE VIEW TEST_OBFUSCATED AS SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA LIKE ''SYS%''');

SELECT TEXT
FROM SYSCAT.VIEWS
WHERE VIEWSCHEMA = CURRENT SCHEMA AND VIEWNAME = 'TEST_OBFUSCATED';
TEXT
CREATE OR REPLACE VIEW TEST_OBFUSCATED WRAPPED SQL11014 long_meaningless_string

You may use this view as any other one in the same way, but its text is not visible for everyone.
Moreover, you can use this "strange" obfuscated statement to create the view from scratch. There is a scalar function which helps you to get this obfuscated statement without creation it first.

VALUES DBMS_DDL.WRAP ('CREATE OR REPLACE VIEW TEST_OBFUSCATED AS SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA LIKE ''SYS%''')

If someone still needs to view the real view text, you may use Row and column access control (RCAC) on the SYSIBM.SYSVIEWS table.

Upvotes: 1

mustaccio
mustaccio

Reputation: 18945

If you want your users to be able to select from a view, they must be able to obtain the definition of that view.

You can wrap the query against the view in a set-returning user-defined function, which has all privileges of its creator, presumably a DBA, and grant other users only the EXECUTE privilege on that function. You will then be able to revoke from your users the privileges to read system catalog tables that you don't want them to read.

Details in the manual.

Upvotes: 0

Related Questions