Creation on read only user.

Hello, 1. I have 2 user in my instance i.e. 'USER1' and 'USER2'. 2. 'USER1' and 'USER2' can Execute SELECT, INSERT, UPDATE, and DELETE statements as they gave got 'CONNECT' database level privilege, that comes with default 'PUBLIC' role. 3. I want to create 'USER3' that should be READONLY user. How this can be achieved without affecting ‘USER1’ and ‘USER2’ Please help with this issue. Thanks in advance.

I think there is a slight bit of confusion in your question about how the privileges work: "'USER1' and 'USER2' can Execute SELECT, INSERT, UPDATE, and DELETE statements as they gave got 'CONNECT' database level privilege, that comes with default 'PUBLIC' role." 1. By default databases (at least non-ANSI ones) have GRANT CONNECT TO PUBLIC: this allows creation of a session plus select access on some/most system tables with id numbers < 100, e.g. queries like "select tabname from systables;". 2. For each individual table the engine will automatically grant SELECT, INSERT, UPDATE, and DELETE privileges to PUBLIC if NODEFDAC was not set in the environment of the user who created the table at the time it was created. While these allow someone to get started quickly without concerning themselves with granting privileges to users or groups, it does mean that if you began this way you're going to have to unpick it to implement something more secure. I recommend using NODEFDAC to prevent automatic granting of privileges but it is an environment variable and not a server parameter so you need complete control of your environment. This aside, to create your read-only user without affecting the other two users you're going to have to revoke at least the privileges granted to PUBLIC you don't want everyone to have and replace them with something else more targetted. One way of doing it would be to create a role, grant the privileges to this role and then make the role the default role for the users. Revoke the privileges from public the role provides. Ben.