# 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

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

## Multimedia

* YouTube: User, Role, Privileges, Password Policy Administration
* Oracle Enterprise User Security

## Activities
### Tutorial

* Complete the tutorial Oracle: Administering Users and Security.

### Database Authentication

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

* Review Oracle: Authentication of Database Administrators.
* Add administrator users.
** Add an operating system account for USER1 and USER2. In Windows use Computer Management / Local Users and Groups / Users. In Linux, use useradd.
** 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
** Log off and log on as Administrator. Connect to the database using the following command: sqlplus / as sysdba
** 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.
** Use the following query to grant SYSOPER privilege to USER2: GRANT SYSOPER TO USER2;
** Use the following query to confirm the changes: SELECT * FROM V$PWFILE_USERS;
** 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
** Log off and log on as Administrator. Connect to the database using the following command: sqlplus / as sysdba

### External Authentication

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

### Global Authentication

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

### Roles

* Create roles.
** Review Oracle: Configuring Privilege and Role Authorization.
** Use the following queries to create roles for the HR schema: CREATE ROLE HR_VIEW; CREATE VIEW HR_UPDATE;
* Manage roles.
** 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;
** 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%';
** 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;
* Delete roles.
** Use the following queries to remove roles: DROP ROLE HR_VIEW; DROP ROLE HR_UPDATE;
** 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

* Review Oracle: CREATE PROFILE.
* View existing profile settings.
** Use the following queries to view existing profile settings: SELECT USERNAME, PROFILE FROM DBA_USERS; SELECT * FROM DBA_PROFILES WHERE PROFILE = 'DEFAULT';
* Create a profile.
** 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;
* Assign a profile.
** Use the following query to assign the profile to USER1: ALTER USER USER1 PROFILE "RESTRICTED";
** Attempt to connect as USER1 with multiple concurrent sessions to test the profile.
* Delete a profile.
** Use the following query to delete the RESTRICTED profile: DROP PROFILE "RESTRICTED";

### Cleanup

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