Security

Question 1

How do you view users granted permission in the password file?

Answer 1

Identify the users who have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view

Question 2

How do you expand the number of users that can be contained in the password file?

Answer 2

Recreate the file with a larger users parameter and re-grant privs to users.

Question 3

What does the password file grant access to?

Answer 3

The password file maintains a secure list of who has been granted the SYSDBA or SYSOPER privilege and allows them to connect as those users.

Question 4

How to add users to password file:

Answer 4

GRANT SYSDBA (or SYSOPER) to username;
User who is granting must be connected as SYSDBA.

Question 5

How to create a password file.

Answer 5

Use orapwd, like”orapwd file=pswdfile entries=30”.

Set REMOTE_LOGIN_PASSWORDFILE to “None”, “Exclusive” (just this instance) or “Shared” for use across a RAC.

Question 6

External authentication

Answer 6

• No authentication. It’s assumed that is done at the OS level.
• To connect, just use username. There is no password.
• Oracle sees OS accounts with the prefix defined in OS_AUTHENT_PREFIX (OPS$ by default). To create an externally authenticated user, create it with the specified prefix so that Oracle can match it up with the OS account as it appears to Oracle.
• Can set OS_AUTHEN_PREFIX to “” (null string) to negate the use of a prefix.

Question 7

Global Authentication

Answer 7

• No password in DB
• Employs an add-on authentication like x.509, Kerberos or RADIUS.
• CREATE USER user IDENTIFIED GLOBALLY AS …security parameters

Question 8

User Tablespaces

Answer 8

• By default the tablespace assigned to new users is SYSTEM or if created by DBCA, USERS.
• Find out what the default is with SELECT * FROM DATABASE_PROPERTIES. The PROPERTY_NAME is DEFAULT_PERMANENT_TABLESPACE.
• Set another tablespace by using “DEFAULT TABLESPACE tbspc” or “TEMPORARY TABLESPACE temptbspc” in the CREATE USER statement.
• Default temp tbspc for users is TEMP.
• Temp tbspc is like a scratchpad for commands entered by the user where things like results of queries are staged.

Question 9

User Quotas

Answer 9

• By default the quota for usage of a tablespace for a new user is none. Specify a quota either in CREATE or ALTER with “QUOTA 100M ON USERS” where 100M is the size, or enter “UNLIMITED”.
• Enter a quota for each tablespace the user uses (USERS primarily)
• Since 11g R2, “deferred segment creation” allows users to be created without a quota (aka quota of “none”). In this case it does not use space until the first row is created (which errors if the quota is still 0).

Question 10

Default accounts

Answer 10

SYS and SYSTEM are created by default and are the only ones unlocked at DB creation if using DBCA.
- If not using DBCA, be sure to manually lock and expire all the other accounts at DB creation.

Question 11

Kinds of Privileges

Answer 11

• Object privs - operations on tables, packages, etc.
• System privs - operations on the db (altering the DB, connecting, altering users, consuming unlimited amts of tbspc or querying all tables in tbspc).
• Role privs - object or system privs granted via a role.
• Query these with DBA_TAB PRIVS for object and DBA_SYS_PRIVS for system privs.
• DBA_ROLE_PRIVS shows all obj or sys privs granted via role.
• Using “ANY” after a priv name permits that priv on any schema. “ON” means just for what follows “ON”. Just using “GRANT priv” applies it to everything within that user’s schema.

Question 12

Table object privs

Answer 12

• SELECT, INSERT, UPDATE, DELETE

- ALTER, DEBUG, INDEX, REFERENCES

Question 13

View object privs

Answer 13

SELECT, INSERT, UPDATE, DELETE, DEBUG, REFERENCES

Question 14

Sequence object privs

Answer 14

SELECT (CURRVAL or NEXTVAL), ALTER

Question 15

Code object privs

Answer 15

DEBUG, EXECUTE

Question 16

Database sys privs

Answer 16

ALTER DATABASE, ALTER SYSTEM, AUDIT SYSTEM, AUDIT ANY, CREATE PLUGGABLE DATABASE

Question 17

GRANT and REVOKE

Answer 17

GRANT priv1, priv2, privN TO user [WITH AMIN OPTION];

REVOKE priv1, privN FROM user;

Question 18

Role Mgmt

Answer 18

• Grant privs to roles and roles to users.
• Roles assigned to a user as “default roles” do not require to be enabled or logged in to.
• Other roles assigned to a user have to be enabled with a SET ROLE rolename IDENTIFIED BY passwd.
• For groups, use SET ROLE ALL [EXCEPT role_list] or SET ROLE NONE to disable all. No way to selectively disable one role.

Question 19

Enforcing Least Privilege

Answer 19

Use the DBMS_PRIVILEGE_CAPTURE package to identify user privileges that have not been used so you can revoke them.

Question 20

Controlling CPU and Memory Usage with Profiles

Answer 20

• Create with CREATE PROFILE “PROFILE1” LIMIT [parameter list];
• There is a “DEFAULT” profile automatically created with all parameters set to “UNLIMITED”.
• Parameters are:
  CONNECT_TIME [# minutes]
  CPU_PER_CALL [# 100ths of a second of CPU time]
  CPU_PER_SESSION [# 100ths of a second CPU time]
  IDLE_TIME [# minutes]
  LOGICAL_READS_PER_CALL [# typically set >1000]
  LOGICAL_READS_PER_SESSION [#]
  PRIVATE_SGA [# bytes]
  SESSIONS_PER_USER [#]
  COMPOSITE_LIMIT [# srvc units]

Question 21

Controlling Password Security with Profiles

Answer 21

FAILED_LOGIN_ATTEMPTS [#]
PASSWORD_LOCK_TIME [# days to lock after max login failures]
PASSWORD_LIFE_TIME [# days]
PASSWORD_GRACE_TIME [# days]
PASSWORD_REUSE_TIME [# days]
PASSWORD_REUSE_MAX [#]
PASSWORD_VERIFY_FUNCTION [my_function]

Question 22

Auditing (standard)

Answer 22

• By default, OS audit logs are written to the audit_file_dest in Unix systems and to the event viewer in Windows systems.
• Four levels of auditing
  Statement - records triggered by statements
  Privilege - trig by statements that use specific privileges
  Object - trig by statements on specific objects
  Fine-Grained - trig by conditions on data content.