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 thoughts on “Security Features 12c

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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