Oracle: object user grants

Oracle is Oracle… It has good and bad things.
The bad of course… it’s closed and you’ll be crazy before getting info about how to do things.
And the good is that everythings is inside!! just at 1 query distance ;-)

Some days ago I was trying to look for grants given to a user by another user (WITH ADMIN OPTION).
I was logged with sys and I used “my” 2 wonderful querys for obtaining that info (copy/pasted from the internet),

  • Direct grants:
  • SELECT owner, table_name, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv 
      FROM table_privileges
     WHERE grantee = '&theUser'
     ORDER BY owner, table_name;
  • INdirect grants:
  • SELECT DISTINCT owner, table_name, PRIVILEGE 
      FROM dba_role_privs rp JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
     WHERE rp.grantee = '&theUser'
     ORDER BY owner, table_name;

Ressult? 0 rows! WTF!
I know there’s a plenty of it!!!

Continue reading “Oracle: object user grants”

Script to check Oracle remotely (nagios or whatever)

For all of you that are not the lucky owner of an Enterprise Manager license or you simply want to use nagios or another monitoring engine to get status and graphs of oracle, probably you’ll be using the “check_remote_oracle” plugin.

If you’re a scripter/developer maybe you’ll understand me when you open an script and see an COMPLETELY UNREADABLE code, with no functions, no indentation… etc
And then you’ll have to change anything stupid inside the script and then f**k it does not work!

This is the story of that script, I installed it, then I have to do some changes… and then I decided to rewrite it completely…
I’m not the best scripter of the world, but I know how to use functions, indentation and that useless shit :-P

You can read the whole documentation on its wiki page.


PS: updated the instructions, I forgot the information about the unprivileged user for connecting to Oracle instance.
PS2: Updated again, new control!

Oracle Metadata Generator (and svn uploader)

(See comments)

My first (public) script for Oracle :-) [beers]
This script will (hopefully) do:

  • List all the objects on the database for each SID/SCHEMA given
  • Dump the metadata of each object in a different file
  • Upload everything to the svn

If you’re asking why? you must deal more with programmers :-P
This will add a second line of defense to the expdp CONTENT=METADATA_ONLY classic script.
Continue reading “Oracle Metadata Generator (and svn uploader)”