Ah ok. So basically you will be able to specify which database (and/or catalog) someone can access but that's basically an all access backstage pass to the DB (ie no allowing reads but not writes, etc.)?
On Mar 19, 2010, at 3:32 PM, Eric Day wrote: > Most things around authentication and authorization were removed early > on, but pluggable authentication was added back in shortly after (PAM > and HTTP plugins). Monty Taylor recently added in an authorization > plugin point to restrict what schemas, tables, and processes a user > can access. We're still thinking about simple ACLs like read/write, > but not a full-blown grant system like traditional RDBM's have. > > The other main differences is that these user/account settings will > most likely not be managed through the database plugins. Drizzle will > only read this information from the plugin source. > > -Eric > > On Fri, Mar 19, 2010 at 03:07:31PM -0500, Tim Soderstrom wrote: >> 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
