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.