Roles

Before I talk about roles, there is a few points that I want the readers to understand before I begin.

All Oracle objects (e.g.tables, indexes, etc) are owned by users who created them.
- only the user who created a table will be able to see its data.To allow other to see rows, the owner/user must grant access privileges.
- Oracle has system privileges that allow global access rights.
- Oracle has object privileges that allow grants to specific tables.

Any big organization will face difficulties to manage their database users in the form of managing their system and objects privileges.

In these companies staffs come and go plus they may have selected privileges on many objects.
Roles will be most helpful to manage this problem.

A role is a set or group of privileges that can be granted to users or roles. This is a great way for DBA to save time and effort.

To be able to create a role, you must have at least the CREATE ROLE system privileges.

The syntax for creating a role is:
CREATE ROLE role_name[ NOT IDENTIFIED IDENTIFIED {BY password USING [schema.] package EXTERNALLY GLOBALLY } ;

If both the NOT IDENTIFIED and IDENTIFIED phrases are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.

The role_name phrase is the name of the new role that you are creating.

The NOT IDENTIFIED phrase means that the role is immediately enabled. No password is required to enable the role.

The IDENTIFIED phrase means that a user must be authorized by a specified method before the role is enabled.

The BY password phrase means that a user must supply a password to enable the role.

The USING package phrase means that you are creating an application role - a role that is enabled only by applications using an authorized package.

The EXTERNALLY phrase means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.

The GLOBALLY phrase means that a user must be authorized by the enterprise directory service to enable the role.


Example:
CREATE ROLE abc;
This 1st example creates a role called abc.

CREATE ROLE abcIDENTIFIED BY abc123;
This 2nd example creates the same role called abc, but now it is password protected with the password of abc123.

The syntax for granting privileges on a table is:
grant privileges on object to role_name

For example, if you wanted to grant select, insert, update, and delete privileges on a table called emp to a role named abc, you would execute the following statement:

grant select, insert, update, delete on emp to abc;

You can also use the all keyword to indicate that you wish all permissions to be granted.

For example:
grant all on emp to abc;

Revoke Privileges (on Tables) to Roles.Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.

The syntax for revoking privileges on a table is:
revoke privileges on object from role_name;

For example, if you wanted to revoke delete privileges on a table called emp from a role named abc, you would execute the following statement:
revoke delete on emp from abc;

If you wanted to revoke all privileges on a table, you could use the all keyword.
For example:
revoke all on emp from abc;

Granting the Role to a User
GRANT role_name TO user_name;

For example:
GRANT abc to bakar;
This example would grant the role called abc to the user named bakar.

The user bakar will automatically gets all the privileges that was in the role abc

The SET ROLE statement allows you to enable or disable a role for a current session.

When a user logs into the database, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.

The syntax for the SET ROLE statement is:
SET ROLE( role_name [ IDENTIFIED BY password ] ALL [EXCEPT roleA, roleB, ... ] NONE );
The role_name phrase is the name of the role that you wish to enable.
The IDENTIFIED BY password phrase is the password for the role to enable it. If the role does not have a password, this phrase can be omitted.
The ALL phrase means that all roles should be enabled for this current session, except those listed in the EXCEPT phrase.
The NONE phrase disables all roles for the current session. (including all default roles)

Example
SET ROLE abc IDENTIFIED BY abc123;
This example would enable the role called abc with a password of abc123.

A default role means that the role is always enabled for the current session when the user logs on. It is not necessary to issue the SET ROLE statement. To set a role as a DEFAULT role, you need to issue the ALTER USER statement.

The syntax for setting a role as a DEFAULT role is:
ALTER USER user_nameDEFAULT ROLE( role_name ALL [EXCEPT roleA, roleB, ... ] NONE );

The user_name phrase is the name of the user whose role you are setting as DEFAULT.
The role_name phrase is the name of the role that you wish to set as DEFAULT.
The ALL phrase means that all roles should be enabled as DEFAULT, except those listed in the EXCEPT phrase.
The NONE phrase disables all roles as DEFAULT.

Example:
ALTER USER bakarDEFAULT ROLE abc;
This example would set the role called abc as a DEFAULT role for the user called bakar.

ALTER USER bakar DEFAULT ROLE ALL;
This example would set all roles assigned to bakar as DEFAULT.

ALTER USER bakarDEFAULT ROLEALL EXCEPT abc;
This example would set all roles assigned to bakar as DEFAULT, except for the role called abc.

How to be a successful Oracle Database Professional

I read this article in a blog from a very famous Oracle Guru, Tom Kyte (http://tkyte.blogspot.com)

I thought of sharing this with you all of what he thinks to become a successful Oracle Database Professional.


Ways to go to become a successful Oracle Database Professional

1. Be involved, participate. I cannot stress that one enough.

2. Understand. It is not good enough to think you know how something should work according to you. You have to understand how it actually does work. What you believe to be the way something is implemented could be far from the truth as to how it actually is implemented.

3.Ask why whenever you don't know why. We could just shorten that to "ask". If you don't ask, you'll never know. If you are answering questions and don't ask some yourself - you are doing it wrong.

4.Trust but Question Authority. If you see something written as a statement of fact without any supporting evidence either ignore it entirely or if you feel it could be beneficial to you if true develop a method to test the concept and see if it holds true.

5.If you want to be successful, you'll be communicating. Be precise in that communication. It really does make a difference. Clarity is necessary and preciseness is mandatory for clarity.

6.When you participate and start giving your own answers, your own input - do it right


I hope this sayings from Tom Kyte motivates you all.

orakill on windows

This utility is provided only with Oracle databases on WINDOWS platforms.Ths executable allows a DBA to kill sessions in Oracle withuot connecting to the database.That is you can do it on DOS command prompt.(orakill.exe)

Unix system are processes based while windows environment are thread based.
In Unix, every oracle sessions can be seen individually by using the ps comand but in windows
based system all sessions are in the oracle.exe executables.That means you won't be able to see
sessions individually.

The orakill will do the same thing in windows what kill -9 does in UNIX.
C:\oracle>orakill
Usage: orakill sid thread
where sid = the Oracle instance to target
thread = the thread id of the thread to kill

The thread id should be retrieved from the spid column of a query such as:

select spid, osuser, s.program
from v$process p, v$session s
where p.addr=s.paddr;

to get a better view, you can user the below query to replace the above.

select b.spid, a.osuser, a.username
from v$process b, v$session a
where b.addr=a.paddr
and a.username is not null;
e.g
SPID OSUSER USERNAME
------------ ------------------------------ -------------------
2508 MURALI\innotiive PRASANA
2644 MURALI\innotiive ALI

C:\oracle>orakill PROD 2644
Kill of thread id 2644 in instance PROD successfully signalled.

SQL>select b.spid, a.osuser, a.username from
v$process b, v$session a where b.addr=a.paddr
and a.username is not null;
SPID OSUSER USERNAME
------------ ------------------------------ --------------
2508 MURALI\innotiive PRASANA

You can also kill a user session from the Oracle databasee.g.alter system kil session (sid,serial#);

You can get the value of sid and serial# from the v$session view.
One of the reason to use orakill instead of alter system kill session is orakill will clear
any locks that exist but alter system kill session will remain connected until it times out and
then it release the locks.

The orakill utility should be used as a last resort only. If the session cannot be killed more
gracefully (via alter system kill session), or the instance is inaccessible via SQL, then
orakill should be used to terminate the offending session.

Be careful not to kill a background process as this may cause the database to be down.