Support for non-PAT authentication types

[natemcfeters]

Would be nice to extend this to use msal for additional authentication types, like device flow.

[niolap]

@cxxman posted above the design suggestion. Ready for feedback.

[cxxman]

So what about groups? I suggest to not implement them.

[cxxman]

I think this model can be simplified a bit. read_write, read_only and admin can be permissions that can be granted to user. admin can read and update data, and also can do DDL, read_only can only read data, read_write can both read and write data but no DDL. In addition to that, user can be granted permission to create and drop databases - these two should be granted separately and admin should not include this.

[niolap]

@cxxman I like this simplification. But, from my point of view, there is an instance administrators and database users. There is no administrator per database. So I would sum up everything like this:

• read_write: can (Query + DML + DDL) only in a database (on database <database>) or all databases (on database all)
  • The goal is to have a quick permission that allow user to to work completely within his database
• read_only: can Query only in a database (on database <database>) or all databases (on database all)
• admin: can do everything in all databases (on database all). root is admin be design (not revokable).
• create_database: can create database and has automatically read_write permission on its created database.
  • By default, a created user can only create a session. He must be granted the permission to use a database or to create one explicitly by a user having an admin permission.

In the future, we may add other permissions like ddl, dml and so on and maybe classical create table, select table, update table etc. But no such need now.

Don't you think?

[cxxman]

I think we need to have more fine-grained permissions like select table underneath, otherwise it will be significantly harder to add them later, but do not expose them now into SQL. SQL will have those higher level things like read_only or read_write, which will map into an appropriate set of those fine-grained permissions.

[niolap]

So what about groups? I suggest to not implement them.

Yes. Not needed now.

[niolap]

I think we need to have more fine-grained permissions like select table underneath, otherwise it will be significantly harder to add them later, but do not expose them now into SQL. SQL will have those higher-level things like read_only or read_write, which will map into an appropriate set of those fine-grained permissions.

100% Ok.

[niolap]

@cxxman one point come up to my mind and I just would like to be sure, when we grant/revoke permission to/from a database object, this must include all existing and future objects. Meaning that if I create a table T in database D after having granted permission READ_ONLY for user U, then user U will be able to select T without having to execute a second grant command. Correct?

[cxxman]

@niolap For this we need to implement special form of the GRANT statement, like this:

-- for the specified database
GRANT permissions ON database_name.* TO user_name

-- for a currently selected database
GRANT permissions ON * TO user_name

Notice that star instead of table name.

[niolap]

@niolap For this we need to implement special form of the GRANT statement, like this:

-- for the specified database
GRANT permissions ON database_name.* TO user_name

-- for a currently selected database
GRANT permissions ON * TO user_name

Notice that star instead of the table name.

@cxxman Yes. I think this is the best way to implement this. Then, it must be included in this issue because we'll need it significantly for our project.

[cxxman]

@niolap Don't we also need some SQL command which will show current permissions granted to user?