Kaley's (mostly) Oracle Database Blog
Hi! I'm Kaley Crum, Oracle DBA.
Welcome to my (mostly) Oracle database blog.

Author: Kaley

Query to Examine Blocking

 

See All Grants Necessary for Selecting From a View

Ran into a frustrating problem where schema “A” couldn’t select from a view that schema “B” owned.  This was because schema “B” referenced tables that were owned by schema “C,” and schema “B” was granted “SELECT” privileges only on those tables, where it should have been granted “SELECT…WITH GRANT OPTION.”

This is a bit like killing a fly with a sledgehammer, so it should be used with thoughtful consideration to the exact privileges that are being granted.  But this SQL should list out all the necessary grants for a user to be able to select from a view.  Just fill in the VIEW_OWNER, VIEW_NAME, and GRANT_SELECT_TO pieces.

 

Grabbing the “Most Recent Record”

Suppose you have a table full of tweets.

You would like a view that will show you the most recent tweet of every username, and you want it done fast and efficiently!  (Suppose that the table has millions of rows)

What’s the best way to get this information?  I don’t know.  But I think I’ve got a way that’s pretty fast.

Continue Reading

Detecting Primes with REGEXP_LIKE()

I saw this trick where a regular expression could be used to see if a number is prime or not.  Which, once I saw it, I had to try it out in Oracle.  It works, but it’s not efficient by any stretch of the imagination.

It works by using ‘1’ as a dummy character (any character could’ve been used, ‘1’ was arbitrarily chosen).  For whatever number that we’re trying to determine is prime or not (13 in the above example) we generate a string of that many ‘1’s.  So since 13 was selected in the above example, RPAD creates  a string that is 13 ‘1’s, so it looks like this:  1111111111111

Next, we try the first half of the regular expression:

Meaning, if the generated string is equal to ” or ‘1’ (indicating that the numbers 0 or 1 were chosen) then we immediately return true (meaning prime).  Otherwise, we try this expression:

Which means “search” for a group of two or more ‘1’s in a non-greedy search, repeated one or more times.

So search goes like this

Is is a multiple of two?
11 (no match, let’s try repeating that group)
1111 (still no match, add another repeated group)
111111 (no match, continue adding 11 to the end…)
11111111 (no match)
1111111111 (no match)
111111111111 (no match)
11111111111111 (too long! backtrack)

Is it a multiple of three?
111 (no match, repeat the group of three ‘1’s)
111111 (no match, add another group of three)
111111111 (no match, continue adding groups of three and checking for matches)
111111111111 (no match)
111111111111111 (too long! backtrack)

Is it a multiple of four?
1111 (no match)
11111111 (no match)
111111111111 (no match)
1111111111111111 (too long! backtrack)

Is it a multiple of five?…

etc…until the string that it’s looking for is longer than the string that’s being searched on.  If no matches have been had, the REGEXP_LIKE function returns false, indicating prime.

See Privileges Granted Through Roles

Here’s a couple of queries that can be used to see privileges granted through roles.

System Privileges:

Object Privileges:

 

Which Partition Is My Row In?

I recently worked on a project where I have an interval-partitioned table that I insert into daily.  After the insert, I wanted to gather statistics on the partition that I’d just inserted to.  I thought back through some recent reading and remembered that the ROWID of an object contains the following elements:

  • Letters 1-6 = the OBJECT_ID
  • Letters 7-9 = the relative FILE_ID
  • Letters 10-15 = the BLOCK within the file
  • Letters 16-18 = the ROW within the block

I knew there was a package called DBMS_ROWID (although I had never used it) but I guessed there would be a procedure that I might could acquire an OBJECT_ID for a given ROWID.

And I was right.

Continue Reading

ORA-01031 Insufficient Privileges Reference

Trying to Creating a View?

You need the CREATE VIEW system privilege to create a view in your own schema, or the CREATE ANY VIEW system privilege to create a view in someone else’s schema.  These privileges can be inherited through roles.

You also need to be granted SELECT, INSERT, UPDATE, and DELETE on all tables referenced by the view, or the corresponding SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, and DELETE ANY TABLE system privileges, and these privileges may not be obtained through roles.

Additionally, in order to grant other users access to your view, you must have received object privileges to the base tables with the WITH GRANT OPTION, or the appropriate system privileges with the WITH ADMIN OPTION.

 

Trying to Truncate a Table?

If the table you’re trying to truncate is not in your own schema, you must be granted the DROP ANY TABLE privilege.  This privilege may be inherited through a role.

 

Trying to do something in a Procedure, Function, or a Trigger?

If the procedure or function is created with definer’s rights (which is the default) then any privilege obtained through a role is “null and void.”  Any privileges you’re trying to take advantage of must be granted to you directly (i.e. not through a role).  For example, if you create a new user and only grant the user the CONNECT and RESOURCE roles, you’ll see that if you login as the newly created user, you’ll be able to create a table; however, if the newly created user tries to create a procedure that creates a table, you’ll get “ORA-01031: insufficient privileges” because you’ve not been granted the CREATE TABLE system privilege directly–it’s been granted to you through a role.  Try granting the CREATE TABLE privilege directly to the owner of the procedure/function.

Triggers always have definer’s rights, so the above is always true for triggers.

If the procedure is created with invoker’s rights, it’s true that an invoker of the function may take advantage of roles; however at compile time the owner of the procedure must have been granted the necessary permissions on the underlying objects if static SQL is being used.  Again, these permissions cannot be inherited through roles–this is necessary for compile-time validation.  One way to avoid this is to use dynamic SQL (execute immediate), so that the dependency is not checked at the function’s compile time.

 

Are you used to working with a version of Oracle prior to 10.2?

Note that if you’re accustomed to working on Oracle version that’s pre-10.2, you were automatically granted the ALTER SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE CLUSTER, CREATE DATABASE LINK, and CREATE SEQUENCE system privileges as part of the CONNECT role, but starting in Oracle 10.2 and beyond, the CONNECT role only has the CREATE SESSION.  So granting someone CONNECT will no longer allow them to create tables, views, etc.  More information here.  Note that using the CONNECT role nowadays is deprecated by Oracle.