| Author |
Message |
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
 Posted: Wed Feb 16, 2005 10:44 am Post subject:
Re: How do I make a VIEW read-only?
|
|
|
Thiru wrote:
| Quote: | I think
grant select on <view name> to user | group <name
will solve the problem.
Correct me if iam wrong.
Cheers,
Thiru
WantedToBeDBA
From an authorization perspective yes. From a locking perspective, no. |
When DB2 for LUW compiles a DML statement that happens independent of
authorization.
Only when the statement executes is authorization validated.
Another user with more authorization can execute the same statement
without needing to recompile.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab |
|
| Back to top |
|
 |
Thiru
*nix forums addict
Joined: 20 May 2005
Posts: 80
|
| Posted: Wed Feb 16, 2005 8:46 am Post subject:
Re: How do I make a VIEW read-only?
|
|
|
I think
grant select on <view name> to user | group <name>
will solve the problem.
Correct me if iam wrong.
Cheers,
Thiru
WantedToBeDBA |
|
| Back to top |
|
|
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Tue Feb 15, 2005 10:30 pm Post subject:
Re: How do I make a VIEW read-only?
|
|
|
Mark A wrote:
| Quote: | "Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:37er9aF5ei42iU1@individual.net...
The READ ONLY column properties on typed views was introduced in order
to create subviews with expressions for columns which are natively
updatable in the parent.
The easiest way to make a view READ ONLY is this:
CREATE VIEW v1 AS SEELCT ....FROM ...., TABLE(VALUES (1)) AS A(a);
The optimizer will throw the VALUES(1) out, so it should be harmless.
Cheers
Serge
Serge Rielau
Are you suggesting that no share locks are taken on the real table?
|
No. I'm suggesting that this makes the view READ ONLY.
Operating on a hunch here w.r.t. OP's question.
There are various clients (and apps, and users, ...) which are rather
sloppy when it comes to defining a cursor as read only.
So any cursor opened on an updatable query will hold a U lock on the row
it positions even if the isolation level is UR.
This trick will make away with the problem. So UR will take no locks.
Cheers
Serge
PS: I learned that some JDBC drivers by default even presume cursors to
be scrollable... now there is a way to wreck performance.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab |
|
| Back to top |
|
|
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Tue Feb 15, 2005 7:01 pm Post subject:
Re: How do I make a VIEW read-only?
|
|
|
"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:37er9aF5ei42iU1@individual.net...
| Quote: | The READ ONLY column properties on typed views was introduced in order
to create subviews with expressions for columns which are natively
updatable in the parent.
The easiest way to make a view READ ONLY is this:
CREATE VIEW v1 AS SEELCT ....FROM ...., TABLE(VALUES (1)) AS A(a);
The optimizer will throw the VALUES(1) out, so it should be harmless.
Cheers
Serge
Serge Rielau
|
Are you suggesting that no share locks are taken on the real table? |
|
| Back to top |
|
|
aj
*nix forums addict
Joined: 28 Jul 2005
Posts: 60
|
| Posted: Tue Feb 15, 2005 6:06 pm Post subject:
Re: How do I make a VIEW read-only?
|
|
|
Cool way to do that there, Serge. Plus this helped
me w/ an unrelated thing where I wanted a view to just
return a group of hardcoded values while acting just
like a table (for join purposes).
thanks :)
aj
Serge Rielau wrote:
| Quote: | aj wrote:
DB2 WSE 8.1 FP5
Red Hat Linux AS 2.1
I have simple view definition like:
create view B as
select * from A ;
How can I make B READ-ONLY so that no locks are
ever created when people query B?
In the SQL reference doc, under CREATE VIEW, I see
reference with-options, where you can somehow specify
that a column is read-only, but I get syntax errors
when I try to use this. There's no super or sub
table stuff w/ my view or base table - is that what
the doc is referring to?
Any help appreciated.
aj
The READ ONLY column properties on typed views was introduced in order
to create subviews with expressions for columns which are natively
updatable in the parent.
The easiest way to make a view READ ONLY is this:
CREATE VIEW v1 AS SEELCT ....FROM ...., TABLE(VALUES (1)) AS A(a);
The optimizer will throw the VALUES(1) out, so it should be harmless.
Cheers
Serge
|
|
|
| Back to top |
|
|
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Tue Feb 15, 2005 4:54 pm Post subject:
Re: How do I make a VIEW read-only?
|
|
|
aj wrote:
| Quote: | DB2 WSE 8.1 FP5
Red Hat Linux AS 2.1
I have simple view definition like:
create view B as
select * from A ;
How can I make B READ-ONLY so that no locks are
ever created when people query B?
In the SQL reference doc, under CREATE VIEW, I see
reference with-options, where you can somehow specify
that a column is read-only, but I get syntax errors
when I try to use this. There's no super or sub
table stuff w/ my view or base table - is that what
the doc is referring to?
Any help appreciated.
aj
The READ ONLY column properties on typed views was introduced in order |
to create subviews with expressions for columns which are natively
updatable in the parent.
The easiest way to make a view READ ONLY is this:
CREATE VIEW v1 AS SEELCT ....FROM ...., TABLE(VALUES (1)) AS A(a);
The optimizer will throw the VALUES(1) out, so it should be harmless.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab |
|
| Back to top |
|
|
aj
*nix forums addict
Joined: 28 Jul 2005
Posts: 60
|
| Posted: Tue Feb 15, 2005 4:09 pm Post subject:
How do I make a VIEW read-only?
|
|
|
DB2 WSE 8.1 FP5
Red Hat Linux AS 2.1
I have simple view definition like:
create view B as
select * from A ;
How can I make B READ-ONLY so that no locks are
ever created when people query B?
In the SQL reference doc, under CREATE VIEW, I see
reference with-options, where you can somehow specify
that a column is read-only, but I get syntax errors
when I try to use this. There's no super or sub
table stuff w/ my view or base table - is that what
the doc is referring to?
Any help appreciated.
aj |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
IBM DB2
