Incubator-superset: Allow table to exist multiple times for different databases, OR allow aliasing of table name

[jasonshah]

I would like to support different users accessing the same table with different entries in the "Sql" field for the table. This would give me a way of constraining a user to have a more limited view of data from Caravel vs. relying on the database to enforce this constraint.

For example, assume there is a table named "logins" in the database. The main user, userA, has access to "logins" unrestricted. The restricted user, userB, has access to logins where all queries have appended "select * from logins where userid=123". To accomplish this, I'd expect to be able to add "logins" table multiple times to the list and customize the Sql field for each instance of the table.

• [X] I have checked the caravel logs for python stacktraces and included it here as text if any
• [X] I have reproduced the issue with at least the latest released version of caravel
• [X] I have checked the issue tracker for the same issue and I haven't found one similar

Caravel version

0.12.0. Git commit 518fbf562cca638cf14d97dfae92756091630eb2

Expected results

Add a table/view with the same name but a different Database, and I could name the table with an an alias to reference that table for that user (e.g. "logins_userB"). Or, allow me to add the same table name multiple times (without an alias), but still allow me to customize each one.

Actual results

Add a table/view with the same name but a different Database, and I see:

(raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (sqlite3.IntegrityError) UNIQUE constraint failed: tables.table_name [SQL: 'INSERT INTO tables (created_on, changed_on, table_name, main_dttm_col, description, default_endpoint, database_id, is_featured, user_id, "offset", cache_timeout, schema, sql, params, changed_by_fk, created_by_fk) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'] [parameters: ('2016-11-01 10:46:53.993413', '2016-11-01 10:46:53.993541', 'logins', None, None, None, 2, 0, None, 0, None, 'public', None, None, 1, 1)]

[xrmx]

Looks like a dup of #1515. Closing.

[jasonshah]

Yes, at a high level that issue seems on the right track. Let's see what emerges, else (or in addition) I'll ask my team to pitch in also.

[xrmx]

@jasonshah tell your team that patches are always welcome ;)

[ganshanshan]

Dear jasonshah

Your goal is to analysis the data which come from different database in the same slice in Caravel?

[jasonshah]

@ganshanshan my goal is to have different users (with different levels of permissions) be able to query the same Postgres table or view. This will allow us to centralize access management within Caravel, and not split it between Caravel and within Postgres.

I don't expect that the same slice is used; I expect that different users with different permissions will have access to different slices.

[ganshanshan]

@jasonshah thanks very much,my goal is the same, but our idea is that ,
First,we build a session variable named as USER or ROLE in Caravel.
Second,we should have a customized database table named W_AUTH_DEPARTMENT_D,which has two core columns ROLE,DEPARTMENT_ID.
Then,we develop a data-auth function in the Table ,with which we can join the table with W_AUTH_DEPARTMENT_D where W_AUTH_DEPARTMENT_D.DEPARTMENT_ID = table.department_id and W_AUTH_DEPARTMENT_D.ROLE =@{ROLE}.

may be this can help.If your team finish the it ,your reply is very expected.

[mistercrunch]

@jasonshah , can't you accomplish that with views today? Views can expose a subset of he columns or rows, and users can be given access to these views. Views can currently be managed using database views and using them as table in Caravel.

Or course this approach won't scale to match a very complex security model (say you have thousands of sales teams and want people to have access only to the data of their teams).

---

@ganshanshan we already support the concept of roles in the he security model, can't you just create a role for each one of your departments?

[ganshanshan]

@mistercrunch can you show me how?thanks

[jasonshah]

@mistercrunch I assume you mean views in our database (Postgres)? Yes, we could, however we would like to standardize the placement of access control restrictions. Having some access control restrictions in Postgres and others in Caravel is less than ideal for our team. Furthermore, making changes to Postgres requires our engineering team to get involved, which makes administering this system far more expensive in the long run.

[mistercrunch]

It's also possible to define "logical views" in Caravel by pasting SQL in that field of the "table" definition. That's probably your best bet until we come up with some sort of "forced-predicates" feature.

[jasonshah]

@mistercrunch sorry to bring this back up, but do you see any issue with removing the UNIQUE constraint on the _tables_ table? This would resolve this specific issue for us.