Database Reference
In-Depth Information
SELECT _CATALOG_ROLE
has been granted
HS_ADMIN_SELECT_ROLE
. All of those will be listed as current roles
of the session. If you have additional packages or different versions installed on your Oracle Database,
you may see additional roles related to those packages.
Create an Application Security User
We will need a couple more users to develop and demonstrate our security. The first user is our
Application Security user,
appsec
. She will own all the structures that make our application security
work.
Please take note of the differentiation I am making between Oracle security, which we've delegated
to security administrator, and application security. The security administrator has been granted a
number of system privileges so that he can create users and do other tasks in Oracle. The application
security user, on the other hand, will own a number of procedures and structures that are used for
application security processes. The application security user will not connect as part of any particular
application; rather, she will grant access to her logic and data to application users.
GRANT create_session_role TO appsec IDENTIFIED BY password;
Caution
Be sure to give a very complex password to this user; she does not have significant privileges in the
database, but she owns some logic and data that we do not want to expose to corruption.
Application Security Role
Our application security user needs to create procedures, functions, Java stored procedures, tables, and
views. When creating those items,
appsec
requires the
CREATE PROCEDURE
,
CREATE TABLE
, and
CREATE VIEW
system privileges. We will grant those privileges to a role named
appsec_role
, and grant that role to the
appsec
user:
CREATE ROLE appsec_role NOT IDENTIFIED;
GRANT CREATE PROCEDURE TO appsec_role;
GRANT CREATE TABLE TO appsec_role;
GRANT CREATE VIEW TO appsec_role;
GRANT appsec_role TO appsec;
Non-Default Role
Our application Security user only requires these privileges (for example,
CREATE PROCEDURE
) on
occasion. Our preference would be for her to set her role to
appsec_role
as needed, but currently it is a
default role. In order to change the behavior for roles created as
NOT IDENTIFIED
from being default roles
to be non-default, we need to issue the
ALTER USER
command. In the following command, we ask Oracle
to make all the roles granted to
appsec
user to be
DEFAULT
except this one we want to exclude:
ALTER USER appsec DEFAULT ROLE ALL EXCEPT appsec_role;
Hereafter, when
appsec
connects in order to create new structures, she will have to issue one of the
SET ROLE
commands. (Do not execute these commands as
secadm
user—at this point, they are for
explanation only.)
