Oracle Database Administration/User Security

This lesson introduces Oracle database user security.

Objectives and Skills

Objectives and skills for the user security portion of Oracle Database Administration I certification include:

• Administering User Security
  • Create and manage database user accounts
  • Grant and revoke privileges
  • Create and manage roles
  • Create and manage profiles

Readings

1. Oracle: Database 2-Day DBA Administering User Accounts and Security

Multimedia

1. YouTube: User, Role, Privileges, Password Policy Administration
2. Oracle Enterprise User Security

Activities

Tutorial

1. Complete the tutorial Oracle: Administering Users and Security.

Database Authentication

1. Review Oracle: Administering Authentication.
2. Display existing users.
   1. Use Enterprise Manager Database Control / Server / Users to display existing users.
   2. Use the following query to describe the DBA_USERS table:
      DESCRIBE DBA_USERS;
   3. Use the following query to display existing users:
      SELECT * FROM DBA_USERS;
3. Add users.
   1. Use Enterprise Manager Database Control / Server / Users to add a new user named USER1 with a password of password.
   2. Use the following command to connect to the server as USER1:
      sqlplus user1/password
   3. Use the following query as SYS or SYSTEM to add a new user named USER2 with a password of password:
      CREATE USER USER2 IDENTIFIED BY password;
   4. Use the following command to attempt to connect to the server as USER2:
      sqlplus user2/password
   5. Use the following query as SYS or SYSTEM to grant a connection to USER2:
      GRANT CREATE SESSION TO USER2;
   6. Use the following command to connect to the server as USER2:
      sqlplus user2/password
4. Grant access to resources.
   1. Review Oracle: Configuring Privilege and Role Authorization.
   2. As USER1 or USER2, attempt to select data from the HR schema using the following query:
      SELECT * FROM REGIONS;
   3. As SYS or SYSTEM, use the following query to grant SELECT access to HR.REGIONS:
      GRANT SELECT ON HR.REGIONS TO USER2;
   4. Use the following query to confirm permissions:
      SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE LIKE 'USER%';
   5. As USER2, select data from the HR schema using the following query:
      SELECT * FROM REGIONS;
5. Modify users.
   1. Use Enterprise Manager Database Control / Server / Users to change the password for USER1 to newpass.
   2. Use the following query to change the password for USER2 to newpass:
      ALTER USER USER2 IDENTIFIED BY newpass;
   3. Use Enterprise Manager Database Control / Server / Users to lock the account for USER1.
   4. Use the following query to lock the account for USER2:
      ALTER USER USER2 ACCOUNT LOCK;
   5. Use the following query to unlock the accounts for USER1 and USER2:
      ALTER USER USER1 ACCOUNT UNLOCK;
ALTER USER USER2 ACCOUNT UNLOCK;

Database Administrator Authentication

1. Review Oracle: Authentication of Database Administrators.
2. Add administrator users.
   1. Add an operating system account for USER1 and USER2. In Windows use Computer Management / Local Users and Groups / Users. In Linux, use useradd.
   2. Log off and log on as USER1 or USER2. Attempt to connect to the database using the following commands:
      sqlplus user1/newpass as sysdba
sqlplus user2/newpass as sysoper
   3. Log off and log on as Administrator. Connect to the database using the following command:
      sqlplus / as sysdba
   4. Use Enterprise Manager Database Control / System / Users to edit USER1 and edit the System Privileges list to add the SYSDBA privilege and select the Admin Option checkbox.
   5. Use the following query to grant SYSOPER privilege to USER2:
      GRANT SYSOPER TO USER2;
   6. Use the following query to confirm the changes:
      SELECT * FROM V$PWFILE_USERS;
   7. Log off and log on as USER1 or USER2. Connect to the database using the following commands:
      sqlplus user1/newpass as sysdba
sqlplus user2/newpass as sysoper
   8. Log off and log on as Administrator. Connect to the database using the following command:
      sqlplus / as sysdba

External Authentication

1. Review Oracle: Administering Authentication.
2. Configure external authentication.
   1. Use the following query to set the Oracle user authentication prefix to an empty string:
      ALTER SYSTEM SET OS_AUTHENT_PREFIX = '' SCOPE = SPFILE;
   2. Use the following query to shutdown and startup the database so that the change takes effect:
      SHUTDOWN TRANSACTIONAL
STARTUP
3. Add external users.
   1. Use the following query to identify the server host name:
      SELECT HOST_NAME from V$INSTANCE;
   2. Use the host name in following query to add a new user named USER3 authenticated by the operating system:
      CREATE USER "<HOST_NAME>\USER3" IDENTIFIED EXTERNALLY;
   3. Use the following query to grant a connection to <HOST_NAME>\USER3:
      GRANT CREATE SESSION TO USER3;
   4. Add an operating system account for USER3. In Windows use Computer Management / Local Users and Groups / Users. In Linux, use useradd.
4. Test external authentication.
   1. Log off and log onto the system as USER3. Use the following command to connect to Oracle as USER3:
      sqlplus /
   2. Log off and log onto the system as Administrator. Use the following command to connect to Oracle as SYS
      sqlplus / as sysdba
5. Delete external users.
   1. Use the following query to delete USER3:
      DROP USER USER3;
   2. Delete USER3 from the system using Computer Management or userdel.

Global Authentication

1. Review Oracle: Global Authentication and Authorization
2. Add global users.
   1. Add a global directory account for USER4. Note the distinguished directory service name for the user.
   2. Add USER4 using the distinguished name in the following query:
      CREATE USER USER4 IDENTIFIED GLOBALLY AS '<distinguished name>';
   3. Use the following query to grant a connection to USER4:
      GRANT CREATE SESSION TO USER4;
3. Test global users.
   1. Log off and log onto the system as USER4. Use the following command to connect to Oracle as USER4:
      sqlplus /
   2. Log off and log onto the system as Administrator. Use the following command to connect to Oracle as SYS:
      sqlplus / as sysdba
4. Delete global users.
   1. Use the following query to delete USER4:
      DROP USER USER4;
   2. Delete the global directory account for USER4.

Roles

1. Create roles.
   1. Review Oracle: Configuring Privilege and Role Authorization.
   2. Use the following queries to create roles for the HR schema:
      CREATE ROLE HR_VIEW;
CREATE VIEW HR_UPDATE;
2. Manage roles.
   1. Use the following queries to grant access to the HR schema roles:
      GRANT CREATE SESSION TO HR_VIEW;
GRANT SELECT ON HR.REGIONS TO HR_VIEW;
GRANT SELECT ON HR.DEPARTMENTS TO HR_VIEW;
GRANT SELECT ON HR.COUNTRIES TO HR_VIEW;
GRANT SELECT ON HR.JOB_HISTORY TO HR_VIEW;
GRANT SELECT ON HR.EMPLOYEES TO HR_VIEW;
GRANT SELECT ON HR.LOCATIONS TO HR_VIEW;
GRANT SELECT ON HR.JOBS TO HR_VIEW;
 
GRANT HR_VIEW TO HR_UPDATE;
GRANT INSERT, UPDATE, DELETE ON HR.REGIONS TO HR_VIEW;
GRANT INSERT, UPDATE, DELETE ON HR.DEPARTMENTS TO HR_VIEW;
GRANT INSERT, UPDATE, DELETE ON HR.COUNTRIES TO HR_VIEW;
GRANT INSERT, UPDATE, DELETE ON HR.JOB_HISTORY TO HR_VIEW;
GRANT INSERT, UPDATE, DELETE ON HR.EMPLOYEES TO HR_VIEW;
GRANT INSERT, UPDATE, DELETE ON HR.LOCATIONS TO HR_VIEW;
GRANT INSERT, UPDATE, DELETE ON HR.JOBS TO HR_VIEW;
 
GRANT HR_VIEW TO USER1;
GRANT HR_UPDATE TO USER2;
   2. Use the following queries to confirm roles and permissions:
      SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE LIKE 'HR_%';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE LIKE 'HR_%';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE LIKE 'USER%';
   3. Connect as USER1 and USER2 and test access for each using the following queries:
      SELECT * FROM REGIONS;
INSERT INTO REGIONS VALUES(5, 'Antarctica');
SELECT * FROM REGIONS;
ROLLBACK;
3. Delete roles.
   1. Use the following queries to remove roles:
      DROP ROLE HR_VIEW;
DROP ROLE HR_UPDATE;
   2. Use the following queries to confirm roles and permissions:
      SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE LIKE 'HR_%';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE LIKE 'HR_%';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE LIKE 'USER%';

Profiles

1. Review Oracle: CREATE PROFILE.
2. View existing profile settings.
   1. Use the following queries to view existing profile settings:
      SELECT USERNAME, PROFILE FROM DBA_USERS;
SELECT * FROM DBA_PROFILES WHERE PROFILE = 'DEFAULT';
3. Create a profile.
   1. Use the following query to create a profile:
      CREATE PROFILE "RESTRICTED" LIMIT
SESSIONS_PER_USER 1
IDLE_TIME 10
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1/24
PASSWORD_GRACE_TIME 5;
4. Assign a profile.
   1. Use the following query to assign the profile to USER1:
      ALTER USER USER1 PROFILE "RESTRICTED";
   2. Attempt to connect as USER1 with multiple concurrent sessions to test the profile.
5. Delete a profile.
   1. Use the following query to delete the RESTRICTED profile:
      DROP PROFILE "RESTRICTED";

Cleanup

1. Delete users.
   1. Use Enterprise Manager to delete USER1.
   2. Use the following query to delete USER2:
      DROP USER USER2;
   3. Use the following query to confirm the changes:
      SELECT * FROM V$PWFILE_USERS;

References

← Data Concurrency

Oracle Database Administration

Database Security →