Services local db, remote db, and schema questions


#1

Windows 7 x64
Bitnami installer

If I try to add a new local database connector I do not have that option in the Services/Type drop down list. Local SQL DB type does not exist, Remote SQL DB exists.

I think I understand what you are doing but it is a little confusing. The Local SQL DB refers to the MySQL instance running from the bitnami install pointing to the default database name=bitnami_dreamfactory. So really it is a remote database connection. By saying its local my first thought was it was a custom db, not just a database running on the default Bitnami Mysql

So when I wanted a new api that pointed to a new database I created a Remote SQL DB connection still pointing to my Bitnami Mysql but to my new database.

See the confusion by having Local SQL DB type, so either allow me to use Local SQL DB type when I create a new database service that just points to the default MySql instance or get rid of Local SQL DB type and just use Remote SQL DB type and you can have your default service just point to the default dream factory database.

The Schema screen note says you can create a new DB service(local or remote) but you can only create Remote, when referring to DB’s

I am also having an issue with the Schema, when I created a new db service called myapp that pointed to database called myapp running on the Bitnami Mysql instance, the same server where the ditnami_dreamfactory database is located. The schema is not restricted to the myapp database. Even though in the connection string set the dbname=myapp, it still lists all the tables from the bitnami_dreamfactory database.

Here are some screen shots of settings as you can see myapp is point to a database with the name myapp, myapp database has one table called test1, I have a second database called mysecondapp with a table of test2.

What I would expect to see is only table from the database named myapp, instead I see table from 3 databases, Two that I created myapp and mysecondapp and the default dreamfactory db

Let me know if I am doing something wrong.

I really am excited about this product as I was just about to write up a new rest api using Laravel,Eloquent ORM or maybe Slim. But your solution does sound like a dream, it would be almost automatic to have a new api for each app with database that you create, looking forward to digging deeper into dreamfactory. Having built in security, being able to restrict data to user id, ect ect, all just what we need.

The UI for setting up Service Access using roles could be a little easier, its a pain to have to choose each table and setup security, it would be better displayed in a data-grid with checkboxes and select and unselect all, like the way MS SQL gui does its permissions, just easier to do permissions on many tables.

Haven’t looked into using Keys for permission so maybe thats the anwers

Any hope this helps, and thanks for the cool product!


#2

Hi @delebash, I’m looking into this issue where tables from one DB are showing up under the schema of another DB. Could you post the promised screenshots for my reference?

As for the naming of the Service Type “Remote SQL DB” you’re correct that it’s fairly confusing. The plan is to change it to simply “SQL DB” just like “NoSQL DB” in the same list.


#3

Sure, sorry for not including the screen shots,
It should be restricted to testdb, but it is showing tables from anotherdb and the default bitnami db

Thanks,
Dan


#4

Actually, the DB service naming will change to replace “Local” with “Native” and remove the “Remote” terminology altogether. That way you have on one hand the native DB installed with DSP, and on the other hand all other DBs you could connect to.

As for seeing tables from other DBs, DSP database services are set up to allow access to everything that the credentials used for the service have been given access to. If you don’t want to list other DBs available from that service, don’t give the same credentials access to them. If you use true admin credentials on the service configuration, then that service could see/do everything, which is likely inadvisable for other reasons. Of course within the DSP you may control access via user or role, but this will not prevent the other DBs from being listed and cross-DB access is much better restricted at the level of the database itself.


#5

Thanks,

Ok so using sql credentials makes sense, It was a little confusing at first because you specify the database to connect to in the connection string, I guess this is just like a default schema, it just made me think that it should be limited to the schema specified in the connection string, but if you use root admin privileges then you still have access to all schema’s, because I am just testing I was not setting up specific sql user privs. So instead you should use normal sql privs to restrict to what schema’s are listed. Got it!


#6

Yes sir, once your permissions and DBs are set up for production this issue would have evaporated anyway. Thanks for using DSP and I look forward to answering any more questions you may have. :smile: