SQL Server and Oracle read / write roles

Question

Hi, I need to create some roles (I don't know how to call it correctly, so I'll name it 'role :)) on SQL Server and Oracle, and I really don't know much about both DB servers, so I don't know what I should look for.

I need to create 2 roles:

• users who can only filter data from tables (SELECT).
• users who can modify data (i.e. DELETE statement), but can't modify schema (adding/deleting tables in DB)

I also need help, how to use those roles from .Net.

Can you help me, and give me some clues what I should look for ? Or maby you have a ready solution. I really appreciate any help. TIA.

Answer 1

I can't speak to Oracle as I have never used it, but in SQL Server there are 2 built in roles that do exactly that: db_datareader and db_datawriter. Please note these are separate so to read and write you will need both roles. If you want to use these roles in a .net application, then the easiest way is to use integrated windows authentication in your connection string then add those roles to the windows user for the relevant database in SQL management studio.

Answer 2

Short answer: MSSQL has built-in roles called db_datareader and db_datawriter that are close to what you want. Oracle does not (as far as I know, I may be wrong), so you would have have to define your own.

Long answer: permissions in MSSQL and Oracle can be complex, especially since they have quite different concepts of logins / users / databases / schemas etc. You really need to invest the time to understand them in order to manage permissions effectively, or the chances are good that you will do something you shouldn't. If you don't have the time yourself, then consider finding someone who does.

You could also manage permissions at the application level, but that doesn't help much because you still need to know how to manage the account that your application uses to connect to the database and avoid issues there.

Answer 3

Oracle doesn't come with an equivalent of the SQL Server roles. This is because it encourages adherence to the principle of Least Privilege. However, it is perfectly possible to come up with our own.

create role db_datareader 
/
grant select any table to db_datareader 
/

Similarly we can knock up an equivalent db_datawriter by granting the various DML ANY privileges to the role. As a rule I would advise against this. In most cases only a DBA needs to have the ANY level of privilege, and there is already a DBA role for them.