Hi Roland!
Thanks for the detailed writeup and sections from the SQL
standard. It's nice to know what it has in there for reference,
one of these days I should probably find a copy. :)
On Fri, Mar 19, 2010 at 07:02:36PM +0100, Roland Bouman wrote:
> Ok - fair enough. A catalog is a collection of schemas....now this:
>
> "
> 4.2.8.3 The Information Schema
> Every catalog contains an SQL-schema with the name INFORMATION_SCHEMA
> that includes the descriptors
> of a number of schema objects, mostly view definitions, that together
> allow every descriptor in that catalog to
> be accessed, but not changed, as though it was SQL-data.
> "
>
> mm, one information_schema per catalog. I don't mind, but I guess this
> means that once you commit to implementing catalogs, you have to go
> all the way, and do this too...if you don't, generic clients will get
> confused (use case: query or reporting tool that accesses drizzle via
> a JDBC driver. Driver says the RDBMS supports catalogs, client uses
> this information and then relies on an information_schema being
> present....)
We are already doing this at the schema/table level with the
authorization plugins. For example, If I am user X and only have access
to schemas A and B, my view of I_S will only contain those schemas
and associated tables. We simply need to extend the namespace for
authorization plugins to include catalog as well. Our API is currently:
virtual bool restrictSchema(const SecurityContext &user_ctx,
const std::string &schema)= 0;
virtual bool restrictTable(const SecurityContext &user_ctx,
const std::string &schema,
const std::string &table);
So we will probably simply add in:
virtual bool restrictCatalog(const SecurityContext &user_ctx,
const std::string &catalog)= 0;
And add 'catalog' arguments to the calls above.
> Now the puzzling thing is, I don't see anything in the standard that
> says how a user/role knows about catalogs...they only mention schema
> objects:
>
> "
> 4.6.12 Privileges
> A privilege represents a grant, by some grantor, to a specified
> grantee (which is either an authorization identifier,
> a role, or PUBLIC), of the authority required to use, or to perform a
> specified action on, a specified schema
> object.
> "
>
> Now, what they say about schemas and owners is this:
>
> "
> 4.2.8.2 SQL-schemas
> An SQL-schema, often referred to simply as a schema, is a persistent,
> named collection of descriptors. Any object whose descriptor is in
> some SQL-schema is known as an SQL-schema object. A schema, the schema
> objects in it, and the SQL-data described by them are said to be owned
> by the authorization identifier associated with the schema.
> "
>
> So, in short, if you want to adhere to the standard for this one, you
> should associate ownership with schemas, not catalogs. How users
> should be authorized for catalogs, is not clear. Nor is it clear to
> me what the benefit is of a catalog object, other than that it is just
> another level. However, if you do implement catalogs, you should also
> provide an information_schema implementation that is compatible with
> the existence of catalogs.
We're probably not taking the traditional role approach in the
standard. We're going to keep things a bit more simple as you can
see in the API calls above. Authorization entities will be loosely
coupled with catalogs/schemas/tables and will not be restricted by
any boundaries on what they have the potential to access.
> Personally, having catalogs is not high on my list. Only RDBMS i have
> worked with that has it, is SQL server and to me it is more a hassle
> than it's worth (IMO).
Agreed for many cases, as Brian mentioned, this is mainly useful in
multi-tenant environments where you want to give each account their
own schema namespace, without having to resort to schema prefix hacks.
> I do think a schema owner is a useful concept. The standard seems to
> imply schemas are always owned by an "authorization" which is to all
> intents and purposes an account (AFAICS). Oracle also implements a per
> schema owner and MS SQL agrees here too (as in, each schema has an
> owner)
We'll have the potential for multiple 'owners' depending on their
restriction level of operations/schemas. There won't be a designated
'user' owns 'schema', but just determined from what auth plugins allow.
> PS I am not saying drizzle should do what Oracle and MS do, but these
> are shining examples of popular RDBMS-es and for developers it helps
> if things like this work the same across the board.
Yeah, I think we're not worrying about traditional SQL roles/grants
like Oracle/MS, mainly just an easy, pluggable, set of permissions
for common tasks. Like we've said, many web shops have a single user
(root) and handle all permissions in the application. We just want
to extend that to a multi-tenant environment. At least this how I
understand it, perhaps some of the other Drizzle developers will have
a different take. :)
Thanks!
-Eric
> On Fri, Mar 19, 2010 at 6:20 PM, Jay Pipes <[email protected]> wrote:
> > Yep, fair enough. Thanks for the excellent explanation!
> >
> > -jay
> >
> > On Fri, Mar 19, 2010 at 1:18 PM, Eric Day <[email protected]> wrote:
> >> The argument I could see for not simply using a 'user' is that
> >> there may be multiple users that want to map to the same catalog
> >> namespace (essentially an account). So 'jay' and 'eric' have access
> >> to the 'drizzle.org' catalog, but we may have a different set of
> >> permissions. The 'account' entity needs to have some object with it,
> >> and you most likely don't want to reference other user accounts for
> >> this. For example:
> >>
> >> catalog
> >> schema
> >> table
> >>
> >> drizzle.org
> >> blog
> >> posts
> >> comments
> >> authors
> >> wiki
> >> pages
> >> accounts
> >>
> >> gearman.org
> >> wiki
> >> pages
> >> accounts
> >>
> >> Now users can map to any set of permissions for the catalog, schema,
> >> table, or any combination of them. Now you can have multiple users
> >> access drizzle.org catalog, or a single user access both drizzle.org
> >> and gearman.org catalogs.
> >>
> >> -Eric
> >>
> >> On Fri, Mar 19, 2010 at 12:10:39PM -0400, Jay Pipes wrote:
> >>> How would that be different from a user, then?
> >>>
> >>> In other words, why not just split the innodb buffer pool by the user
> >>> id (which, BTW, would require a major overhaul of InnoDB...)?
> >>>
> >>> What I'm asking is what would be the benefits of one more level of
> >>> taxonomy when the user ID already allows for such categorization?
> >>>
> >>> Cheers!
> >>>
> >>> jay
> >>>
> >>> On Fri, Mar 19, 2010 at 12:05 PM, Brian Aker <[email protected]> wrote:
> >>> > Think multi-tenancy. A user can create as many schemas as they like,
> >>> > and I
> >>> > can split the innodb pool up per catalog.
> >>> >
> >>> > Cheers,
> >>> > --Brian
> >>> >
> >>> > On Mar 19, 2010, at 8:52 AM, Jay Pipes <[email protected]> wrote:
> >>> >
> >>> >> NULL.
> >>> >>
> >>> >> I actually don't think catalogs are all that useful, FWIW...
> >>> >>
> >>> >> -jay
> >>> >>
> >>> >> On Thu, Mar 18, 2010 at 9:47 PM, Roland Bouman
> >>> >> <[email protected]>
> >>> >> wrote:
> >>> >>>
> >>> >>> Hi!
> >>> >>>
> >>> >>> On Thu, Mar 18, 2010 at 11:25 PM, Brian Aker <[email protected]>
> >>> >>> wrote:
> >>> >>>>
> >>> >>>> Do we want to just default the value to NULL?
> >>> >>>
> >>> >>> SQL standard says it should be NULL in case there is no support for
> >>> >>> catalogs.
> >>> >>>
> >>> >>>
> >>> >>>
> >>> >>> --
> >>> >>> Roland Bouman
> >>> >>> http://rpbouman.blogspot.com/
> >>> >>>
> >>> >>> Author of "Pentaho Solutions: Business Intelligence and Data
> >>> >>> Warehousing with Pentaho and MySQL",
> >>> >>> http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html
> >>> >>>
> >>> >>> _______________________________________________
> >>> >>> Mailing list: https://launchpad.net/~drizzle-discuss
> >>> >>> Post to : [email protected]
> >>> >>> Unsubscribe : https://launchpad.net/~drizzle-discuss
> >>> >>> More help : https://help.launchpad.net/ListHelp
> >>> >>>
> >>> >
> >>>
> >>> _______________________________________________
> >>> Mailing list: https://launchpad.net/~drizzle-discuss
> >>> Post to : [email protected]
> >>> Unsubscribe : https://launchpad.net/~drizzle-discuss
> >>> More help : https://help.launchpad.net/ListHelp
> >>
> >
> > _______________________________________________
> > Mailing list: https://launchpad.net/~drizzle-discuss
> > Post to : [email protected]
> > Unsubscribe : https://launchpad.net/~drizzle-discuss
> > More help : https://help.launchpad.net/ListHelp
> >
>
>
>
> --
> Roland Bouman
> http://rpbouman.blogspot.com/
>
> Author of "Pentaho Solutions: Business Intelligence and Data
> Warehousing with Pentaho and MySQL",
> http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
