In an attempt to create field value security on the database side, I am trying to create the following view:
create view ArOpenItemV as
select * from pub."ar-open-item" join pub."sql-sec"
on pub."sql-sec"."user-id" = user()
and pub."sql-sec"."table-name" = 'ar-open-item'
and pub."sql-sec"."field-name" = 'ar-entity'
where pub."ar-open-item"."ar-entity" = pub."sql-sec"."field-value";
This works quite well when I was testing with just the select statement. I place the data into the sql-sec table and then the query gets data or not based upon the values in that table.
Once I added the create view line the SQL Explorer tool came back and said that the "Server did not respond". Looking into it further I notice I had been disconnected when I ran this code. I verified I was the only user attached to the DB and that I was the DBA.
Does anyone have any ideas? Maybe there is another solution that could work.
create view ArOpenItemV as
select * from pub."ar-open-item" join pub."sql-sec"
on pub."sql-sec"."user-id" = user()
and pub."sql-sec"."table-name" = 'ar-open-item'
and pub."sql-sec"."field-name" = 'ar-entity'
where pub."ar-open-item"."ar-entity" = pub."sql-sec"."field-value";
This works quite well when I was testing with just the select statement. I place the data into the sql-sec table and then the query gets data or not based upon the values in that table.
Once I added the create view line the SQL Explorer tool came back and said that the "Server did not respond". Looking into it further I notice I had been disconnected when I ran this code. I verified I was the only user attached to the DB and that I was the DBA.
Does anyone have any ideas? Maybe there is another solution that could work.