What privileges are needed for flashback queries?

If the table is in your own schema, no extra privileges are needed. If the table is in a schema different from your own, you need object privileges to flashback the table:

grant flashback on scott.emp to jim;

or you can grant user jim a system privilege to flashback any table:

grant flashback any table to jim;

How to solve ORA-01031: insufficient privileges when creating a cross-schema fk constraint

Scenario:
you want to create a cross-schema Foreign key constraint:

 ALTER TABLE JIM.TRANSACTION
 ADD ( CONSTRAINT TRANSACTION_FK 
       FOREIGN KEY (TRANS_ID) REFERENCES 
       DWIGHT.TRANSACTION_HISTORY(TRANS_ID)
     );

In order to accomplish this, user JIM needs the REFERENCES system privilege on DWIGHT's table:

GRANT REFERENCES ON DWIGHT.TRANSACTION_HISTORY TO JIM;

How to create a trigger that will add newly created tables to a role

A commonly asked for functionality in my day-to-day work is READ-ONLY access for users to various tables in the database. This is easy to solve by creating a role, and granting SELECT on the tables to that role.

Every time a new table is created, the role must be updated. This will for most of the time be impossible to administer, and should be automated by creating a trigger on the schema owning the tables/views.

Here is how:

create a new role:

create role my_ro_role;

Grant execute permissions on dbms_jobs to scott:

grant create job to scott;

Note that if you do not have the password for the schema that should own the trigger, you cannot use the SYS user to create it for them. You need SYSTEM or another DBA user to avoid the error

ORA-30510: system triggers cannot be defined on the schema of SYS user

Create the trigger. In this example, I am using the system user to accomplish this:

conn system/password
create or replace trigger scott.add_to_ro_role
after CREATE on schema
declare
 l_str varchar2(255);
 l_job number;
begin
 if ( ora_dict_obj_type = 'TABLE' ) then
  l_str := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to my_ro_role";';
  dbms_job.submit( l_job, replace(l_str,'"','''') );
 end if;
end;
/

Every time a new table is created in the SCOTT schema, SELECT on the same table will be granted to the role.
The role can then be granted to individual users.

Thanks to Tron Malmø-Lund for the idea and the code to implement it! ;-)

Changes in privilege "SELECT ANY DICTIONARY" in Oracle 12c

From version 12.1 and onwards, Oracle has introduced some changes to enhance security when granting the system privilege "SELECT ANY DICTIONARY".

In the New Features guide for version 12.1, the authors explain:

The SELECT ANY DICTIONARY privilege no longer permits access to security sensitive data dictionary tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, and XS$VERIFIERS. This change increases the default security of the database by not allowing access to a subset of data dictionary tables through the SELECT ANY DICTIONARY privilege.

The access to USER$ has also been excempt when granting the system privilege SELECT ANY TABLE and the role SELECT_CATALOG_ROLE, but I have not yet found the documentation that verifies this.

How to bulk-grant privileges to a selection of users using PL/SQL

This anonymous PL/SQL script will select all the desired users, and then grant SELECT on a number of performance views to these.

set serveroutput on

DECLARE

 CURSOR c1 IS
  select username
  from dba_users
  where username like 'IT%'
  or username like 'MAITD%';

BEGIN
 FOR x IN c1 LOOP
    dbms_output.put_line('user ' || x.username || ' processed.' );
    execute immediate('GRANT SELECT ON SYS.IND$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.OBJ$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.TAB$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.USER$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$DATABASE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$INSTANCE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$LATCH TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$LIBRARYCACHE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$MYSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$PROCESS TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$ROWCACHE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESSION TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESS_IO TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SGASTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$STATNAME TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SYSSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SYSTEM_EVENT TO ' || x.username);
 END LOOP;
END;
/

How to use the 12c Privilege Analysis feature

One of the many New features in Oracle 12c is the ability to perform analysis of privileges that are assigned to a user.

Privilege Analysis sorts under Oracles "Database Vault" option, but can be used without enabling Database Vault.
Note that you need a valid License for Database Vault option to use the privilege analysis package.

To check if it's allready in use:

SELECT
   NAME             ,
   DETECTED_USAGES  ,
   FIRST_USAGE_DATE ,
   LAST_USAGE_DATE,
   LAST_SAMPLE_DATE,
   CURRENTLY_USED,
   DESCRIPTION   
 FROM
   DBA_FEATURE_USAGE_STATISTICS
 WHERE
   FIRST_USAGE_DATE IS NOT NULL
   AND NAME LIKE 'Privilege%';

From my example database, this is the output:

NAME	DETECTED_USAGES	FIRST_USAGE_DATE	LAST_USAGE_DATE	LAST_SAMPLE_DATE	CURRENTLY_USED	DESCRIPTION
Privilege Capture	11	27.05.2017 03:41:35	05.08.2017 03:46:46	05.08.2017 03:46:46	TRUE	Privilege Capture is being used

Here's how to set it up:

1. Create the capture:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.create_capture(
    name        => 'my_policy',
    type        => DBMS_PRIVILEGE_CAPTURE.g_context,
    condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SCOTT'''
  );
END;
/

2. Check that the capture was created:

COLUMN name FORMAT A15
COLUMN roles FORMAT A20
COLUMN context FORMAT A30
SET LINESIZE 100

SELECT name,
       type,
       enabled,
       roles,
       context
FROM   dba_priv_captures
ORDER BY name;

3. Enable the capture

BEGIN
  DBMS_PRIVILEGE_CAPTURE.enable_capture('my_policy');
END;
/

When a representative time has passed, you can disable the capture and generate results:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.disable_capture(
    name        => 'saga2_felles_policy'
  );

  DBMS_PRIVILEGE_CAPTURE.generate_result(
    name        => 'saga2_felles_policy'
  );
END;
/

You can check the results of the capture by using these queries:

SET LINESIZE 200
COLUMN username FORMAT A20
COLUMN username FORMAT A20
COLUMN used_role FORMAT A30
COLUMN path FORMAT A50
COLUMN sys_priv FORMAT A30
COLUMN obj_priv FORMAT A8
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A11

prompt ================================
prompt Which privileges have been used?
prompt Look in DBA_USED_SYSPRIVS
prompt ================================
SELECT username, sys_priv
FROM   dba_used_sysprivs
WHERE  capture = 'my_policy'
ORDER BY username, sys_priv;

prompt ================================
prompt How were the privileges granted
prompt to the user?
prompt Look in DBA_USED_SYSPRIVS_PATH
prompt ================================
SELECT username, sys_priv, used_role, path
FROM   dba_used_sysprivs_path
WHERE  capture='my_policy'
order by username,sys_priv;

prompt ================================
prompt What object privileges were necessary?
prompt Look in DBA_USED_OBJPRIVS
prompt ================================
SELECT username, obj_priv, object_owner, object_name, object_type
FROM   dba_used_objprivs
WHERE  capture = 'my_policy';

prompt ================================
prompt How were the object privileges granted to the user?
prompt Look in DBA_USED_OBJPRIVS_PATH
prompt ================================

SELECT username, obj_priv, object_owner, object_name, used_role, path
FROM   dba_used_objprivs_path
WHERE  capture = 'my_policy';

exit

To drop the usage of Privilage Capture all together:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.drop_capture(
    name        => 'my_policy'
  );
END;
/

What is the INHERIT PRIVILEGES granted to PUBLIC in 12c?

I was checking which object privileges my user had made, and I found the following:

select * from user_tab_privs_made;

GRANTEE	TABLE_NAME	GRANTOR	PRIVILEGE	GRANTABLE	HIERARCHY	COMMON	TYPE
PUBLIC	MYUSER	MYUSER	INHERIT PRIVILEGES	NO	NO	NO	USER

I couldn't understand when this privilege had been made, and why the type was set to USER.

Searching the internet, I soon found that this is a new privilege in Oracle 12c. By default the privilege INHERIT PRIVILEGES is granted to PUBLIC, and it secures a weakness where a malicious user may accidently get access to a privilege that user shouldn't have.

For backward compability INHERIT PRIVILEGES is granted to PUBLIC in Oracle 12c, but it can and probably should be revoked.

Tim Hall has setup a simple example on how this new privilege can secure your database. Instead of trying to mimic his work, I will simply refer you to his site.

You can find his article at oracle-base.com

See also the release changes section for Oracle 12c

Getting ORA-01031: insufficient privileges when data dictionary table is being used in a view

I must admit I have been consulted in these situations before, but since then I had forgotten how it worked and failed to take notes on how to solve it.

So here it is: a user is getting a run-time error ORA-01031: insufficient privileges when accessing his view.
The view is based on his own objects and a lookup to the dynamic performance view V$DATABASE.

Example:

connect scott/tiger

CREATE VIEW MYVIEW AS
SELECT 
FROM MYTABLE MT,
     V$DATABASE DB
WHERE....
AND... ;

If the user has only SELECT ANY TABLE, Oracle will return runtime error ORA-01031 when the view is compiled.

However, if you give user scott the SELECT privilege on the table directly:

GRANT SELECT ON V_$DATABASE TO SCOTT;

then Oracles rules for object creation is honored and the runtime error will disappear.

Why aren't other schemas' procedures showing in TOADs schema browser?

Problem: User SCOTT has been granted SELECT on a number of tables belonging to another schema, OBM.
They all show up neatly in TOADs schema browser.

However, procedures and sequences are not visible. Why?

Answer: lack of privileges.

Solution: Try granting the following as user sys:

GRANT SELECT ON OBM.sequence1 to SCOTT;
GRANT DEBUG ON OBM.proceure1 to SCOTT;

Refresh the schema browser - voila!

To generate a list of objects with grant statements:

SELECT 'GRANT DEBUG ON ' || OWNER || '.' || OBJECT_NAME || ' TO SCOTT;' 
FROM DBA_PROCEDURES 
WHERE OWNER = 'OBM' 
AND OBJECT_TYPE='PROCEDURE';

SELECT 'GRANT SELECT ON ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME || ' TO SCOTT;' 
FROM DBA_SEQUENCES 
WHERE SEQUENCE_OWNER = 'OBM';

Alternatively, if acceptable in your environment you could grant select and debug on all sequences and procedures, respectively:

GRANT SELECT ANY SEQUENCE TO SCOTT;
GRANT DEBUG ANY PROCEDURE TO SCOTT;