What privileges should you give your users?
The generally accepted answer to that question is: "as few as possible," while still enabling them to do their work.
To end-users, who only connect when running an application, I generally give the
CREATE SESSION
privilege, whatever object privileges and roles are required by the application, and nothing more.
That allows them to log in and use their application, and that's all you want them to be able to do.
Developers often require the ability to create objects. I usually end up granting them the following privileges:
CREATE SESSION
CREATE TABLE
CREATE DATABASE LINK
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE VIEW
CREATE SYNONYM
ALTER SESSION
These privileges allow developers to connect, and to create objects such as tables, views, and so forth.
Developers often need these capabilities in order to test code or to experiment. Note however, that this applies only to those databases used for development.
It is very rare for me to grant the ANY
privileges, or the specific system-wide object privileges such as
CREATE TABLESPACE
.
These represent tasks that are best left to the DBAs.