1. Helge Tjelta
  2. Valentina Server
  3. Monday, January 19 2015, 06:54 PM
  4.  Subscribe via email
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?
Comment
There are no comments made yet.
Ivan Smahin Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 1
Ivan Smahin Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 2
Helge Tjelta Accepted Answer
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.?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 3
Helge Tjelta Accepted Answer
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...
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 4
Johnny Harris Accepted Answer
Could you use "SHOW CONNECTIONS;" to find out who is connected to which database?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 5
Helge Tjelta Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 6
Ivan Smahin Accepted Answer
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
Comment
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 years ago
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 7
Helge Tjelta Accepted Answer
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 ?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 8
Ivan Smahin Accepted Answer
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).
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 9
Helge Tjelta Accepted Answer
OK, understandable.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 10
Helge Tjelta Accepted Answer
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 ?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 11
Ivan Smahin Accepted Answer
SELECT * FROM (SHOW GRANTS) WHERE privilegeObjectName like 'nameofdb'
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 12
Johnny Harris Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 13
Ivan Smahin Accepted Answer
It should be something like

db.sqlQuery( "USE master; SHOW GRANTS;" )
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 14
Johnny Harris Accepted Answer
Thank you Ivan. That worked.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 15
Helge Tjelta Accepted Answer
Thanks Ivan, that select line worked perfectly.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 16
Ruslan Zasukhin Accepted Answer
Ivan, do we need always to execute SHOW GRANTS against master db?

If yes, then we should add this info to WIKI
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Server
  3. # 17
  • Page :
  • 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