Release 3.00
Nov 09, 2002
|
Sybernet / Supplied Packages Reference
Release 3.00 Nov 09, 2002 |
|
The SECURITY package provides subprograms for interrogating roles. While Oracle normally takes care of this there are times when you may want to interrogate whether or a not a user belongs to a particular role or whether a user has select permission on a table. A user can select from a table if they were granted select on that object (of course), but a user can also select from a table if they belong to a role that has this privilege. The role itself might not have this privilege, but instead belongs to a role that does. The SECURITY package handles these cases.
| Subprogram | Description |
|---|---|
GRANTED_BY_NAME function |
Determines if a username or role has access to a object. |
GRANTED_BY_ROLE function |
Determines if a username or role is associated with a role that has access. |
GRANTED_TO_NAME function |
Determines if a username can access this object. |
GRANTED_BY_WHOM function |
Retrieves the reason why a username or role can access an object. |
GRANTED_TO_ROLE function |
Determines if a username or role is granted to another role. |
ROLES function |
Retrieves all roles associated with this username. |
This function determines if the username or role name passed to this function has permission to access this object. granted_by_name is not recursive. This means it only returns true if the username passed to this function has direct access to this object.
HTTP.SECURITY.GRANTED_BY_NAME
(
OBJECT_NAME in varchar2
, USERNAME in varchar2 := USER
, PRIVILEGE in varchar2 := 'EXECUTE'
)
RETURN BOOLEAN;
| Parameter | Description |
|---|---|
object_name |
The object name to interrogate. |
username |
The username to interrogate. |
privilege |
The privilege that you are interested in. Privilege is one of
DELETE
EXECUTE (default)
INSERT
REFERENCES
SELECT
UPDATE
|
GRANTED_BY_NAME returns TRUE if this username was granted direct access to this object.
This following example illustrates how to call granted_by_name and determine if username E14568 has direct access to this function:
begin
if http.security.granted_by_name('TOUCHABLE','E14568','EXECUTE') then
http.writeln('TRUE');
else
http.writeln('FALSE');
end if;
end;
If the example above returned false, it does not mean that E14568 cannot execute this function because the function might be granted to PUBLIC or a role that E14568 belongs to. Here is how to determine if PUBLIC can execute this function:
begin
if http.security.granted_by_name('TOUCHABLE','PUBLIC','EXECUTE') then
http.writeln('TRUE');
else
http.writeln('FALSE');
end if;
end;
This function calls granted_by_name recursively for each role that username belongs to. If direct access was granted to this user or access was granted to public, then granted_by_role will usually return false. The function will return TRUE if the username belongs to a role that was granted this access.
HTTP.SECURITY.GRANTED_BY_ROLE
(
OBJECT_NAME in varchar2
, USERNAME in varchar2 := USER
, PRIVILEGE in varchar2 := 'EXECUTE'
)
RETURN BOOLEAN;
| Parameter | Description |
|---|---|
object_name |
The object name to interrogate. |
username |
The username to interrogate. |
privilege |
The privilege that you are interested in. Privilege is one of
DELETE
EXECUTE (default)
INSERT
REFERENCES
SELECT
UPDATE
|
GRANTED_BY_ROLE returns TRUE if this username was granted access to this object by a role.
Ths following example illustrates how to call granted_by_role:
begin
if http.security.granted_by_role('TOUCHABLE','E14568','EXECUTE') then
http.writeln('TRUE');
else
http.writeln('FALSE');
end if;
end;
This function combines granted_by_name and granted_by_role into one function. The point here is that you are interested in whether or not a username can access this object. You do not care whether this is a direct access (because a grant was applied to this username) or the object was granted to public or the username belongs to a role that has this access.
HTTP.SECURITY.GRANTED_TO_NAME
(
OBJECT_NAME in varchar2
, USERNAME in varchar2 := USER
, PRIVILEGE in varchar2 := 'EXECUTE'
)
RETURN BOOLEAN;
| Parameter | Description |
|---|---|
object_name |
The object name to interrogate. |
username |
The username to interrogate. |
privilege |
The privilege that you are interested in. Privilege is one of
DELETE
EXECUTE (default)
INSERT
REFERENCES
SELECT
UPDATE
|
GRANTED_TO_NAME returns TRUE if this username was granted access to this object.
Ths following example illustrates how to call granted_to_name. granted_to_name returns true if this username can execute this procedure from Oracle:
declare
object_name varchar(30) := 'SP_HTML_ISQL';
username varchar(30) := 'E14568';
privilege varchar(30) := 'EXECUTE';
begin
if http.security.granted_to_name(object_name,username,privilege) then
http.writeln('TRUE (' || security.granted_by_whom(object_name,username,privilege) || ')');
else
http.writeln('FALSE');
end if;
end;
The granted_by_whom function returns the role that allows this username to access this object. The result can be PUBLIC if access was granted to public, a role name if this username belongs to a role that allows this access, or the username if direct access was granted on this object.
HTTP.SECURITY.GRANTED_BY_WHOM
(
OBJECT_NAME in varchar2
, USERNAME in varchar2 := USER
, PRIVILEGE in varchar2 := 'EXECUTE'
)
RETURN BOOLEAN;
| Parameter | Description |
|---|---|
object_name |
The object name to interrogate. |
username |
The username to interrogate. |
privilege |
The privilege that you are interested in. Privilege is one of
DELETE
EXECUTE (default)
INSERT
REFERENCES
SELECT
UPDATE
|
GRANTED_BY_WHOM returns the role that granted access to this object. NULL is returned if no access is provided.
Ths following example illustrates how to call granted_by_whom:
declare
object_name varchar(30) := 'SP_HTML_ISQL';
username varchar(30) := 'E14568';
privilege varchar(30) := 'EXECUTE';
begin
if http.security.granted_to_name(object_name,username,privilege) then
http.writeln('TRUE (' || security.granted_by_whom(object_name,username,privilege) || ')');
else
http.writeln('FALSE');
end if;
end;
This function allows you to determine if username belongs to a role. This is complicated by the fact that roles can be granted to roles. Suppose E14568 belongs to role_1 and role_2 belongs to role_3. Asking if E14568 belongs to role_3 will return TRUE because it does.
HTTP.SECURITY.GRANTED_TO_ROLE
(
ROLENAME in varchar2
, USERNAME in varchar2 := USER
)
RETURN BOOLEAN;
| Parameter | Description |
|---|---|
rolename |
The role name to interrogate. |
username |
The username to interrogate. |
GRANTED_TO_ROLE returns TRUE if a username was granted to a role.
Ths following example illustrates how to call granted_to_role:
begin
if http.security.granted_to_role('ROLE_3','E14568') then
http.writeln('TRUE');
else
http.writeln('FALSE');
end if;
end;
The following example illustrats how to create buttons based on whether or not a role was granted to this user. In this example, the role to search for is the text "UPDATE_" prepended to your procedure name:
CREATE OR REPLACE procedure SP_HTML_INPUT
(
procname IN VARCHAR2
, type IN VARCHAR2
, value IN VARCHAR2 := NULL
, name IN VARCHAR2 := NULL
, onClick IN VARCHAR2 := NULL
, confirm in VARCHAR2 := NULL
)
is
begin
if http.security.granted_to_role('UPDATE_' || http.object_name(http.object_id(procname)),USER) then
/*
** Success!
*/
http.sp_html_input
(
type => type
, name => name
, value => value
, onClick => onClick
, confirm => confirm
) ;
else
/*
** Oops!
"*/
http.sp_html_input
(
type => 'BUTTON'
, value => value
, onClick => 'alert(''Oops!\n \nYou have read-only access only. To change your access contact Howard Nixon.'');'
) ;
end if;
return;
end;
This function returns a sorted (comma separated) list of all roles available to this username.
HTTP.SECURITY.ROLES
(
USERNAME in varchar2 := USER
)
RETURN BOOLEAN;
| Parameter | Description |
|---|---|
username |
The username to interrogate. |
ROLES returns a comma separated list of all roles belonging to this user.
Ths following example illustrates how to call roles:
http.writeln(http.security.roles('E14568'));