It seems I am hit by this DATABASE VAULT constraints making my views getting errors
I am confused about this database vault to make it harder its coupled with oracle wallet and ASO-TDO (advance security option - transparent data encryption)
Anyone here knows Vault ?
1. I created a "hr" realm
2. I added "hr" at realm secured objects, as the table owner.
3. I added "hr" at realm authorizations as owner.
4. I added "scott" at realm authorizations as participants.
I granted select on the table HR.EMP to SCOTT.
At user SCOTT> I created synonym SCOTT.EMP for HR.EMP.
When logged in as SCOTT, I can select the synonym EMP.
But when I created a view against this synonym EMP, i got ora-1031 error
I am creating a view but it says:
Warning: View created with compilation errors.
SQL> show error
Are you trying to create Force View ?. then this warning is fine.
Login as scott:
Even if I test : create view xxx as select * from emp; (emp is a synonym from hr.emp)
I got ora-1031 insufficient privileges
user do not have permission to create view. grant create view and then try.
As sb92075 said copy paste all the command what you tried on your sql prompt.
I already granted scott a "create any view" privilege but still same error
Can you help please. Try to install db vault on your test server or laptop using this docs
1.And then grant select on hr.emp to scott;
2.The create a synoym scott.emp for hr.emp;
3.Then create view scott.emp_vew for scott.emp;
Alternatively you could do as below to see if the error continues after Data Vault has been shutdown
dvca -action disable