Dmitri Lenev <dlenev@stripped> wrote on 09/28/2005 04:06:19 PM:
> 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.
That's a great plan.
>
> 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?
You answered this already with the description of how the SQL SECURITY
clause will work.
>
> > 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 ... :)
Excellent! That means that it will be possible to create a view exposing
just parts of certain tables and still limit some users to certain parts
of that view.
For example: research data from 2 labs are kept in a table. Some users can
work with the data directly (direct table access). Some users will be able
to only work with views that are specific to their lab (CREATE VIEW
vw_Lab1ReportsBlue AS SELECT * FROM reportmetadata WHERE labID = 1 and
groupname='blue'), while others will only be able to work with parts of
that view (GRANT SELECT (title, date, author) ON labdb.vwLab1ReportsBlue
...)
That will make some of my managers very happy.
>
> > 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
bothcolumns 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
fullprivileges
> 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
>
Thank you very much!!!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
