Security Features 12c

Posted on by Osama Mustafa in Database
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

Published by Osama Mustafa

Osama considered as one of the leaders in Cloud technology, DevOps and database in the Middle-East. I have more than ten years of experience within the industry. moreover, certfied 4x AWS , 4x Azure and 6x OCI, have also obtained database certifications for multiple providers. In addition to having experience with Oracle database and Oracle products, such as middle-ware, OID, OAM and OIM, I have gained substantial knowledge with different databases. Currently, I am architecting and implementing Cloud and DevOps. On top of that, I'm providing solutions for companies that allow them to implement the solutions and to follow the best practices.

4 thoughts on “Security Features 12c”

  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_PRIVFROM DBA_USED_SYSPRIVSWHERE USERNAME IN ('OSAMA' , 'TEST');/Frank

    Like

    Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.