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

oracleoracle-11g-r2permissions

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?

Best 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.

Related Solutions

Sql-server – Select permission inside stored procedure

Ok, on the basis of the above comment and as per my suspicion - it seems as though you are trying to execute dynamic SQL within your stored procedure.

What you need to remember is that when you do this it does not get executed within the context of the stored procedure - it gets executed within a new session. Because of this, the fact that the statement is being called within a stored procedure is a moot point, and you will need to grant explicit permission on the objects that your dynamic SQL is using.

If you don't want to do this I would refactor your stored procedure to not use dynamic SQL.

The below link from Microsoft should help you with your problem:

PRB: Security Context of Dynamic SQL Statements Inside a Stored Procedure (Wayback Machine archive)

This behavior occurs because a dynamic execution query (sp_executesql or EXECUTE) executes in a separate context from the main stored procedure; it executes in the security context of the user that executes the stored procedure and not in the security context of the owner of the stored procedure.

This is also discussed in the (more current) Microsoft Docs article:

Writing Secure Dynamic SQL in SQL Server

Executing dynamically created SQL statements in your procedural code breaks the ownership chain, causing SQL Server to check the permissions of the caller against the objects being accessed by the dynamic SQL.

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

I'm not sure if I'm understanding your question properly. I created a user with the ability to create views for his own schema.

SQL> create user view_user identified by password default tablespace users quota unlimited on users;

User created.

SQL> grant create session, create table, create view to view_user;

Grant succeeded.

SQL> connect view_user
Enter password: 
Connected.
SQL> CREATE TABLE Members (ID number(10), Name varchar2(100), Street_No varchar2(100), ZIP number(5), City varchar2(100));

Table created.

SQL> CREATE VIEW MembersNamesOnly AS SELECT ID, Name FROM Members;

View created.

SQL>

Related Question