Sunday, July 29, 2007

Oracle for Beginner: Fine Grained Access (FGA)

SQL> conn / as sydba
SQL> alter system set Event="28131 trace name context forever" scope=spfile;
SQL> shutdown immediate;
SQL> startup
SQL> conn / as sysdba
SQL> show parameter event;
SQL> create user test identified by oracle;
SQL> grant connect, resource, create any context, administer database trigger to test;
SQL> grant execute on dbms_rls to test;
SQL> conn test/oracle
SQL> create table manager(dep_id number(3) primary key, nama_manajer varchar(30));
SQL> create table karyawan(dep_id number(3), nama_kary varchar(30), primary key(dep_id, nama_kary) );
SQL> insert into manager values (10, 'BUDSUS');
SQL> insert into manager values (20, 'HR');
SQL> commit;
SQL> insert into karyawan values (10, 'ANI');
SQL> insert into karyawan values (10, 'WATI');
SQL> insert into karyawan values (20, 'ANTOK');
SQL> commit;
SQL> create table orders(id number(4) primary key, dep_id number(3), tgl date, karyawan varchar(30) default user);
SQL> insert into orders values (1, 10, '01-AUG-07', 'ANI');
SQL> insert into orders values (2, 10, '02-AUG-07', 'ANI');
SQL> insert into orders values (3, 10, '02-AUG-07', 'WATI');
SQL> insert into orders values (4, 20, '02-AUG-07', 'ANTOK');

Here is the procedure that will be called when a user login to the database:

create or replace procedure set_testapp_role(
p_user varchar2 default sys_context('userenv', 'session_user')) is

--parameter berisi username,
--jika tidak diberikan, username akan diambil dari sys_context()
--yang mengembalikan user session saat ini

--v_ctx menyimpan nama application context.
v_ctx varchar2(16) := 'testapp_ctx';

v_is_manajer number;
v_dep_id number;
v_is_kary number;

--set variabel application context "username" dengan user yang diberikan
dbms_session.set_context(v_ctx, 'username', p_user);

select count(*) into v_is_manajer from test.manajer where nama_manajer=p_user;
select count(*) into v_is_kary from test.karyawan where nama_kary=p_user;

--jika user adalah TEST,
-- maka beri role APP_OWNER.

if (p_user=sys_context('userenv','current_schema')) then
elsif (v_is_manajer=1) then
--jika user manajer, beri role APP_ADMIN dan ambil

select dep_id into v_dep_id from manajer where nama_manajer=p_user;


elsif (v_is_kary=1) then
--jika user karyawan, beri role APP_USER
--jika user tidak berwewenang
end if;

We have to create a context to save some global variables :

create or replace context testapp_ctx using set_testapp_role;

We will call set_testapp_role procedure for each time a user login to database. To make that thing can run, we have to create a trigger that will be run for each time a login happened:

create or replace trigger test_logon_trigger after logon on database

Here is a function that will return a condition string for a user session. It's mean, when a user is login to database and want to access ORDERS table, the system will make a restriction for each query from the active user:

create or replace function testapp_security_function (
p_schema varchar2, p_object varchar2) return varchar2 is
if (sys_context('testapp_ctx','rolename')='APP_OWNER') then
--tidak ada kondisi yang diberkan
return '';

elsif (sys_context('testapp_ctx','rolename')='APP_ADMIN') then
--kondisi query dibatasi untuk data-data departemen dimana
--user sekarang sebagai manajernya (APP_ADMIN)
return 'dep_id=sys_context(''testapp_ctx'',''depid'')';

elsif ( sys_context('testapp_ctx','rolename')='APP_USER') then
--untuk role APP_USER
return 'karyawan=sys_context(''testapp_ctx'',''username'') and '||
'dep_id = (select dep_id from test.karyawan ' ||
'where nama_kary=sys_context(''testapp_ctx'',''username''))';

return '1=2';
end if;

Then, we add a new security policy to implement FGA :

object_schema => 'TEST',
object_name => 'ORDERS', policy_name => 'TESTAPP_POLICY',
function_schema => 'TEST',
policy_function => 'TESTAPP_SECURITY_FUNCTION',
statement_types => 'SELECT,UPDATE,INSERT,DELETE',
update_check => TRUE,
enable => TRUE,
static_policy => FALSE);

Let's try our FGA example:

SQL> conn / as sysdba
SQL> create user budsus identified by oracle;
SQL> grant connect to budsus;
SQL> create user ani identified by oracle;
SQL> grant connect to ani;
SQL> conn test/oracle
SQL> grant select, update, delete, insert on orders to budsus;
SQL> grant select, update, delete, insert on orders to ani;

SQL> conn ani/oracle
SQL> select * from test.orders;

---------- ---------- --------- ------------------------------
1 10 01-AUG-07 ANI
2 10 02-AUG-07 ANI

SQL> insert into test.orders values (10,20,'03-AUG-07','ANI');
insert into test.orders values (10,20,'03-AUG-07','ANI')
ERROR at line 1:
ORA-28115: policy with check option violation
SQL> insert into test.orders values (10,10,'03-AUG-07','ANI');

1 row created.

SQL> conn budsus/oracle
SQL> select * from test.orders;

---------- ---------- --------- ------------------------------
1 10 01-AUG-07 ANI
2 10 02-AUG-07 ANI
3 10 02-AUG-07 WATI
10 10 03-AUG-07 ANI

Thursday, July 26, 2007

Oracle for Beginner: 'Snapshot too old' a simple practice

In Oracle, there is a undo tablespace (is controlled by undo_tablespace parameter) that is used by oracle to store old image of updated rows. With this tablespace, you can rollback to cancel the transaction. In a undo tablespace, there are some undo segments that is controlled by oracle automatically (it is recommended that undo_management init parameter has value AUTO). For each old row images will be retained at undo segment for 900 seconds (undo_retention parameter), after that the block for old images can be overwritten by another old row images. If the undo tablespace is too small to retain old row images, sometime you will get an error that will tell you about there is no enough space for undo data. Here is an example of that error:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS_2'

The "snapshot too old" error can be happen if some old row images, that is used by another query for read consistency, have been overwritten. In this article, from another resources too, I would like to share about a simple practice to help us to understand about this error.

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> create undo tablespace undotbs_2 datafile size 1M;

Tablespace created.

SQL> alter system set undo_tablespace='UNDOTBS_2';

System altered.

SQL> create user budsus identified by oracle quota 10M on users;
SQL> grant connect, create table, create any index to budsus;

Grant succeeded.
SQL> conn budsus/oracle
SQL> create table siswa (id number(5), nama varchar(100));

SQL> create table dump(tmp varchar(100));

SQL> begin
2 for i in 1..5000 loop
3 insert into siswa values (i, 'siswa terbaru ' || i);
4 if mod(i, 100) = 0 then
5 insert into dump values ('teeeeeeeemmmmmmmmmppppppppp');
6 commit;
7 end if;
8 end loop;
9 commit;
10 end;

SQL> declare
2 cursor crs_siswa is select rowid, siswa.* from siswa where id < 250;
3 begin
4 for v_siswa in crs_siswa loop
5 update dump set tmp = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
6 update dump set tmp = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbb';
7 update dump set tmp = 'ccccccccccccccccccccccccccccc';
8 update siswa set nama = 'siswa terupdate ' || where v_siswa.rowid = rowid;
9 commit;
10 end loop;
11 end;
12 /
ORA-01555: snapshot too old: rollback segment number 15 with name "_SYSSMU15$" too small

SQL> conn / as sysdba;
SQL> alter system set undo_tablespace='UNDOTBS1';
SQL> drop tablespace undotbs_2;
SQL> drop table siswa;
SQL> drop table dump;
SQL> quit


Wednesday, July 25, 2007

Language Translator Application

Altough this application is the old one (now with a good service), but this very helpful for me. I use gnome-translate that is created by Jean-Yves Lefort. Actually I use this application for make translation for a site from one language to other language. Sometime I use this to translate from English-Indonesia vice versa. Unfortunately, to use this application, we should have a Internet connection directly without proxy (based on my experience). Please visit GNOME Translate web site to get more information and more application from the Author.

Monday, July 23, 2007

Oracle for Beginner: Deferrable Constraint

In this article I describe a simple example of deferrable constraint. With this feature, we can make the validation of the constraint process deferred until a commit is done. Sometime we need this condition when we try to load data from external file to our table.

SQL> create table testing(nilai number(5) check ( nilai in (10, 20, 30) ) deferrable initially deferred );
SQL> alter session set constraint = deferred;
SQL> insert into testing values(100);
(there are no errors! why?)
SQL> insert into testing values(20);
SQL> commit;
(there is an error, because there is data 100 that is not invalid for our check constraint)
SQL> select * from testing;
SQL> alter session set constraint = immediate;
SQL> insert into testing values (100);
(immediately, oracle will check the new data with check constraint)
SQL> insert into testing values (10);
SQL> commit;

There is another ways to manage a constraint. We can change the status of constraint. There are for kind of status: enable (validate), enable novalidate, disable validate, disable (novalidate). In this moment, we will try to change status of our constraint in table TESTING:

SQL> desc user_constraints;
SQL> select constraint_name from user_constraints where table_name = 'TESTING';
(with this query, you can obtain the constraint name for TESTING table. Please note that constraint_name)
SQL> alter table testing modify constraint const_name disable;
SQL> insert into testing values(50);
(there are no problems, because constraint is disabled)
SQL> insert into testing values(30);
SQL> commit;
SQL> alter table testing modify constraint const_name enable novalidate;
(what happen? why? )
SQL> insert into testing values (50);
(why oracle give you an error?)
SQL> insert into testing values(20);
SQL> commit;

That's all for today. Thanks.

Sunday, July 22, 2007

Oracle for Beginner: Password Resource Management

Everytime a user account was created, Oracle will assign a profile for that user. The default profile is 'DEFAULT' if for that user was not specified the profile when was created. There are two kind of resouces that can be managed in a profile: Kernel Resouce and Password resource. Lets we try to do a simple exercise to manage password resource:

oracle$ sqlplus /nolog
SQL> conn / as sysdba
SQL> select profile from dba_profiles;
(you will see all of profile objects that have been created. There are two default profile objects, i.e. DEFAULT and MONITORING_PROFILE).
SQL> select * from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';

-------------------- ------------------------- -------- --------------------

(with this query, we can obtain all of password resource parameter for profile DEFAULT)
SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
(this is a build-in script that will change password resource parameters in DEFAULT profile to make some tight restriction for password management. utlpwdmg.sql script will create a new stored function which is called verify_function, and then alter the DEFAULT profile)
SQL> select * from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';

-------------------- ------------------------- -------- --------------------

Now, the DEFAULT profile was changed. Try some practice below:
1. try to create a new user account.

SQL> conn / as sysdba
SQL> create user myuser identified by oracle;
(because the password_verify_function has been changed with verify_function, evertime new password want to save to oracle should consider some rules that already have defined by verify_function.)
SQL> create user myuser identified by oracle_2007;
SQL> grant connect to myuser;

2. try to login with invalid password for 3 times, and then login with valid password for last time:

SQL> conn myuser/gggg
SQL> conn myuser/rrrr
SQL> conn myuser/ttttt
SQL> conn myuser/oracle_2007
ORA-28000: the account is locked

(what happen? yes, your account is locked, because we have failed to login for 3 times (FAILED_LOGIN_ATTEMPTS). Now, we should wait for about 1 minute (1/1440) which is 1440 is number of minutes in a day, see PASSWORD_LOCK_TIME).
(after 1 minute, try to login again with correct password)
SQL> conn myuser/oracle_2007

3. The parameter PASSWORD_LIFE_TIME is to determine the age of your password (in days). After the (PASSWORD_LIFE_TIME + 1)th day, you have PASSWORD_GRACE_TIME days to retain your old password. Now, we will try to change both parameters for short time, so you can try it immediately.
SQL> conn / as sysdba
SQL> alter profile default limit
SQL> conn myuser/oracle_2007
(please wait for about 5 minutes)
SQL> conn myuser/oracle_2007
ORA-28002: the password will expire within 0 days

(please wait for about 5 minutes again)
SQL> conn myuser/oracle_2007
ORA-28001: the password has expired

Changing password for testing
New password:

(please give your new password with minimum 3 new character that different from the old one)
SQL> conn / as sysdba
SQL> alter profile default limit

4. Between parameter PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX should be choosen only one. It's mean, if we mention a value for one parameter, the second parameter should have UNLIMITED value. Both parameters are used to determine how many times old passwords can be reuse again.

Thursday, July 19, 2007

Oracle for Beginner: Creating User Account

In Oracle, we can create an user account easily. Here are some examples that will show you how to create a new user account and grant some privileges to that user (as dba user):

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> create user xxx identified by password_xxx;
SQL> grant connect to xxx ;
SQL> quit

(now, you can try to login as user xxx)
$ sqlplus xxx/password_xxx
(done user xxx login in! but you can't do anything except querying some dictionary view that already grant to you, like v$version, user_tables, user_indexes, etc.)
SQL> select * from v$version;
SQL> select table_name from user_tables;
(you also can change your password)
SQL> alter user xxx identified by oracle;
(but you can't do DDL or DML operation)
SQL> create table test(a number);
SQL> exit

We will do a simple practice to make our user xxx can do DDL or DML operation on tablespace "operations" with space limitation . To do this, you should login again as DBA:

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> create tablespace operations datafile size 5M;
SQL> alter user xxx default tablespace operations quota 2M on operations password expire;
(we want to change default tablespace from "users" to "operations" and make a space limitation for user xxx only for 2M. Option "password expire" is an option to force user xxx to change the password. Try to login as user xxx again)
SQL> grant create table, create any index, create sequence to xxx;
(as a dba, we grant some privileges for user xxx, so he/she can DDL and DML toward their own objects)
SQL> conn xxx/oracle
ORA-28001: the password has expired

Changing password for xxx
New password:
Retype new password:
Password changed

SQL> create sequence seq_test;
SQL> create table test(id number(4) primary key, data varchar(40));
SQL> insert into test values(, 'test one');
SQL> commit;
SQL> select * from test;
SQL> drop table test purge;
SQL> exit

In unix/linux environment, if you want unix/linux user account can login to oracle which is the instance oracle is also running on the same machine, you can try with these a simple practice:

$ su -
# adduser testing
# passwd testing
# exit

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> show parameter os_authent_prefix;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$

SQL> create user ops$testing identified by externally;
SQL> grant connect, resource to ops$testing;
SQL> quit
$ su - testing
$ sqlplus /
SQL> show user;
(now you can login to your oracle with operating system user account)

Tuesday, July 17, 2007

Oracle for Beginner: Securing Roles

From this article, I want to share some commands that I have knew about Oracle10g. At this point, we will learn about how to make secure our roles, so every user that want to activate that roles should identifying with the password for each role. Here is an example to realize that:

$ sqlplus / as sysdba
SQL> create user test1 identified by oracle;
SQL> grant connect, create table to test1;
SQL> conn test1/oracle
SQL> create table test(name varchar(20));
SQL> conn / as sysdba
SQL> create role test_mgr identified by oracle;
SQL> grant insert, update, delete on test1.test to test_mgr;
SQL> create role test_qry;
SQL> grant select on test1.test to test_qry;
SQL> grant test_qry, test_mgr to budsus;
SQL> alter user budsus default role all except test_mgr;
SQL> conn budsus/oracle
SQL> select * from test1.test;
(no problem with this command, because you have test_qry role)
SQL> insert into test1.test values ('Testing 1');
(this command will generate an error, because your test_mgr is not default for your user account. In this context, you have to activate it first)
SQL> set role test_mgr identified by oracle;
(done! now you have object privileges for test1.test)
SQL> insert into test1.test values ('Testing 1');
(no problem)
SQL> commit;
SQL> select * from test1.test;
SQL> quit

Now, you can make secure for any roles that you want.

Friday, July 13, 2007

Setup ASM with Loopback device on FC6

First, this document is very helpfull for me:
Here are some steps that I was doing to try ASM on my notebook with Oracle10g R 2 on FC 6:
  • I create a disk image for 500M with this command (as root):
dd if=/dev/zero of=/disk2.img bs=516096c count=1000
dd if=/dev/zero of=/disk3.img bs=516096c count=1000

  • then i mount those disk image as local device, so that file will be threaded as phisycal disk
losetup /dev/loop0 /disk2.img
losetup /dev/loop1 /disk3.img

Note: if you want to detach loopback device from disk image, you can use this command:
losetup -d /dev/loop?
  • We should configure and run CSS (Cluster Synchronization Service) before configure ASM
$ORACLE_HOME/bin/localconfig add

that command will create init.cssd service in /etc/init.d and /etc/oracle directory store some information and location to store any information from cssd service.
You can call CSS service with this command:

/etc/init.d/init.cssd start
  • download the latest ASMlib source from OracleASM project. (please read some discussion at this). In that directory, there is no configure script, so I download ASMLib kernel module (oracleasm-2.0.3.tar.gz) and use configure script from that. The compilation is success. After make install, we will get /etc/init.d/oracleasm service script.
  • now, I have to make oracleasm kernel module is launched safely.
/etc/init.d/oracleasm configure
Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [y]: n
Fix permissions of Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: [ OK ]
Unmounting ASMlib driver filesystem: [ OK ]
  • in my case, kernel module oracleasm can't be found by modprobe -r oracleasm, so, I try to call with
insmod /lib/module/{kernel_version}/extra/oracleasm.ko

I changed modprobe -s "$MODNAME" command with insmod command like above.
  • To make our local loopback disk become ASM disk, I have to make ASM disk with /etc/init.d/oracleasm createdisk command :

/etc/init.d/oracleasm createdisk ASMD0 /dev/loop0
/etc/init.d/oracleasm createdisk ASMD1 /dev/loop1

With those command, there are trow ASMD* disk that will create in my /dev/oracleasm/disks. I can check with this command:
/etc/init.d/oracleasm listdisks

  • Based on blog, I should prepare ASM instance with these commands:
as oracle user, I create /tmp/init+ASM.ora that have some parameters declaration like these:


$ export ORACLE_SID=+ASM
$ sqlplus / as sysdba
SQL> create spfile from pfile='/tmp/init+ASM.ora';
SQL> startup nomount;
SQL> alter system set ASM_DISKSTRING=''/dev/oracleasm/disks/*';
SQL> shutdown;

  • now, I will configure ASM with dbca. I choose Configure Automatic Storage Management, and give the group disk with DGROUP1 and choose all of ASMD* as the members.

  • Now, with dbca I can create a new database which is using ASM as the database storage.
This is my first experience using ASM with loopback device as 'phisical' storage for Oracle. I still need to learn more about Oracle RAC. I have blogroll that may be can help you too to learn about OracleRAC. If you have any other great tutorial about this, please share with me. Thanks.

Wednesday, July 11, 2007

leech with wget

This afternoon, I tried to make duplication from a http server to my external harddrive using wget. I want to make a note at my blog, so everytime when I forget about the command, this note can help me. Here is my sintax for wget to get all of contents from a directory in a server recursively, including the structure of directories.

wget -rv -l 10 -t0 -I start_of_remote_directory http://host_name

Option -r is used to download all of content recursively. If you want to make more deep for the level of directory, you can specify in the -l option.
There are many ways to do like that, one project that may be appropriate for you is Leech based on PHP. I was used this tools, but I change it only with wget.
In another case, if you have a html file that contain all links that you want to download, you can use this command:

wget -rv --user-agent="you user agent" -i file.html -F

In some cases, some web servers has configured to received only known user agent. For this condition, you can pass most famous user agent, like : "Mozilla/5.0 (X11; U; Linux i686; en-US; rv: Gecko/20070510 Fedora/ Firefox/".

Saturday, July 07, 2007

Windows XP Pro on VMWare Player

I tried to install VMWare Player on my FC6 with kernel 2.6.20-1.2962.fc6. After I regitered, I downloaded the file, and installed with this command :

rpm -ivh VMware-player-2.0.0-45731.i386.rpm

after that, I tried to run as root. But I got some big errors. Based on those errors, I have to install kernel source development. The important think to install kernel source is the compatibility of machine should be same with the kernel. You can query with rpm command like this :

rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}.%{ARCH}\n" | grep kernel

I got the package name of kernel : kernel-2.6.20-1.2962.fc6.i586. With this information, I should download kernel-devel-2.6.20-1.2962.fc6.i586. After this installation, I can run successfully.

After this installation, I have tried to run minix and haiku with VMWare Player. You can download those vmdk and vmx from Virtual Appliance (

To get more experiences for using VMWare player, I tried to install Windows XP Prof SP2 in VMWare Player too. These sites are very helpfull for me : I use image file 20 GB (store at my ntfs partition) and modify template-windows.vmx (is saved on my ext3 home) like this:

displayName = "Windows"
guestOS = "winxppro"

memsize = "192"
ide0:0.fileName = "/media/BUDSUSOTHIE/winXPSP2/20GB.vmdk"
ide1:0.fileName = "auto detect"

config.version = "8"
virtualHW.version = "3"

MemAllowAutoScaleDown = "FALSE"
MemTrimRate = "-1"

uuid.location = "56 4d 07 13 a0 34 45 9d-d8 48 18 f5 99 4b 9a ef"
uuid.bios = "56 4d 07 13 a0 34 45 9d-d8 48 18 f5 99 4b 9a ef"

uuid.action = "create"
checkpoint.vmState = ""

ethernet0.present = "TRUE"
ethernet0.connectionType = "nat"
ethernet0.addressType = "generated"
ethernet0.generatedAddress = "00:0c:29:4b:9a:ef"
ethernet0.generatedAddressOffset = "0"

usb.present = "FALSE"
sound.present = "TRUE"
sound.virtualDev = "es1371"

scsi0.present = "FALSE"
scsi0:0.present = "FALSE"
scsi0:1.present = "FALSE"

floppy0.present = "FALSE"

ide0:1.present = "FALSE"
ide1:1.present = "FALSE"

ide0:0.present = "TRUE"
ide0:0.deviceType = "disk"
ide0:0.startConnected = "TRUE"

ide1:0.present = "TRUE"
ide1:0.deviceType = "cdrom-raw"
ide1:0.autodetect = "TRUE"
ide1:0.startConnected = "TRUE"

ide0:0.redo = ""

extendedConfigFile = "template-windows.vmxf"
virtualHW.productCompatibility = "hosted"
tools.upgrade.policy = "manual" = "TRUE"

usb.autoConnect.device0 = ""

Here are some snapshot of my vmware player window for Windows XP setup and Haiku :

I've tried to run ReactOS image with QEMU. Here are some "little" steps that I do :
  • run echo 1024 > /proc/sys/dev/rtc/max-user-freq as root
  • in my machine, qemu was told me to have 256 MB minimum for /dev/shm, because i have only 248 MB. QEMU told me to do this:
    • umount /dev/shm
    • mount -t tmpfs -o size=272m none /dev/shm
  • run qemu with this command:
qemu -serial stdio -boot c -hda /media/BUDSUSOTHIE/ReactOS-0.3.1/ReactOS-0.3.1.img -m 256 -localtime

Here is a snapshot for running ReactOS under QEMU :

This is one of good tutorial about VMware Player.