The power of Oracle CREATE SESSION privilege

May 26, 2010 by Leave a comment 

So many times I hear from database developers that their application has no rights on an Oracle database server. This always surprises me since it is not possible to have an Oracle user connected to the database without any rights at all. Even with a single create session privilege the user obtains the whole bunch of default rights which can not be easily revoked by a poor DBA.

Below I list the database operations an Oracle user can always perform once connected to an Oracle database

– Read data from own and public tables, views using SELECT SQL command.

– Write data in own tables, views using following SQL commands: insert, delete, update, commit, rollback (tablespace quota required for some)

– Execute own and public procedures, functions, packages using EXECUTE SQL command.

– Change, compile, debug, drop, rename own schema objects using following SQL commands – alter, drop, rename:
alter procedure test compile;
alter procedure test compile debug;
alter trigger tr disable;
drop table t1;

– Create indexes on own existing tables (tablespace quota required)

– Gather statistics, list chained rows or validate structure of own tables, indexes, clusters using ANALYZE SQL command

– Create comments on own schema objects using COMMENT SQL command.

– Grant any privilege on own schema objects using GRANT SQL command.

– Lock own tables:
lock table t in exclusive mode;

– Export objects contained in your own user’s schema using exp utility

– Generate execution plan output for SQL against own objects using EXPLAIN PLAN command

– Change own session settings:
alter session set optimizer_index_cost_adj = 100;

Perhaps I’ve even missed some… if so, … please comment below!

See below some of the most powerful packages from PUBLIC schema that every Oracle user can access

DBMS_JOBS – can submit an own Oracle job
DBMS_STATS – can produce heavy load and change own execution plans.
UTL_FILE – security issue
DBMS_RANDOM – security issue
UTL_HTTP – security issue
UTL_SMTP – security issue
UTL_TCP – security issue

Use the following SQL to find the entire list of objects you have access to
select * from all_tab_privs;

The following things would probably not work even in own schema without extra privilege granted

– import utility
– flashback
– data pump utilities
– create triggers (work on 9i- though)
– create own schema objects

The Bottom Line: I strongly suggest keeping application objects in a separate locked Oracle schema accessing them via different database users. This technique ensures the security and stability of your application by eliminating direct access to the schema objects and it’s data. In contrast, many 3d party software packages use so-called One Big Application User model where end-users directly access the Oracle schema containing database objects. This approach is probably simpler in development but increases the risk of compromising and damaging the application data.

See also a relevant article about “Oracle User Dictionary Views”

Enjoyed this article? Please share it with others using the social site of your choice:

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email