Hi everyone

I am trying to prevent some specific user to access all tables in database. IOW, this user must access only a few tables.

Steps:

1) To remove all public access I am using the following statement:
REVOKE ALL PRIVILEGES ON DATABASE MyDB FROM PUBLIC

But if I use SELECT * FROM INFORMATION.TablePrivileges the public access is still there.

I am accessing the engine using ADMINISTRADOR user/password combination.

2) To give access for specific table I will use (after the step 1 succed)
GRANT ALL PRIVILEGES ON TABLE Table1 TO ThisUser
GRANT ALL PRIVILEGES ON TABLE Table2 TO ThisUser
GRANT ALL PRIVILEGES ON TABLE TableN TO ThisUser

What am I doing wrong ? Or what I am missing ?

Eduardo

Jose


I had something similar - here's the post from Raul - its in the SQL group

-----------------------------------------------------------------------------------------------------------------------------------

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

-----------------------------------------------------------------------------------------------------------------------------------


My problem was that after revoking privileges noone could log on

Roy Lambert

Roy

Thanks for the answer.

After some try/fail situations I think I have understand the way to do that.

1) Revoke all privileges from all tables in the database

revoke all privileges on table "table1" from "public"'

2) And just grant privileges to desired tables and desired user

grand all privileges on table "table8" to "myuser

3) I will do that using the procedure below, already created

CREATE PROCEDURE MyProcedure
BEGIN        
   DECLARE xCursor CURSOR FOR xSql;
   DECLARE xTableName VARCHAR DEFAULT '';
   PREPARE xSql FROM 'select * from information.tables';    
   OPEN xCursor;
   FETCH FIRST FROM xCursor('Name') INTO xTableName;
   WHILE NOT EOF(xCursor) DO
      EXECUTE IMMEDIATE 'revoke all privileges on table "' + xTableName + '" from "public"';
      FETCH NEXT FROM xCursor('Name') INTO xTableName;
   END WHILE;
   CLOSE xCursor;   

   EXECUTE IMMEDIATE 'grant all privileges on "table8" to "myuser";
END

4) One thing I am still looking for is "how to execute or run a procedure"

Using this approach just "hide" all tables from database and only "table8" can be seen by "myuser".

Eduardo

Roy

Done.

CREATE PROCEDURE MyProcedure
BEGIN        
   DECLARE xCursor CURSOR FOR xSql;
   DECLARE xTableName VARCHAR DEFAULT '';
   PREPARE xSql FROM 'select * from information.tables';    
   OPEN xCursor;
   FETCH FIRST FROM xCursor('Name') INTO xTableName;
   WHILE NOT EOF(xCursor) DO
      EXECUTE IMMEDIATE 'revoke all privileges on table "' + xTableName + '" from "public"';
      FETCH NEXT FROM xCursor('Name') INTO xTableName;
   END WHILE;
   CLOSE xCursor;   

   EXECUTE IMMEDIATE 'grant select on "table8" to "myuser";
END

And after that I run a script

script()
begin
        call MyProcedure;
end;

And now everything is right.

Thanks in advance
Eduardo

Jose


Why not just put the code from the procedure into the script?

Roy Lambert

Roy

<<
Why not just put the code from the procedure into the script?
>>
Yes, I can do that but I put this in a procedure because I think I am going to put the procedure to run into a job during the night because the database structure (new tables) are created with a certain frequency and then I do not need to be concerned about it.

Eduardo