Mega Code Archive
How to verify User Object Privileges in ORACLE from DELPHI
<![CDATA[
Title: How to verify User Object Privileges in ORACLE from DELPHI ?
Question: When you want to insert or select from a table, sometimes you are not sure whether the user had privilege to do that.
so it's good to have something to verify before you do this.
Answer:
Create new package and put this unit into the package, compile it and install, you will see the commponent in the data control tab.
This unit programmed to provide simplicity of the programmer side,
when the programmer writing code like insert into table, he or she can check first whether the user had right to do that using this unit, instead of getting the result through the database return code. use bde or odbc connection for this unit.
HOW TO USE THE UNIT BELOW IN YOUR FORM
======================================
Let just say you have 1 form with dbgrid that show tabel with name 'tabelX' and 2 button (insert,delete).
the problem is this form is for any user with different right to this table, so to prevent user that doesn't have right to insert to tablex to click button insert. do this !!
in the form create event
oraprivs1:=Toraprivs.create(application);
oraprivs1.open;
btn_insert.enabled:=oraprivs1.HadPrivilege('tableX',pinsert);
btn_delete.enabled:=oraprivs1.HadPrivilege('tableX',pdelete);
so with that code, the button insert, and delete will have different behaviors in different users session. got it ???
===============================================================================
{ Designed and Programmed by Said Azli
Feature Idea by Onno Lagerwerf
Created : 23-08-2001
name : Oracle Object Privilege Identification
description : This feature making list of all object privilege of user
that creating a session into the database.
This feature tested on Ms Windows 2000, Oracle 8.1.7.0.0 Beta
User Guide :
1. This fature only display object privilege, no system privilege.
The object that were identified are :
- Table ( Insert, delete, update, select privilege)
- View ( only select privilege)
- Sequence ( only select privilege)
- Procedure/function/package (only execute privilege)
2. You can put the object_name as was created or the synonym name of the object.
For procedure or package composed in a package, put the package name or package
synonym name instead of the procedure or the function name in the package, since
dba can't grant execute partially on a package.
This information is important for searching privilege in the list
3. Property and procedure that you can use from this unit is :
- property DatabaseName: string ;
- Create(AOwner: TComponent); override;
- Destructor Destroy; override;
- Procedure Open;
- procedure Close;
- Procedure Refresh;
- function HadPrivilege(ObjectName: string; Privilege: TPrivs): boolean;
Information on how to use this procedure can be found in the procedure
or function documentation below
4. This feature can be expand, any comments please e-mail to ayedfathan@hotmail.com,
or s.azli@ptt-post.nl
5. Any use of this unit are dedicated with respect to the idea maker, designor,
and programmer : Said Azli and Onno Lagerwerf for free.
}
unit OraPrivs;
interface
uses SysUtils, Classes, dbTables, Dialogs;
type
PDBObject = ^ADBObject;
ADBObject = record
owner,
object_name,
Privilege : string;
end;
TPrivs = (pSelect, pInsert, pUpdate, pDelete, pExecute);
TOraPrivs = class(TComponent)
private
LstPrivilege : TList;
FQuery: TQuery;
User : string;
FDatabase: TDatabase;
Function GetUSerName : string;
Procedure GetObjectBelongToUser;
Procedure GetObjPrivilegeGrantByOther;
Function FoundMatchInList(owner,object_name,privilege: string):boolean;
Function FoundMatch(object_name,privilege: string):boolean;
Procedure GetAvailableSynonym;
function RetrieveOriginalObjectInList(owner : string;object_name : string):Tlist;
procedure SetDatabase(const Value: TDatabase);
protected
public
constructor Create(AOwner: TComponent); override;
destructor Destroy; override;
procedure Notification(AComponent: TComponent; Operation: TOperation); override;
Procedure Open;
procedure Close;
Procedure Refresh;
function HadPrivilege(ObjectName: string; Privilege: TPrivs): boolean;
published
property Database: TDatabase read FDatabase write SetDatabase;
end;
procedure Register;
implementation
procedure Register;
begin
RegisterComponents('Data Controls', [TOraPrivs]);
end;
{ TOraPrivs }
procedure TOraPrivs.Close;
begin
LstPrivilege.Clear;
end;
constructor TOraPrivs.Create(AOwner: TComponent);
begin
inherited;
FQuery := TQuery.Create(Self);
LstPrivilege:=TList.create;
end;
destructor TOraPrivs.Destroy;
begin
FQuery.Free;
LstPrivilege.free;
inherited;
end;
function TOraPrivs.FoundMatch(object_name, privilege: string): boolean;
{
finding object privilege in the list
return values is true if founded, and false if not
}
var
i, j : integer;
DBObject : PDBObject;
found : boolean;
begin
new(DBObject);
j:=LstPrivilege.count-1;
i:=0;
found:=false;
while (not(ij)) and (not found) do
begin
dbobject:=LstPrivilege.items[i];
if (AnsiStrIComp(PChar(trim(dbobject^.object_name)),PChar(trim(object_name)))=0)and
(AnsiStrIComp(PChar(trim(dbobject^.privilege)),PChar(trim(privilege)))=0) then
found :=true
else
inc(i);
end;
result := found;
end;
function TOraPrivs.FoundMatchInList(owner, object_name,
privilege: string): boolean;
var
i, j : integer;
DBObject : PDBObject;
found : boolean;
begin
new(DBObject);
j:=LstPrivilege.count-1;
i:=0;
found:=false;
while (not(ij)) and (not found) do
begin
dbobject:=LstPrivilege.items[i];
if (AnsiStrIComp(PChar(trim(dbobject^.owner)),PChar(trim(owner)))=0) and
(AnsiStrIComp(PChar(trim(dbobject^.object_name)),PChar(trim(object_name)))=0)and
(AnsiStrIComp(PChar(trim(dbobject^.privilege)),PChar(trim(privilege)))=0) then
found :=true
else
inc(i);
end;
result := found;
end;
procedure TOraPrivs.GetAvailableSynonym;
var
TempLst : TList;
DBObject,DBObjectNew : PDBObject;
synonym_name, table_owner, table_name : string;
i,j : integer;
object_name,owner,privilege:string;
begin
if Fquery.active then
Fquery.close;
Fquery.sql.text:='SELECT SYNONYM_NAME , TABLE_OWNER, TABLE_NAME '+
'FROM ALL_SYNONYMS '+
'WHERE TABLE_OWNER ''SYS''';
Fquery.open;
new(DBObject);
TempLst:=TList.Create;
Fquery.first;
while not Fquery.eof do
begin
synonym_name:=Fquery.fieldbyname('synonym_name').asstring;
table_owner:=Fquery.fieldbyname('table_owner').asstring;
table_name:=Fquery.fieldbyname('table_name').asstring;
TempLst.Clear;
TempLst:=RetrieveOriginalObjectInList(table_owner,table_name);
j:=TempLst.count-1;
for i:=0 to j do
begin
Dbobject:=TempLst.items[i];
object_name:=synonym_name;owner:=dbobject^.owner;privilege:=dbobject^.Privilege;
new(DBObjectNew);
DBObjectNew^.Object_name:=object_name;
DBObjectNew^.owner:=owner;
DBObjectNew^.Privilege:=privilege;
if not FoundMatchInList(DBObjectNew^.owner,DBObjectNew^.object_name,
DBObjectNew^.privilege) then
LstPrivilege.add(DBObjectNew);
end;
Fquery.next;
end;
end;
procedure TOraPrivs.GetObjectBelongToUser;
var
object_type : string;
DBObject : PDBObject;
begin
if Fquery.active then
Fquery.close;
Fquery.sql.text:='SELECT OBJECT_NAME,OBJECT_TYPE '+
'FROM USER_OBJECTS '+
'WHERE OBJECT_TYPE IN (''TABLE'',''VIEW'',''PROCEDURE'',''FUNCTION'',''PACKAGE'')';
Fquery.open;
Fquery.first;
while not Fquery.eof do
begin
object_type:=Fquery.fieldbyname('object_type').asstring;
if (object_type='PROCEDURE') or (object_type='FUNCTION')or
(object_type='PACKAGE') then
begin
new(DBObject);
DBObject^.owner:=user;
DBObject^.object_name:=Fquery.fieldbyname('object_name').asstring;
DBObject^.privilege:='EXECUTE';
LstPrivilege.add(DBObject);
end
else begin
if (object_type='VIEW') or (object_type='SEQUENCE') then
begin
new(DBObject);
DBObject^.owner:=user;
DBObject^.object_name:=Fquery.fieldbyname('object_name').asstring;
DBObject^.privilege:='SELECT';
LstPrivilege.add(DBObject);
end
else begin
new(DBObject);
DBObject^.owner:=user;
DBObject^.object_name:=Fquery.fieldbyname('object_name').asstring;
DBObject^.privilege:='INSERT';
LstPrivilege.add(DBObject);
new(DBObject);
DBObject^.owner:=user;
DBObject^.object_name:=Fquery.fieldbyname('object_name').asstring;
DBObject^.privilege:='SELECT';
LstPrivilege.add(DBObject);
new(DBObject);
DBObject^.owner:=user;
DBObject^.object_name:=Fquery.fieldbyname('object_name').asstring;
DBObject^.privilege:='UPDATE';
LstPrivilege.add(DBObject);
new(DBObject);
DBObject^.owner:=user;
DBObject^.object_name:=Fquery.fieldbyname('object_name').asstring;
DBObject^.privilege:='DELETE';
LstPrivilege.add(DBObject);
end;
end;
Fquery.next;
end;
end;
procedure TOraPrivs.GetObjPrivilegeGrantByOther;
var
DBObject : PDBObject;
owner,object_name,privilege : string;
begin
if Fquery.active then
Fquery.close;
Fquery.sql.text:='SELECT OWNER ,TABLE_NAME,PRIVILEGE '+
'FROM ALL_TAB_PRIVS_RECD '+
'WHERE OWNER ''SYS''';
Fquery.open;
Fquery.first;
while not Fquery.eof do
begin
new(DBObject);
DBObject^.owner:=Fquery.fieldbyname('owner').asstring;
DBObject^.object_name:=Fquery.fieldbyname('table_name').asstring;
DBObject^.privilege:=Fquery.fieldbyname('privilege').asstring;
if not FoundMatchInList(DBObject^.owner,DBObject^.object_name,
DBObject^.privilege) then
LstPrivilege.add(DBObject);
Fquery.next;
end;
end;
function TOraPrivs.GetUSerName: string;
begin
if Fquery.active then
Fquery.close;
Fquery.sql.text:='SELECT USER FROM DUAL';
Fquery.open;
result:=Fquery.fieldbyname('user').asstring;
end;
function TOraPrivs.HadPrivilege(ObjectName: string;
Privilege: TPrivs): boolean;
{
searching user object privilege in the list
return true if the user had object privilege
the parameter you can pass :
- object_name = the name of the object ( case insensitive)
- privilege = pSelect, pInsert, pDelete, pUpdate, pExecute
}
begin
result:=false;
case Privilege of
pSelect : result:=FoundMatch(objectName,'SELECT');
pInsert : result:=FoundMatch(objectName,'INSERT');
pDelete : result:=FoundMatch(objectName,'DELETE');
pUpdate : result:=FoundMatch(objectName,'UPDATE');
pExecute : result:=FoundMatch(objectName,'EXECUTE');
end;
end;
procedure TOraPrivs.Notification(AComponent: TComponent;
Operation: TOperation);
begin
inherited;
case Operation of
opInsert: ; //nothing
opRemove:
begin
if AComponent is TDatabase then Database := nil;
end;
end;
end;
procedure TOraPrivs.Open;
{
opening connection to the database and
creating the list of the object privileges user had
}
begin
if Assigned(FDataBase) then
begin
FQuery.DatabaseName:=FDatabase.databasename;
user:=GetUserName;
GetObjectBelongToUser;
GetObjPrivilegeGrantByOther;
GetAvailableSynonym;
end else
ShowMessage('There is no database???');
end;
procedure TOraPrivs.Refresh;
{
refreshing connection and the list of user's object privileges
}
begin
close;
open;
end;
function TOraPrivs.RetrieveOriginalObjectInList(owner,
object_name: string): Tlist;
var
templist : TList;
DBObjectNew,DBObjectTrace : PDBObject;
i,j : integer;
begin
TempList:=Tlist.create;
j:=LstPrivilege.count-1;
new(DBObjectTrace);
for i:=0 to j do
begin
DBObjectTrace:=LstPrivilege.items[i];
if(AnsiStrIComp(PChar(trim(dbobjectTrace^.owner)),PChar(trim(owner)))=0) and
(AnsiStrIComp(PChar(trim(dbobjectTrace^.object_name)),PChar(trim(object_name)))=0)then
begin
new(DBObjectNew);
DBObjectNew:=DBObjectTrace;
TempList.add(DBObjectNew);
end;
end;
result:=TempList;
end;
procedure TOraPrivs.SetDatabase(const Value: TDatabase);
begin
FDatabase := Value;
end;
end.
]]>