Hi Readers, In this tutorial, I will take you to the steps to handle the role of database users and the importance of profile's need and how to stop them. As always keep in mind, provide the right access to database users is the precious thing that every administrator should possess. :-)
Let us jump into the tutorial,
Creating a role and assign system and object privileges at the same,
One who has to create role privilege only creates a role.
Now, create two roles in sys user and pass it to the user,
Big_role ( system and object privileges)
Small_role
Make sure there is no privilege assigned to c user,
Grant the big_role (which has small_role also) to ‘c’ user,
Connect ‘C’ user and check whether to create session, table and select b.b1 table happened or not,
Now, check the role with admin option,
Create a new user called ‘D’ and test the admin option,
Connect C and pass it D, then revoke a role from C, but still, D can access the permissions, As a result ROLES and SYSTEM privileges cannot be cascaded.
If the role has system and object privileges, we can use only ‘with admin option’ and grant won’t work,
PROFILE
The profile contains constraints imposed on specific users.
Default_profile assigned to all users. There two limiting parameters exist,
- Resources
- Password constraints
Scenario: Create a profile P1 assigning which to the user account will impose the following constraints,
1.user change password after every 30days with 6days of the extended time, if not changed PWD.
2. The user cant stay connected to the DB without doing anything for more than 10mins.
3. one user can not establish more than 3 sessions
4. one user session consume 200seconds CPU time.
5. one user can try 5 attempts for failed logging in.
Note: to apply the resource constraints we should set resource_limit as true.
To assign this profile to a particular user,
Alright, here it concludes. I hope this might be useful for you. Thanks for reading!