grant select on sys tables

I prefer to use a powerful named user with dba rather than sys. It is more conform to the security policies in place regarding accounting of administrator operations.

Very occasionaly, my user get ORA-1031 insufficient privileges even if I have the dba role.

Amoung others, I have “PURGE DBA_RECYCLEBIN” and DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE

update, 2012-07-24
For purge dba_recyclebin, you probably should purge tables individually
exec for f in(select*from dba_recyclebin where owner!='SYS' and type='TABLE')loop execute immediate 'purge table "'||f.owner||'"."'||f.object_name||'"';end loop;

For DBMS_STREAMS_AUTH, what I am actually missing, is the GRANT OPTION on some documented dba views and dbms package. So I could safely grant the grant option to my user for all sys objects that have been granted to DBA, PUBLIC and any other roles.

Kind of


create table scott.t as 
  select distinct owner,table_name,privilege 
  from dba_tab_privs t 
  where privilege not in ('USE','DEQUEUE') and owner='SYS' ;
begin
  for f in(select * from scott.t) loop 
    execute immediate 
      'grant '||f.privilege||' on "'||f.owner||'"."'
        ||f.table_name||'" to scott with grant option'; 
  end loop;
end;
/

It is better to not select from dba_tab_privs directly, as executing immediate while opening the cursor may have unexpected side effects.

This may help you to increase your security by reducing your connections as sys.

remove the current directory

Can I remove the current directory?

I used to believe you cannot.

Solaris:


$ uname -s
SunOS
$ mkdir /tmp/bla
$ cd /tmp/bla
$ rm -r /tmp/bla
rm: Cannot remove any directory in the path of the current working directory
/tmp/bla
$

AIX:


$ uname -s
AIX
$ mkdir /tmp/bla
$ cd /tmp/bla
$ rm -r /tmp/bla
rm: Cannot remove the current directory /tmp/bla.
$

Today I did a rm that I expected to fail, but …


$ uname -s
Linux
$ mkdir /tmp/bla
$ cd /tmp/bla
$ rm -r /tmp/bla
$ 

Wait, did it work?


$ cd /tmp/bla
$ pwd
/tmp/bla
$ cd /tmp/bla
$ ls -lad /tmp/bla
ls: /tmp/bla: No such file or directory
$ cd /tmp/bla
$

Somehow I am still there, in /tmp/bla, but /tmp/bla has been removed. What a strange operating system ;)

American = fast

I had the incredible behavior of having the same query running 10x faster in sqlplus depending on two different PCs. After analysis, I realised the super fast pc was American… at least in nls_lang

Slow client: PC setup in German, NLS_LANG is set to GERMAN_SWITZERLAND.WE8MSWIN1252


C:\>set NLS_LANG=GERMAN_SWITZERLAND.WE8MSWIN1252

C:\>sqlplus scott/tiger@db01

SQL*Plus: Release 11.2.0.3.0 Production on Fr Jul 6 10:30:25 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Verbunden mit:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set autot on exp
SQL> select job,count(*) FROM emp group BY job order by job;

JOB         COUNT(*)
--------- ----------
ANALYST            2
CLERK              4
MANAGER            3
PRESIDENT          1
SALESMAN           4

Ausf├╝hrungsplan
----------------------------------------------------------
Plan hash value: 2389703825

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     5 |    40 |     5  (40)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |     5 |    40 |     5  (40)| 00:00:01 |
|   2 |   HASH GROUP BY     |      |     5 |    40 |     5  (40)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   112 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Now let’s try with the PC setup with american_america


C:\>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

C:\>sqlplus scott/tiger@db01

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 6 10:31:57 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set autot on exp
SQL> select job,count(*) FROM emp group BY job order by job;

JOB         COUNT(*)
--------- ----------
ANALYST            2
CLERK              4
MANAGER            3
PRESIDENT          1
SALESMAN           4

Execution Plan
----------------------------------------------------------
Plan hash value: 637087546

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    40 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |     5 |    40 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   112 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

The “cost” and “plan” changed. Why this? Maybe an optimizer bug, but it is related to NLS_SORT, which is GERMAN for nls_lang=GERMAN_SWITZERLAND and BINARY for nls_lang=AMERICAN_AMERICA.

Binary!

How to quit crontab -e without overwritting cron

Imagine my crontab


* * * * * /usr/bin/date > /tmp/foo

I am writing the date to /tmp/foo every minute
$  cat /tmp/foo
Thu Jul  5 08:45:01 CEST 2012

Now I want to view my crontab in my EDITOR (vi).


$ crontab -e

I do not quit yet.

In the meantime, my colleague modify the crontab.


* * * * * /usr/bin/date > /tmp/bar

Later, I quit vi with :q!

O Surprise, the crontab is * * * * * /usr/bin/date > /tmp/foo again

According to the doc :

When you finish creating entries and exit the file, the crontab command
copies it into the /var/spool/cron/crontabs directory

Even if you did not make change, you overwrite the content of your crontab !

If you want to exit your crontab editor really without overwritting the crontab, you need to kill yourself.

Ex:


CTRL-Z
[2] + Stopped (SIGTSTP)        crontab -e
$ kill %2
$
[2] + Stopped (SIGTTOU)        crontab -e

Thanks to Colin comment, I realized I could not kill with kill, let’s kill with -9


$ kill -9 %2
$
[2] + Killed                   crontab -e