Grant privilege to see column names in null constraint violations when inserting in views

Posted on

Question :

My database has two schemas, one with the actual tables and one that is used by an application. The application schema doesn’t have any privileges on the actual tables. Instead there is a view for each table that limits the rows the application schema sees (but not the columns, the view is created as SELECT * FROM ...). For all those views SELECT, INSERT, UPDATE and DELETE was granted to the application schema.

If the application user does an insert into the view and violates a not null constraint, no column name is given. The actual error message is

ORA-01400: cannot insert NULL into (???).

I see that this makes sense from a security standpoint, but I don’t care if the application user can see the name of the column of the actual table since it’s always the same as the column name in the view. If it isn’t possible otherwise, it would even be OK for me if the application schema sees the table name. Is there some privilege that can be granted that allows seeing the column name without allowing any actual queries on the table?

Answer :

It seems that granting the privilege references allows the user to see the column names:

grant references on the_table to app_user;

The grant needs to be done for the base table not for the view.

Leave a Reply

Your email address will not be published. Required fields are marked *