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 Mustafa has progressive experience in Oracle Products, community. He recently served as Oracle Database Administrator.
Provide Database Implementation Solutions, High Availability Solution, Infrastructure and Storage Planning, Install, Configure, Implement and manage Oracle E-Business Suite environments. Architect, build and support highly-available Oracle EBS, Database and Fusion Middleware environments including appropriate reporting, Installs, configures, upgrades, tunes, and maintains production, development and test databases.
View all posts by Osama Mustafa
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