Hi, Shawn!
* SGreen@stripped <SGreen@stripped> [05/09/23 22:24]:
> Does this change mean that view security is going to be evaluated in the
> contexts of the people who created them or in the context of the users
> attempting to use them?
Actually the plan is to support both types of behavior.
This is achieved by adding optional SQL SECURITY clause to CREATE VIEW
statement. For example:
CREATE SQL SECURITY DEFINER VIEW v1 as SELECT * FROM t1; # case A
means that view 'v1' should be evaluated in security context of its
creator (This is default option since it is in line with SQL standard,
behavior of other RDBMSes, and MySQL's own behavior in earlier versions).
And:
CREATE SQL SECURITY INVOKER VIEW v1 as SELECT * FROM t1; # case B
means that view should be evaluated in the context of user who will
attempt to use it.
> How would this scenario work with your change? Here is the setup:
>
> Tables t1, t2, and t3 exist.
>
> UserA has full rights to t1,t2, and t3.
>
> UserB has SELECT rights to t1 and t2 but not t3.
>
> UserA defines a view that uses columns from both t1 and t3: TestView1
>
> Test 1:
>
> An administrator tries to grant SELECT rights to TestView 1 to UserB. What
> happens? Why?
This will succeed (assuming that administrator has enough rights to grant
this privilege). In case A it is perfectly sensible thing to do, and in
case B it is probably less sensible but still harmless (see below)...
> Test 2:
>
> (assuming the GRANT from event 1 succeeds) UserB tries to use TestView1 in
> a SELECT query. What happens? Why? Does the query completely fail? Will
> it fail only if UserB references a column that originates from t3? Will
> UserB be able to see the information from t3 exposed through TestView1?
In case A (if view was created with no SQL SECURITY option or with explicit
SQL SECURITY DEFINER option) UserB will be able to select all data exposed
through TestView1 (including columns from t3).
In case B query will fail completely as will fail any query for UserB
which references both tables t1 and t3 (it does not matter if UserB
does not use columns of view that originate from t3 or not, since
we may leak to him some information about t3 if we allow him to
obtain even the smallest bit of information from view).
>
> <<end scenario>>
>
> Some other questions I have about view security:
>
> >>Why is it important to know who made the view when it comes time to
> evaluate the view?
>
> >>What context does the owner's/creator's information provide to the task
> of rendering the view to a user?
In case A it is important to know who was creator of view to be able to
use his security context (i.e. set of privileges associated with him)
for access to tables used in view and for execution of stored functions
used in view. It as also important to know who was the view's creator
to be able to prevent access to view's data when this creator was dropped
or privileges on view's underlying tables were revoked from him.
In case B it does not matter who has created the view...
> >> When are user-level permissions determined when accessing a view? Do
> the user's permissions start and stop at the VIEW level or are they also
> inherited from the underlying tables (do a view's permission definitions
> override the definitions for the underlying tables)?
I am not sure that I understand this question. Could you please elaborate?
> Note: defining a view to expose just part of a table that a user normally
> couldn't access is currently allowed in several other RDBMS's.
As you can see it should be possible to do this in MySQL too.
> >> Will views have column-level security, like tables?
Why not ? Actually simple test involving granting privileges to some
of view's columns works for me ... :)
> What if a user tries to create a view that includes columns they normally
> could not reference? Will that succeed?
No, it won't succeed in both cases (case A is obvious and in case B
even if this view may be still useful to other users we still won't
allow to create it since we won't be able to check its correctness...)
> If it succeeds and a user that normally would have permission to view the
> base table data uses the new view would they be blocked from seeing that
> data because the view was created by someone restricted from viewing it
> in the first place?
Imagine the following situation. UserA has SELECT privileges on both columns c1
and c2 of table t1. So he successfully creates view on t1 which exposes values
from both columns. Now administrator revokes SELECT privilege on column c2
from him.
In case A (if the view was created with DEFINER security) noone will be ever
able to select something from this view (even if this UserB has full privileges
on t1 table) but of course it still ok to fetch data from t1 directly if you
have proper rights on it.
In case B (if the view was created with INVOKER security) everything depends
on privileges of user who uses this view... So in this case it does not matter
that privileges of UserA were revoked.
Best regards,
--
Dmitri Lenev, Software Developer
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification
