1. Helge Tjelta
  2. Valentina Server
  3. 月, 1月 19 2015, 06:54 PM
  4.  メールで購読
I know that VServer has a "master" table for users at server level, but I want to have several databases on my server, and I want to have different users (i.e. 10 from one organisation, and 5 from another company etc), but if I had listed users available on login in my front end (Xojo), then I would have got all 15. So how do you do it with different users for different databases.

I'm thinking of having the users stored in a table in the database itself, and only have a "masterUser" for each database on the login to the server, this is for getting the users, then the user has to use the login and password to contiue in the front end app.

I of course then have to find a way to enqrypt the password, or at least not store it in plain text.

Or have I totaly misunderstood something?
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
There are 2 approaches:

1. One vServer per "organization". You can run few vServers (just use different IP ports for them) on the single comp.
2. Permissions. Firstly you should enable them in the vServer's ini. Then, you should grant access to that databases to the particular users (See GRANT/REVOKE sql statements syntax).

BTW, passwords are not stored as plain text.
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
Or event third approach (it could be used as additional one)

ACL (Access Control List) for particular database (See SHOW DATABASES properties).
So you can allow/disallow access to the db for particular IP-addresses or/and subnets.
コメント
There are no comments made yet.
Helge Tjelta 承諾済みの回答
OKAY, if I go the option 2, how when I log into on database, I get a list of users only for that database (like a popupmenu).

If I grant db to users, will that be possible to retrieve on a GetUserName command... but as far as I can see this would list alle users and not only those spesificly to a given db.?
コメント
There are no comments made yet.
Helge Tjelta 承諾済みの回答
I think that option 1 would be the easiest, but it would be nice to have say 4 db on one server, each with its own users...
コメント
There are no comments made yet.
Johnny Harris 承諾済みの回答
Could you use "SHOW CONNECTIONS;" to find out who is connected to which database?
コメント
There are no comments made yet.
Helge Tjelta 承諾済みの回答
hi johnny, I wanted to find out a way to list which users can connect, before they connect.

this way I can use a popupmenu to login with. this only works on a small number of users , I know. but still.
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
There is "SHOW GRANTS" statement to see current state of privileges on the vServer.
The WIKI was missed for that command (fixed already) - http://www.valentina-db.com/docs/dokuwiki/v5/doku.php?id=valentina:vcomponents:vsql:reference:show:show_grants
コメント
I see this command also should be added to page: SQL 5.x Grammar.
I have added it now:

http://www.valentina-db.com/docs/dokuwiki/v5/doku.php?id=valentina:vcomponents:vsql:reference:sqlgrammar:5.0
  1. Ruslan Zasukhin
  2. 9 年前
There are no comments made yet.
Helge Tjelta 承諾済みの回答
OK, show grants shows me the results in the master table.

Is it best to interact with this in a normal sql ways as I do in the other tables/databases ?

Or should this be done in vsp ? (like creating new users and priveliges...) ? The customer might need to create his own users ?
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
I'm not sure I understand your question. If you are about manually changing master's tables - never do this.
For permissions you should use SQL statements like GRANT, REVOKE, SHOW GRANTS.
(admin privilege is needed for GRANT/REVOKE).
コメント
There are no comments made yet.
Helge Tjelta 承諾済みの回答
OK, understandable.
コメント
There are no comments made yet.
Helge Tjelta 承諾済みの回答
But how do I get a cursor with only grants for a spesific db.

Show grants Where privilegeObjectName like 'nameofdb' <---- alike this ?

if not i have to loop through the whole usercursor and get those with the chosen db ?
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
SELECT * FROM (SHOW GRANTS) WHERE privilegeObjectName like 'nameofdb'
コメント
There are no comments made yet.
Johnny Harris 承諾済みの回答
Executing "SHOW GRANTS;" from my .Net project ends up with a vException.

The error message I get is "Table sysuser not found.".

I've tried con.sqlQuery, con.sqlExecute, db.sqlQuery, db.sqlExecute. I've also tried executing "USE master;" before sending the "SHOW GRANTS;" query.

Could anyone tell me what I'm doing wrong? This works fine from VStudio.

I'm sure it's something simple. I've read the docs, but probably missed something.
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
It should be something like

db.sqlQuery( "USE master; SHOW GRANTS;" )
コメント
There are no comments made yet.
Johnny Harris 承諾済みの回答
Thank you Ivan. That worked.
コメント
There are no comments made yet.
Helge Tjelta 承諾済みの回答
Thanks Ivan, that select line worked perfectly.
コメント
There are no comments made yet.
Ruslan Zasukhin 承諾済みの回答
Ivan, do we need always to execute SHOW GRANTS against master db?

If yes, then we should add this info to WIKI
コメント
There are no comments made yet.
  • ページ :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories