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
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 :
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
Like this:
Like Loading...
Related
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.
View all posts by Osama Mustafa
Technology Geek 
Thank you for sharing this very nice post, please keep continue the sharing of this types of information. Here we are waiting for more
LikeLike
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
LikeLike
This comment has been removed by the author.
LikeLike
This comment has been removed by the author.
LikeLike