Database Security? I'm not sure what are the question marks are lately...I can't think of better titles so perhaps it's me guessing at the title.

I'm sadly dependent on SQL*Plus...and when I mean "sadly" I mean I go to great lengths to get the Windows version which was obsolesced (sp?) in 11gR1. I could never get it quite right, the output I mean, but it was just so easy. Type in your SELECT statement, hit Enter and go. Easy. Yes, yes, I know, you can use the shell version too...I just liked the Windows version despite all it's quirks.

Anyway, as part of my login.sql script, I have the following 2 queries:

prompt ***roles granted***
SELECT role FROM session_roles;
prompt
prompt ***system privileges granted***
SELECT privilege 
FROM user_sys_privs
UNION
SELECT privilege
FROM role_sys_privs
WHERE role IN ( SELECT role FROM session_roles );

Each time I login or connect to a different database, this script runs. The results of those 2 queries, the first shows the roles I have been granted and the second query returns the system privileges I (or one of my granted roles) have. I'm sure I got this from Tom Kyte...I've been using this for years and I can't remember when or where I began to use it.

Having changed jobs quite a few times over the past few years, I've gotten to see many different environments. These scripts give me a quick glance at what I am able to do in a particular database, which is very helpful.

Where am I going? Not really sure.

I know that system level privileges are usually a no-no. CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, those are the basic items that you need to build objects in your own schema. CREATE ANY TABLE, CREATE ANY VIEW and CREATE ANY PROCEDURE will allow you create said objects in any schema (except SYS...I think, I don't have my power cable thus I can't start up my vm which contains my database).

Having read AskTom for so many years, I know or understand that system level privileges are bad, very bad.

At one former job, in the production database, not only did I have CREATE ANY TRIGGER, I had EXECUTE ANY PROCEDURE. I won't go into why or how those are very, very bad, you can research those yourself. Or you can just buy David Litchfield's The Oracle Hacker's Handbook: Hacking and Defending Oracle.

Pointing those types of things out though can be tricky...to put it mildly. Lots of politics involved. Trust me on this.

Hopefully you can glean a point or at least some information from this post. My mind is not all there due to the massive brain infection I have.

Labels: dba, oracle, security

  ¶ 2/15/2010 10:16:00 PM