Table Creation Examples

Here is a simple example of the SQL used to create a couple tables, populate them, create a view, and grant access to view them.

Create the two tables:

create table name
(
id number not null,
first_name varchar2(30),
last_name varchar2(30) not null,
constraint pk_name primary key (id)
);

create table office_location
(
name_id number not null,
room_number varchar2(6 byte),
building varchar2(20 byte),
constraint person_exists foreign key(name_id)
references name,
constraint room_number_check check (room_number between 0 and 799)
);

Insert some values into each:

insert into name (id, first_name, last_name)
values (1, 'Jon', 'Emmons');

insert into name (id, first_name, last_name)
values (2, 'Matt', 'Batchelder');

insert into name (id, first_name, last_name)
values (3, 'Zach', 'Tirrell');

insert into name (id, first_name, last_name)
values (4, 'Jon', 'Graton');

insert into office_location (name_id, room_number, building)
values (1, '334', 'Hyde');

insert into office_location (name_id, room_number, building)
values (2, '334', 'Hyde');

insert into office_location (name_id, room_number, building)
values (3, '222', 'Hyde');

insert into office_location (name_id, room_number, building)
values (4, '333', 'Hyde');

Extract some meaningful data with a simple join:

select first_name, last_name, building, room_number
from name, office_location
where id=name_id;

Create a view based on that join:

create view name_office as
(
select first_name, last_name, building, room_number
from name, office_location
where id=name_id
);

Select from our new view:

select * from name_office;

Grant access to jemmons to the tables and view (note that you do not have to grant access to the underlying tables for a user to be able to select from the view):

grant select on name to jemmons;

grant select on office_location to jemmons;

grant select on name_office to jemmons;

Technorati tags: , , ,

Useful Oracle Security Views

One of the cool things about oracle is that if you have the right privileges you can learn all it’s secrets from the so-called “data dictionary.”

If you’re interested in this then you probably already know what that’s about, so here are what I consider the important data dictionary views for Oracle security:

DBA_SYS_PRIVS – Probably most important, this show system privileges granted to users and roles

DBA_TAB_PRIVS – While the name implies table privileges this actually incorporates view privileges, procedures, packages and functions

DBA_COL_PRIVS – Not always used, but this view tracks grants on columns

DBA_ROLE_PRIVS – This shows who (or what roles) a role has been granted to

These views don’t seem to have changed much from 9i to 10g (or in 8i at that matter) but it is always best to check documentation for your version of Oracle.

Stay tuned for some handy queries to run on these to get quick reports on who can see what.

Technorati tags: , , , , , ,

Oracle CONNECT and RESOURCE Roles

So what exactly is in those CONNECT and RESOURCE Oracle roles? The ship with every Oracle database and many apps require they be granted. I did a little digging and found the following:

In Oracle 10gR2 things are fairly sane:
CONNECT role has only CREATE SESSION
RESOURCE has CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER and CREATE TYPE

In Oracle 9iR2 things get a little scary:
CONNECT has ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE and CREATE VIEW. Rather a scary lot for a role called ‘connect’
RESOURCE has CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER and CREATE TYPE

The admin option would allow the users to grant the privlelge to another user. Thankfully neither of these roles have the admin option in the versions of Oracle I checked.

To find these privileges you can query the DBA_SYS_PRIVS table with a query like this:

select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT';

Of course an oracle role could also have table or column privileges granted to it, so to be thorough you should also check for entries in DBA_TAB_PRIVS and DBA_COL_PRIVS.

NOTE: You should always check these privileges in your database before granting roles as a script, application or previous DBA may have granted or revoked additional roles.

Technorati tags: , , , , , ,

Oracle Change Tables – An Example

Here is an example of how to create and use Oracle Change tables. This is part of what Oracle refers to as “change data capture” or CDC.

Oracle change tables allow you to capture what has changed in a specific table over a period of time. This can be useful if you are attempting to keep data updated with a source. Typically CDC is used as part of the “extract” process in an extract-transform-load procedure for loading a data warehouse.

There are many sties and articles on this topic that go into much more depth than I will here, however I have never found a fully functional demonstration of an entire CDC cycle.

Change table example code

For more information on change data capture I recommend Oracle’s Data Warehousing Guide. More on data warehousing in general can be found at Mark Rittman’s Oracle Weblog.

A new feature in Oracle 10g called ‘streams’ offers an alternative to this type of CDC, however I (and I’m sure many others) are still dealing with change tables on a regular basis.

Technorati tags: , , , , , ,

Oracle Password Hacks

As a DBA it is often useful to change a user’s database password for testing, but what if you don’t know the user’s original password so you can change it back when you’re done?

There is no easy way to decipher the encrypted password, but you can view it. What you can do is copy the user’s encrypted password, change the password to a known value for testing with the normal alter user command, then replace the original password with a special alter user command.

In my case I am actually using this to synchronize passwords between two databases to assure database links work properly. This will only work if the usernames are identical.

Here is an example of how I am using this technique to synchronize passwords:

First we want to set up a user with a known password

In the original database:

SYS:TEST> alter user jemmons identified by copyme;

User altered.

SYS:TEST> select username, password from dba_users where username='JEMMONS';

USERNAME PASSWORD
------------------------------ ------------------------------
JEMMONS EAEC44107194EBC6

Now we connect up to the database we want to clone the password to. Note the first attempt to connect as jemmons fails as that is not the assigned password.

In the database you want to copy the password to:

nolog> conn jemmons/copyme;
ERROR:
ORA-01017: invalid username/password; logon denied

nolog> conn / as sysdba
Connected.
nolog> alter user jemmons identified by values 'EAEC44107194EBC6';

User altered.

Elapsed: 00:00:00.01
nolog> conn jemmons/copyme;
Connected.

Now we see that this only works if the usernames are identical. This is because the hashed password is based on a combination of the password provided and the username.


nolog> conn / as sysdba
Connected.
nolog> alter user ken identified by values 'EAEC44107194EBC6';

User altered.

nolog> conn ken/copyme;
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

If you want to know more on this, check out article from red-database-security.com

Note: This was done on a 9i database. This may or may not work across versions.

Technorati tags: , , , ,