In DBISAM3 I can user the Server Admin to set up users and databases PLUS a
cross reference

e.g.  DB1 can be accessed by UserA and UserB
      DB2 can be accessed by  UserA only
      DB3 can be accessed by  UserB only

After login to the server, I present a combobox of databases available to
the user e.g. UserA gets a comboox with DB1 and DB2..

The actual code posted be below my sig.

I can see how to do the same in ElevateDB ... Probably blindly obvious, just
can't see it.

Help!

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

APM is the TDBISAMDatabase

       slDBNames := TStringList.Create;
       slDBDesc := TStringList.Create;
       try
         Session.GetRemoteDatabaseNames(slDBNames);
         if slDBNames.Count = 0 then
         begin
           MessageDlg('User not set up', mtError, [mbOK], 0);
           Application.Terminate;
         end
         else if slDBNames.Count > 1 then
         begin
           SelectDBForm := TSelectDBForm.Create(nil);
           try
             SelectDBForm.cboDBNames.Items.Text := slDBNames.Text;
             SelectDBForm.cboDBNames.Text := slDBNames[0];
             if SelectDBForm.ShowModal = mrOK then
               APM.RemoteDatabase := SelectDBForm.cboDBNames.Text
             else Application.Terminate;
           finally
             SelectDBForm.Free;
           end;
         end
         else APM.RemoteDatabase := slDBNames[0];
       finally
         slDBNames.Free;
         slDBDesc.Free;
       end;

Of course I meant " I *can't* see how to do the same in ElevateDB" not "I
can see how to do the same in ElevateDB" !!!!

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:643A8936-4C34-45F7-80E6-263099249A41@news.elevatesoft.com...
> In DBISAM3 I can user the Server Admin to set up users and databases PLUS
> a cross reference
>
> e.g.  DB1 can be accessed by UserA and UserB
>       DB2 can be accessed by  UserA only
>       DB3 can be accessed by  UserB only
>
> After login to the server, I present a combobox of databases available to
> the user e.g. UserA gets a comboox with DB1 and DB2..
>
> The actual code posted be below my sig.
>
> I can see how to do the same in ElevateDB ... Probably blindly obvious,
> just can't see it.
>
> Help!
>
> Cheers
>
> Jeff
> --
> Jeff Cook
> Aspect Systems Ltd
> www.aspect.co.nz
>
> APM is the TDBISAMDatabase
>
>        slDBNames := TStringList.Create;
>        slDBDesc := TStringList.Create;
>        try
>          Session.GetRemoteDatabaseNames(slDBNames);
>          if slDBNames.Count = 0 then
>          begin
>            MessageDlg('User not set up', mtError, [mbOK], 0);
>            Application.Terminate;
>          end
>          else if slDBNames.Count > 1 then
>          begin
>            SelectDBForm := TSelectDBForm.Create(nil);
>            try
>              SelectDBForm.cboDBNames.Items.Text := slDBNames.Text;
>              SelectDBForm.cboDBNames.Text := slDBNames[0];
>              if SelectDBForm.ShowModal = mrOK then
>                APM.RemoteDatabase := SelectDBForm.cboDBNames.Text
>              else Application.Terminate;
>            finally
>              SelectDBForm.Free;
>            end;
>          end
>          else APM.RemoteDatabase := slDBNames[0];
>        finally
>          slDBNames.Free;
>          slDBDesc.Free;
>        end;
>
>
>

Jeff


Users & permissions. I can't give you more than that right now because I'd need to check my code / experiment and I'm busily pulling hair out because I'm setting up a new PC.

Roy Lambert

Jeff,

<< I can see how to do the same in ElevateDB ... Probably blindly obvious,
just can't see it. >>

This is the section of the manual that you need:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=User_Security

It's a little more complicated than DBISAM, but not by much.  In addition to
setting the privileges for the role/user for the database, you'll also need
to do so for all database objects that you want them to access (tables,
views, functions, and procedures).   You should definitely consider using
roles for this, since it will help eliminate a lot of duplicate permission
settings.

How many databases are you going to be normally defining in a typical
installation ?

Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:7FD1A561-6DF0-4727-A809-D49C2481AFBB@news.elevatesoft.com...
> Jeff,
>
>
> This is the section of the manual that you need:
>
> http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=User_Security
>
> It's a little more complicated than DBISAM, but not by much.  In addition
> to setting the privileges for the role/user for the database, you'll also
> need to do so for all database objects that you want them to access
> (tables, views, functions, and procedures).   You should definitely
> consider using roles for this, since it will help eliminate a lot of
> duplicate permission settings.

This is how far I have got so far - though if I use Roles perhaps it would
be different?

SELECT P."Name",
      D."Description"
FROM "DatabasePrivileges" P
JOIN "Databases" D ON D."Name" = P."Name"
WHERE P."GrantedTo" = 'Jeff.Cook'   /* Jeff.Cook = Session.CurrentUser */
 AND P."Privilege" = 'Select'

Seems toi give me a list of databases that the user can access.  I have set
myself up at some stage to access one of the databases. This SQL just shows
me able to access just one database.  I can't recall how I did that set up
and now I have come back to it I can't work out how to associate a user with
a database.

>
> How many databases are you going to be normally defining in a typical
> installation ?

Hmmm...  The general pattern is that there will be a server running on the
client's machine.  Office bound users will access usually just one database,
but we have clients that have two or three databases (Rent Rolls) so when
theystart the app and log in they will be given a choice of databases.

BUT we plan to replicate the databases to a "cloud" server and there will be
(currently) 60+ databases.  I will have a login or logins that will be
associated with all (or maybe groups of) databases as will my colleague who
does the user support.

Non-office bound users will have notebooks with databases that replicate to
the "cloud".

LATER, we will have some functions that use EWB to access the database from
tablet or phone - perhaps we will rewrite the whole app in EWB ...

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Starting to understand this but still struggling

1.   Created a User

CREATE USER "Jeff.Cook"
PASSWORD '<Password>'

2.   Created at Role

CREATE ROLE "DB1264Access"

3.   Granted role to my user

GRANT "DB1264Access" TO "Jeff.Cook"

4.   Granted priivileges

GRANT ALL PRIVILEGES ON
ON DATABASE "DB1264"
TO "DB1264Access"

5.   Tried many permutations trying to get a list of users and databases
that they can access, allowing for privileges granted to users AND roles.
This is the latest one:-

SELECT DISTINCT
      U."Name" AS "UserName",
      CAST(U."Description" AS VARCHAR(50)),
      DP."Name" AS "Database"
FROM "Users" U
LEFT OUTER JOIN "UserRoles" UR ON UR."GrantedTo" = U."Name"
LEFT OUTER JOIN "DatabasePrivileges" DP ON (DP."GrantedTo" = U."Name")
                                       OR (DP."GrantedTo" = UR."GrantedTo")
WHERE ((UR."GrantedTo" IS NOT NULL)
  OR  (DP."GrantedTo" IS NOT NULL))
 AND U."Name" = 'Jeff.Cook'
ORDER BY "UserName"

.... which only gives the privileges granted to individual users, not through
roles.


There must surely be a simpler way - but I'll be happy with a complex one
that works

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:CE0FDD44-2F93-4824-9678-DB3A5CB80078@news.elevatesoft.com...
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
> news:7FD1A561-6DF0-4727-A809-D49C2481AFBB@news.elevatesoft.com...
>> Jeff,
>>
>>
>> This is the section of the manual that you need:
>>
>> http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=User_Security
>>
>> It's a little more complicated than DBISAM, but not by much.  In addition
>> to setting the privileges for the role/user for the database, you'll also
>> need to do so for all database objects that you want them to access
>> (tables, views, functions, and procedures).   You should definitely
>> consider using roles for this, since it will help eliminate a lot of
>> duplicate permission settings.
>
> This is how far I have got so far - though if I use Roles perhaps it would
> be different?
>
> SELECT P."Name",
>       D."Description"
> FROM "DatabasePrivileges" P
> JOIN "Databases" D ON D."Name" = P."Name"
> WHERE P."GrantedTo" = 'Jeff.Cook'   /* Jeff.Cook = Session.CurrentUser */
>  AND P."Privilege" = 'Select'
>
> Seems toi give me a list of databases that the user can access.  I have
> set myself up at some stage to access one of the databases. This SQL just
> shows me able to access just one database.  I can't recall how I did that
> set up and now I have come back to it I can't work out how to associate a
> user with a database.
>
>>
>> How many databases are you going to be normally defining in a typical
>> installation ?
>
> Hmmm...  The general pattern is that there will be a server running on the
> client's machine.  Office bound users will access usually just one
> database, but we have clients that have two or three databases (Rent
> Rolls) so when theystart the app and log in they will be given a choice of
> databases.
>
> BUT we plan to replicate the databases to a "cloud" server and there will
> be (currently) 60+ databases.  I will have a login or logins that will be
> associated with all (or maybe groups of) databases as will my colleague
> who does the user support.
>
> Non-office bound users will have notebooks with databases that replicate
> to the "cloud".
>
> LATER, we will have some functions that use EWB to access the database
> from tablet or phone - perhaps we will rewrite the whole app in EWB ...
>
> Cheers
>
> Jeff
> --
> Jeff Cook
> Aspect Systems Ltd
> www.aspect.co.nz
>
>

Hi

I think that I have cracked it.

The SQL below seems to give me the correct result, with the UNION sneekily
removing duplicates where a user has direct priivileges and also through
roles.

It works and executes in "0 secs", but perhaps there is a simpler way?

Cheers

Jeff

SELECT DP."Name" AS "Database",
      CAST(D."Description" AS VARCHAR(50))
FROM  "DatabasePrivileges" DP
LEFT OUTER JOIN "Roles" R ON R."Name" = DP."GrantedTo"
LEFT OUTER JOIN "UserRoles" UR ON UR."Name" = R."Name"
LEFT OUTER JOIN "Users" U ON U."Name" = UR."GrantedTo"
LEFT OUTER JOIN "Databases" D ON D."Name" = DP."Name"
WHERE R."Name" <> 'Administrators'
 AND R."Name" <> 'Public'
 AND U."Name" = :User
UNION
SELECT DP."Name" AS "Database",
      CAST(D."Description" AS VARCHAR(50))
FROM  "DatabasePrivileges" DP
LEFT OUTER JOIN "Users" U ON U."Name" = DP."GrantedTo"
LEFT OUTER JOIN "Databases" D ON D."Name" = DP."Name"
WHERE U."Name" = :User
ORDER BY "Database"


--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz