How to view the original script that built a materialized view and that was created by another user

materialized-vieworaclepermissions

In the Oracle database server use, another user (called MIMIC2DEVEL) created a materialized view and made it public. How to view the original script that was used to built it?

Here are the grants on the materialized view:

enter image description here

I am aware of How to view the original script of a materialized view? but the two solutions proposed there do not seem to work for materialized views that was created by another user.

I use Oracle SQL Developer and the database server runs:

enter image description here

Best Answer

Specify the owner in GET_DDL, or use ALL_MVIEWS instead of USER_MVIEWS:

select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MVIEW_NAME', 'OWNER') from dual;

select query from all_mviews
  where mview_name = 'your materialized view';

Related Solutions

Why does dropping a MVIEW trigger a full refresh

After running some tests in an isolated environment it seems that Raj is right: the refresh background job that I saw while waiting for the DROP wasn't triggered by the DROP. As far as I can tell this was caused by the refresh settings provided during the creation of the mview.

It seems that

refresh start with trunc(sysdate) + 1/8  next sysdate + interval '8' hour

together with the implicit build immediate causes the mview to be refreshed during the creation and then again immediately after the creation has finished.

Apparently start with trunc(sysdate) + 1/8 causes this. When running

create materialized view foobar 
   BUILD DEFERRED
   refresh start with trunc(sysdate) + 1/8  next sysdate + interval '8' hour
as
select ...
from ...;

The create statement finishes quickly and when I then look into the active sessions I do see one background session that does the refresh.

Sql-server – If view A is built atop other views and tables, does a user need permission to only view A, or everything down

Do you need to grant permissions to every object built underneath the views? Or just the view itself? Or either one?

Permissions need to be granted to the person executing the query for every object referenced by the view. Except if they are owned by the view owner. In that eventuality Ownership Chains come into play.

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

The below creates two tables. T1 (owned by TestUser1) and T2 (owned by TestUser2) and a view that references both tables and is owned by TestUser1.

A third user is granted SELECT permissions on the view but none of the underlying objects. Selecting from the view initially fails for them. However it succeeds after they are granted select permissions on T2. There is no need to grant them permissions on T1 as this is owned by the same user as the view.

Setup

CREATE USER TestUser1 WITHOUT LOGIN;
CREATE USER TestUser2 WITHOUT LOGIN;
CREATE USER TestUser3 WITHOUT LOGIN;

CREATE TABLE dbo.T1(C INT);

ALTER AUTHORIZATION ON dbo.T1 TO TestUser1; 


CREATE TABLE dbo.T2(C INT);

ALTER AUTHORIZATION ON dbo.T2 TO TestUser2; 

GO

CREATE VIEW dbo.V
AS
SELECT T1.C
FROM dbo.T1 CROSS JOIN dbo.T2

GO

ALTER AUTHORIZATION ON dbo.V TO TestUser1;

GRANT SELECT ON dbo.V TO TestUser3;

Test 1 (Fails)

EXECUTE AS USER='TestUser3';

SELECT *
FROM dbo.V;

REVERT;

The SELECT permission was denied on the object 'T2', database 'Foo', schema 'dbo'.

Test 2 (Succeeds)

GRANT SELECT ON dbo.T2 TO TestUser3    

EXECUTE AS USER='TestUser3';

SELECT *
FROM dbo.V;

REVERT;

Cleanup

DROP TABLE dbo.T1, dbo.T2
DROP VIEW dbo.V
DROP USER TestUser1
DROP USER TestUser2
DROP USER TestUser3