API: Get Users Privileges and characteristics.

It’s quite simple API that generates user’s belongings and privileges in Database. It’s useful to synchronize grants of users between more than one Databases.

I assume we are logged in with SYS User in order to grab full detailed information of all users.

I am using below database views to get the information.

select * from DBA_USERS where ACCOUNT_STATUS = 'OPEN' and USERNAME not in ('SYS','SCOTT','TEST') ;

select distinct DEFAULT_TABLESPACE from DBA_USERS where ACCOUNT_STATUS = 'OPEN' and USERNAME not in ('SYS','SCOTT','TEST');

select * from SYS.DBA_TABLESPACES ;

select * from SYS.DBA_DATA_FILES where 1 = 1 ;

select * from ALL_TAB_PRIVS where 1 = 1 ;

Select * From Dba_Sys_Privs Where 1 = 1 ;

select * from DBA_ROLE_PRIVS where 1 = 1;

Run this code to get all you want regarding database schema. 

Declare

 L_Create_Ts Varchar2(1000) ;     

Begin 

  For Tindx In (Select Distinct Default_Tablespace From Dba_Users Where Account_Status = 'OPEN' And Username Not In ('SYS','SCOTT','TEST')) Loop 

    Dbms_Output.Put_Line('v_Return = ' || Tindx.Default_Tablespace);

     Select Dbms_Metadata.Get_Ddl('TABLESPACE', Tindx.Default_Tablespace) 

     Into   L_Create_Ts

     From Dual;     

  End Loop ;  

  Dbms_Output.Put_Line(' ---------------------- User Grants ----------------------------');

 For Uindx In (Select * From Dba_Users Where Account_Status = 'OPEN' And Username Not In ('SYS','SCOTT','TEST')) Loop 

  Dbms_Output.Put_Line(' -------------Schema : ' || Uindx.Username || '''s Tablespace grants------------------');  

         Dbms_Output.Put_Line('Create user ' 

                             || Uindx.Username 

                             || ' Identified by {}  DEFAULT TABLESPACE "' 

                             ||  Uindx.Default_Tablespace 

                             || '" TEMPORARY TABLESPACE "'

                             || Uindx.Temporary_Tablespace 

                             || '" ; '

                             || Chr(10)

                             || ' Alter user ' 

                             || Uindx.Username 

                             || ' Quota Unlimited On Apps  ' ||  Uindx.Default_Tablespace  ) ;

  Dbms_Output.Put_Line(' -------------Schema : ' || Uindx.Username ||'''s Admin Level Grants------------------');  

      For Aidx In (Select * From Dba_Sys_Privs Where 1 = 1 And Upper(Grantee) = Upper(Uindx.Username)) Loop 

        Dbms_Output.Put_Line('Grant ' 

                             || Aidx.Privilege 

                             || ' To ' 

                             || Aidx.Grantee 

                             || Case When  Aidx.Admin_Option =  'YES' Then ' WITH ADMIN OPTION' Else '' End  

                             || ' ; ');

      End Loop ;     

  Dbms_Output.Put_Line(' --------------Schema : ' || Uindx.Username || '''s Role Level Grants------------------');     

      For Ridx In (Select * From Dba_Role_Privs Where 1 = 1 And Upper(Grantee) = Upper(uindx.Username)) Loop 

        Dbms_Output.Put_Line('Grant ' 

                             || Ridx.Granted_Role 

                             || ' To ' 

                             || Ridx.Grantee 

                             || Case When  Ridx.Admin_Option =  'YES' Then ' WITH ADMIN OPTION' Else '' End  

                             || ' ; ');

      End Loop ;     

  Dbms_Output.Put_Line(' ------------- Schema : ' || Uindx.Username || '''s Object Level Grants------------------');         

      For Tidx In (Select * From All_Tab_Privs Where 1 = 1 And Upper(Grantee) = Upper(Uindx.Username)) Loop 

        Dbms_Output.Put_Line('Grant ' 

                             || Tidx.Privilege 

                             || ' On ' 

                             || Tidx.Grantor 

                             || '.' 

                             || Tidx.Table_Name 

                             || ' To ' 

                             || Tidx.Grantee 

                             || ' ; ') ; 

      End Loop ;     

  End Loop ; 

End; 

Comment below if you feel I missed anything.

DML Operations on Views

Views are used for many purposes including data hiding and giving limited access to end users or NON-DBA schema in Database. When we make visible the less secure column publicly using views at the same time we need to grant some DML on those views without involving base tables.

Here we will focus on these areas.

1. Complex views.
2. Simple views. 
3. Key preserve keys.

DML on complex views.

Complex views can involve more than two tables, complex calculations, inline sub queries, functions called in column selection list, aggregates or grouping data sets. These kind of views are absolutely not allowed for performing DML operations. If it’s really necessary then we need to think about INSTEAD OF Triggers despite of their maintenance issues.

DML on Simple Views. 

A simple view based on single table having less calculated expressions. These kind of views are very easy to be manipulated in term of DML operations. In a simple view these following elements can be involved.

1.       Distinct Keyword.

2.       Aggregate function.

3.       Group by clause.

4.       Expression ( Any Expression or calculated derived column Salary * 12 as ANNUAL_INCOME)

5.       ROWNUM pseudo Column.

6.       ROWID pseudo Column.

7.       Not null column constrained column not listed in selection column list of View.

Here I will discuss some cases when operation is allowed on simple view or not. First we will take a look on this table to get the abstract of all the rules.

	Distinct	Aggregate	Group by	Any Expression	ROWNUM	ROWID	Missing Not null column In View	Key Preserve Tables
Delete	True	False	False	False	False	True	True	True
Update	True	False	False	False/True	False	True	True	True
Insert	True	False	False	False	False	False	False	True

You can use this SAMPLE codes.

If we analyze the table, all the cases are very straight forward. For example a simple view having DISTINCT keyword could be allowed for DML operations similarly view having ROWID will allow for delete and update but not for Insert. On the other hand we have a special case which is view having Expression e.g

Create Or Replace View With_Expression As

Select  Employee_Id , Department_Id , First_Name ||  Last_Name As Full_Name, Salary * 12 Anual_Salary

From   Employees

Where  1 = 1 ;

Delete and insert is clearly not allowed. But Update is TRUE/FALSE. This true and false is depend on WHERE clause that we use in update. If we use expression in WHERE clause and simple column in SET clause to update in this case it’s TRUE and if we use simple columns in WHERE clause to update expression in the SET clause in this case it’s FALSE.

Update With_Expression

Set    Full_Name = 'XYZ'

Where  Department_Id = 90  ;

Result: False

Update With_Expression

Set    Anual_Salary = 90

Where  Department_Id = 90  ;

Result: False

Update With_Expression

Set    Department_Id = 100

where  Anual_Salary = 108000 ;

Result: True

DML on Key preserved

let's say we have two tables. One is master table which is Department. One is child which is Employees. When we join these two tables the DEPARTMENT_ID will be repeated against each corresponding child entry in employees tables but the EMPLOYEE_ID in this join will not repeated. So in this case our Key preserve table is Employees where each entry Appear in the result at most one time. 

for more Ask Tom

According to our table, all the operations are allowed satisfying other conditions. But only satisfying rows should be from the table which having now repetition of PK in the result set of View. 

Here are some experiments.

Create Or Replace View Dpt_Emp_V As 

Select         

       Departments.Department_Id  Dpt_Id,

       Departments.Department_Name,

       Employees.Employee_Id      ,

       Employees.First_Name       ,

       Employees.Last_Name        ,

       Employees.Hire_Date        ,

       Employees.Salary

From   Departments

Inner Join Employees

On     Departments.Department_Id = Employees.Department_Id ;

Note: We are not including DEPARTMENT_ID from Employees tables.

Update On DPT_EMP_V

Update Dpt_Emp_V 

Set First_Name = 'MODIFIED' 

Where  Dpt_Id = 90 ;

Result: True

Because "First Name" belongs to Key preserved table and the column in WHERE clause is belong to Non-Key Preserved. 

Now let's reverse the clock. 

Update Dpt_Emp_V 

Set Dpt_Id = 500 

Where  employee_id = 101 ; 

Result: False  

Because DPT_ID belongs to Non-Key Preserve table. 

Delete On DPT_EMP_V

Here is very tricky point:

if we issue a delete statement on this view having filter on Non-Key preserve table's column the rows will be deleted from Key-Preserve tables. 

let's give it a try. 

Select count(*) From Departments Where 1 = 1 ;

count: 27

Select * From EMPLOYEES Where 1 = 1 ;

count: 107

DELETE FROM Dpt_Emp_V WHERE Dpt_Id = 90;

3 Rows Deleted.

Select * From EMPLOYEES Where 1 = 1 ;

Count: 104

Rollback; 

Explanation:

We didn't include DEPARTMENT_ID from employees table but when we issue DELETE command after satisfying all the conditions rows are deleted from Key-Preserve table. 

Insert On DPT_EMP_V

Satisfying all the insert criteria the values can be inserted on this view but the insertion will be occurred in Key-Preserve table which is, in our case is EMPLOYEES.

Conclusion: 

It's always been considered that simple views or views having two tables can easily be modified using DML statements but it's not true in all the situations. INSTEAD of triggers can be a good idea to handle these situations but still we should seriously need to figure it out do we really need triggers to updated hidden column's data because of maintainability of triggers. Maintaining manipulations on views depends on business logic.

Make Delay in your PL\SQL code Using Java

Any scenario or any requirement can drive you make such delay in your code. recently I have came across  some requirement where I have to hold my code flow for couple of seconds. the scenario was that there is a Database job that sends E-mail through Microsoft Exchange server using Database code. I will not discuss the code of E-mail right now but the problem was when i run the code it loops through all the target emails within 4 seconds that was the problem for Exchange Server to get hold all the requests, Therefore, I need to make delay during execution of PL\SQL block.
Here it is.

You can use this according to any of your requirement like this.

I am using 

Here is your Java source that will be Stored in your Database.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "Sleep"
as import java.io.*;
public class Sleep extends Object
{
   public static void sleep()
   {
      try
      {
         Thread.sleep(3000);  //<----------- 1000 =  1 second
      }
      catch(Exception ex)
      {}
   
   }
 
 
}

Once compile above command you can find it in your Database tool's navigator.

Now make PL\SQL wrap for your Java Source.

Create Or Replace Procedure P_Sleep 

As Language Java Name  

'Sleep.sleep() ';

Now at this step you can use your flow even in your Package or any procedure using this code template.

Declare

   V$time_1 Number ; 

   V$time_2 Number ;  

   V$differnce Number ;  

Begin

   V$time_1:= Dbms_Utility.Get_Time;   

   Dbms_Output.Put_Line('Starting Time: ' || V$time_1 );   

   P_Sleep;

   V$time_2 := Dbms_Utility.Get_Time;

   Dbms_Output.Put_Line('Ending Time: ' || V$time_2);

   V$differnce := (V$time_2 - V$time_1) ;  

   Dbms_Output.Put_Line('Difference in Time: ' ||  V$differnce / 100 || ' - Secs') ; 

End;