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
