I'm trying to create a new role that will only have SELECT privileges. OK That's easy, but when, after REVOKEing the Public role from a user, and I try and use it to logon, I get

---------------------------
ElevateDB Manager
---------------------------
ElevateDB Error #401 The database Configuration does not exist.
---------------------------
OK  
---------------------------

Anyone have any idea as to what privileges I should grant a role to allow a user with it but without Public to logon?


Roy Lambert

On 4/26/2020 9:26 AM, Roy Lambert wrote:
> I'm trying to create a new role that will only have SELECT privileges. OK That's easy, but when, after REVOKEing the Public role from a user, and I try and use it to logon, I get
>
> ---------------------------
> ElevateDB Manager
> ---------------------------
> ElevateDB Error #401 The database Configuration does not exist.
> ---------------------------
> OK
> ---------------------------
>
> Anyone have any idea as to what privileges I should grant a role to allow a user with it but without Public to logon?

Roy,

Some of this is just a guess so take with grain of salt.

EDBManager would throw this since it tries to query system config to
show various objects in UI. Using this user in theory should work from
app though i have no tried - app that just connects the session and then
executes queries against known db, tables and other objects.

Found an older post from Tim
(https://www.elevatesoft.com/forums?action=view&category=edb&id=edb_general&page=1&msg=10042#10042)
but look like you will need public role to query system config tables.

Does not appeat EDB currently allows privileges to be set for system
(maybe future feature request to Tim).

You might have to revise your logic to be more like

1. revoke public role permissions for your database(s) and other
relevant objects but keep public role for the user (this should provide
read only system config ability still).
(i.e. REVOKE ALL PRIVILEGES ON DATABASE <your db> FROM PUBLIC)

2. User also has your new role that is only one giving select
permissions to any objects you want


Raul

Raul


I didn't find that thread because I was stupid and only searched the sql newsgroup

Thanks for the answer and suggestion.

Just in case you're bored:

The application is going to be freeware accounts for micro organisations - the sort that use spreadsheets currently. Portable, single instance single user type of thing. I want them to have the option of encryption and logon security but if they don't want it just open up.

My logic (hmm not sure of that word) went:

1. try opening session with default encryption code - if it fails try with no encryption code and if that fails ask user to enter one.

2. when past encryption count the number of entries in the Users table - if more than System,  my encryption check user and a default user ask for logon details

3. let them do anything their little hearts desire

When any users are added drop the default user so they have to log on.

Roy Lambert