Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
Revoke privileges |
Tue, Nov 10 2020 9:31 AM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | 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 |
Tue, Nov 10 2020 10:57 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Nov 10 2020 11:50 AM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | 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 |
Tue, Nov 10 2020 12:55 PM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | 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 |
Wed, Nov 11 2020 3:03 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jose
Why not just put the code from the procedure into the script? Roy Lambert |
Wed, Nov 11 2020 4:22 AM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | 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 |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |