Wednesday, January 25, 2012

Difference between WITH GRANT and WITH ADMIN

Sometimes people are puzzled to find out the difference between WITH GRANT and WITH ADMIN.

Here is the simple explanation:


We can grant object privileges only with grant option like (select,update,insert)


If A want to revoke privileges from C, it is not possible. The user who granted the privilege can only revoke. we have to revoke the privilege from B ,that automatically revoke the privileges from C.


We can give the system privileges onlywith admin option(CREATE TABLE,CREATE INDEX,CREATE SESSION)


In admin option,it is possible to revoke the privileges from both B and C by A.Here suppose if we revoke the privileges from B means it didn’t revoke the privileges of C.


WITH ADMIN OPTION  When a user is granted a system privilege, the grantor (i.e., the person granting the privilege, typically the DBA) also has the option to allow the grantee (the person receiving the privilege, typically the user) to grant the same privilege to other users. If this is the result desired, the grantor can grant the privilege using the WITH ADMIN OPTION. When privileges are granted WITH ADMIN OPTION,this means that the grantor has decided that the grantee can be fully trusted by him as well as by the user that granted him the system privilege in the first place. In essence 
all users holding a system privilege

Security Hole:  WITH ADMIN OPTION are considered equal and can grant and revoke that privilege from anyone, including the person who granted it to them in the first place.

For example, if you give the key to your car to a friend and tell him that it is alright to make copies of the key, when you ask for the key back from your friend, you cannot, at the same time, get back all copies that were made by him and given to others. In order to get the other copies of the key back, you need to find out who has them. Similarly, in Oracle you need to query the data dictionary to determine which other users were granted the permission being revoked by the user from which it is being revoked.

WITH GRANT OPTION on object privileges allows a user granted 
the privilege to grant it to someone else. The reason for doing this is to minimize the administrative burden of granting object privileges.if an object privilege isrevoked from a user to whom it was granted WITH GRANT OPTION, that privilege would also be removed from anyone that user granted the privilege to. For example,if Damir granted John the SELECT privilege on the DAMIR.JOHN1 table WITH GRANT OPTION, and John then granted the SELECT privilege to Tim, then if Damir issued the command REVOKE SELECT ON DAMIR.JOHN1 FROM JOHN, Tim would also no longer have the privilege. This is because when object privileges are revoked,the revoke also cascades to anyone that the privilege was granted to by the user from whom it is being revoked.


Monday, January 16, 2012

Manually create database in Oracle 10g Linux


It is very exciting to create database manually on Linux platform.


Thursday, January 12, 2012

Who are you? Development DBA or Production DBA.

Development Oracle DBAs:

Generally speaking, Oracle Development DBAs are concerned with developing applications from a database perspective. Here are some of their tasks:
  • ·         Know the source code very well, and the database structure
  • ·         Look mainly after one or a set of applications
  • ·         Involved in SQL tuning, and re-writing of code
  • ·         Development of new functionality
  • ·         Liaise with developers regularly in development issues

Production Oracle DBAs

On the whole, the Oracle Production DBA is all about maintaining a reliable, secure and performant database. Here are some common tasks:
  • ·         Patching
  • ·         Storage and capacity planning
  • ·         Backup and recovery
  • ·         Rollout of new releases
  • ·         Performance tuning of instance as a whole
  • ·         Troubleshooting expert

It’s also essential to bear in mind that there will certainly be overlap in any job you go to; just because you don’t work on production doesn’t mean you shouldn’t know how to patch or backup and recover a database. These are just an idea of the main functions of the different types of roles and what you can expect to spend most of your time doing.

It is very commong for you to be both the Development and Production DBA. I would say that at much larger organisations where there are many Oracle DBAs you are more likely to get the distinction between Development and Production DBAs. In smaller companies, you are more likely to be working both as the Development and Production DBA.