Not to take a step back but I thought Drizzle was not going to have any authentication. It sounds like there is talk about adding this back in using a more fine-grained method than something like PAM? Not that I disagree (for some reason, the idea of catalogs makes me excited though I don't know why :) but were permissions removed largely for performance reasons?
Just curious on the context is all :) Tim S. On Mar 19, 2010, at 2:23 PM, Eric Day wrote: > 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 > _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
