Oracle Database Security - Expert Oracle and Java Security - page 7

Working as the SYS User
SYS has a schema (by the same name) and has lots of structures belonging to his account. You can
connect (log in) to a database as SYS , but a unique requirement of that user is that you have to specify
that you are using the SYSDBA privilege. (Note that in the text I will be using all uppercase for reserved
words in Oracle commands and for default Oracle users, schemas, and structures.)
CONNECT sys AS sysdba;
At that point, you will have to enter the password associated with the SYS user.
 Note You can find a script of the following commands in the file named Chapter2/Sys.sql .
System Privileges
SYSDBA is a unique system privilege; let's call it a “super” system privilege. It provides practically
unlimited administrative ability. It can be granted to any user, but is best left granted only to SYS . As SYS
you can check who has SYSDBA :
SELECT * FROM sys.v$pwfile_users;
With this query, you request to SELECT (see) all the columns of data (*) from the V$PWFILE_USERS
view. The sys. prefix on that view name indicates the schema (user) that owns the view. You will see a
couple of other super system privileges listed in this query: SYSOPER and SYSASM . These should also only
be granted to SYS .
Besides the super system privileges, there are several dozen other system privileges that can be
granted to provide such administrative abilities as creating other users and granting them privileges. We
will be granting privileges as we explore Oracle security.
In addition to system privileges, there are schema object privileges. These allow, for example,
another user to read your data. We will grant these privileges also. They have a much more tailored
impact on Oracle application security.
Roles
Roles are collections of granted privileges that are much more granular and diverse and limited than the
super system privileges. Instead of granting SYSDBA , the preferred method for giving users access to the
various privileges is to grant the privileges to a role, then grant the role to the user. If you do this, you can
always substitute a different user in a job, or replicate the privileges required for a job to another user by
simply granting the existing role to the new user. (Privileges can be granted directly to users, but we will
mostly avoid that.)
For example, I can create a role named appaccess and grant the ability to read the application tables
to that role. Then I can grant that role to a user. When I have another user who needs access to the same
application, I can permit that by granting the appaccess role to the new user as well. And when I delete
the first user, the permissions required for other users to access the application do not disappear in the
process.
I need to grant access to the application data as described previously, because the data tables will be
in a schema belonging to the application, and each user will have her own schema. Users cannot read