Thursday, June 27, 2013

Security Features 12c

As Any Newer Version of database oracle Enhance Security This time Oracle create new users for administration purpose

Separate user duties : 

SQL> select username from dba_users where username like '%SYS%';


USERNAME
----------------------------------
SYS  : Super user
SYSKM : Key magament tasks
SYSDG : Data Guard Managment
SYSBACKUP : backup management


DBMS_PRIVILEGE_CAPTURE

The Privilege Analysis feature allows you to: 

  • Define a privilege capture
  • Start a privilege analysis during a period of time
  • Delete capture analysis
The Capture can be Done on Three level :
  • User
  • Role
  • Context
to understand this new package check the below examples :

sqlplus / as sysdba

SQL> create user test identified  by test ;

SQL> grant create session to test ;
Grant succeeded.

SQL> grant select any table to test ;
Grant succeeded.

Create new Capture using this package :

exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( -
        name           =>  'test_capture', -
        description    =>  'Capture_all_thing', -
        type           =>   dbms_privilege_capture.g_database)

Where 

Name : Package name
Description : What this package fo
type : depend on level users ( g_database ) , role (g_role )  , Context : (g_role_and_context)

we need to enable this package to start capture :

SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'test_capture');
 PL/SQL procedure successfully completed.

until everything goes fine now to start capture you have to logout from sqlplus and start do what you need to do.

SQL> conn test/test ;
Connected.

SQL> select * from osama.test ;
no rows selected

SQL> select * from osama.test2 ;

   TEST_ID TEST_NAME
---------- --------------------
1 JENNIFER
2 STEVE
3 USER

again reconnect as / as sysdba to disable capture then generate results like the following :

SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => 'test_capture');
PL/SQL procedure successfully completed.

SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => 'test_capture');
PL/SQL procedure successfully completed.

SQL> select username, object_owner, object_name, obj_priv
from   dba_used_objprivs
where  username in ('OSAMA', 'TEST') 


USERNAME   OBJECT_OWNER OBJECT_NAME       OBJ_PRIV
---------- ------------ ------------------------------ -------------------------
TEST   SYS ORA$BASE                                      USE
TEST   SYS DBMS_APPLICATION_INFO       EXECUTE
TEST   SYS DUAL                                              SELECT
TEST   SYS DUAL                                               SELECT
TEST   SYSTEM PRODUCT_PRIVS                     SELECT


Drop Capture :
exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => 'test_capture')

Thank you 
Osama mustafa

4 comments:

  1. Thank you for sharing this very nice post, please keep continue the sharing of this types of information. Here we are waiting for more

    ReplyDelete
  2. Hi Osama,
    thank you for the example. If you access DBA_USED_SYSPRIVS it will give you the SELECT ANY TABLE privilege that was used to access the test tables.

    SELECT USERNAME, SYS_PRIV
    FROM DBA_USED_SYSPRIVS
    WHERE USERNAME IN ('OSAMA' , 'TEST');

    /Frank

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Hi,

    I had setup 12c RAC database and created few users in PDB's (Local Users). But when i try to connect to those users i am facing ORA-12154.

    [oracle@db2 ~]$ sqlplus REP/Oracle789@prodpdb.oraclehour.com

    SQL*Plus: Release 12.1.0.1.0 Production on Tue Sep 3 01:07:37 2013

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

    ERROR:
    ORA-12154: TNS:could not resolve the connect identifier specified

    Kindly advise. Any help is highly appreciated.

    Thanks,
    Sravan

    ReplyDelete