User Administration and Security

1. User Administrationand Security By Lecturer / Aisha Dawood

2. User Administration and Security • Administering Users • Create and manage database user accounts. • Create and manage roles. • Grant and revoke privileges. • Control resource usage by users. • Oracle Database Security • Apply the principle of least privilege. • Manage default user accounts. • Implement standard password security features. • Audit database activity. • Register for security updates.

3. Creating and Managing User Accounts • One of the most basic administrative requirements for a database is to identify the users. Each user who connects to your database should have an account. Shared accounts are difficult to troubleshoot and audit , and having them are a poor security practice. • You create a new database account with the CREATE USER statement. When you create a new account, at a minimum, you must assign a unique username and authentication method. • You can optionally assign additional attributes to the user account with the CREATE USER statement. • To change or assign new attributes to an existing user account, use the ALTER USER statement.

4. Configuring Authentication • When you connect to an Oracle database instance, your user account must be authenticated. • Authentication involves validating the identity of the user and confirming that they have the authority to use the database. • Oracle offers three authentication methods for your user accounts: password authentication (the most common), external authentication, and global authentication.

5. Configuring Authentication • Password Authenticated Users • When a user with password authentication attempts to connect to the database, the database verifies that the username is a valid database account and that the password supplied matches that user’s password as stored in the database. • Password authenticated user accounts are the most common and are sometimes referred to as database authenticated accounts. With a password authenticated account, the database stores the encrypted password in the data dictionary. • For example, to create a password authenticated user named rajesh with a password of welcome , you execute the following: CREATE USER rajesh IDENTIFIED BY welcome; • The keywords IDENTIFIED BY password (in this case, password is welcome ) tell the database that this user account is a password authenticated account.

6. Configuring Authentication • Externally Authenticated Users • When an externally identified user attempts to connect to the database, the database verifies that the username is a valid database account and trusts that the operating system has performed authentication. • Externally authenticated user accounts do not store or validate a password in the database. These accounts are sometimes referred to as OPS$ (pronounced ahps dollar ) accounts. • you can configure this OS_AUTHENT_PREFIX in the initialization or SPFILE file. • For example, to create an externally authenticated user named oracle , using the default OS_AUTHENT_PREFIX, you execute the following: CREATE USER ops$oracle IDENTIFIED EXTERNALLY; • The keywords IDENTIFIED EXTERNALLY tell the database that this user account is an externally authenticated account.

7. Configuring Authentication • Globally Authenticated Users • When a globally identified user attempts to connect to the database, the database verifies that the username is valid and passes the connection information to the advanced security option for authentication. • Globally authenticated user accounts do not store or validate a password in the database as a password authenticated account does. • These accounts rely on authentication provide by a service supported through the advanced security option. • The syntax for creating a globally authenticated account depends on the service called, but all use the keywords IDENTIFIED GLOBALLY , which tell the database to engage the advanced security option for authentication. Here is an example: CREATE USER spy_master IDENTIFIED GLOBALLY AS 'CN=spy_master, OU=tier2, O=security, C=US';

8. Assigning a Default Tablespace • Every user is assigned a default tablespace. The default tablespace for a user is that tablespace where schema objects are stored when no TABLESPACE clause is given in statements that create tables or indexes. • If you execute a CREATE TABLE statement and do not explicitly specify a tablespace, the database uses your default tablespace. • Use the keywords DEFAULT TABLESPACE tablespace_name to assign a default tablespace to either a new user via a CREATE USER statement or an existing user, like this: CREATE USER rajesh IDENTIFIED BY welcome DEFAULT TABLESPACE users; • Or via an ALTER USER statement: ALTER USER rajesh DEFAULT TABLESPACE users; • To change the database default tablespace (the value that users inherit if no default tablespaceis provided), use the ALTER DATABASE statement, like this: ALTER DATABASE DEFAULT TABLESPACE users;

9. Assigning a Temporary Tablespace • Every user is assigned a temporary tablespace in which the database stores temporary segments. • Temporary segments are created during large sorting operations, such as ORDER BY, GROUP BY, SELECT DISTINCT, MERGE JOIN, or CREATE INDEX. • Temporary segments are also used when a temporary table is used. The database creates and drops temporary segments transparently to the user. • You must use a dedicated tablespace of type TEMPORARY for your user’s temporary tablespace setting.

10. Assigning a Temporary Tablespace • If you do not explicitly assign a temporary tablespace at user creation time, the database assigns the database default temporary tablespace to the new user account. • Use the keywords TEMPORARY TABLESPACE tablespace_name to assign a temporary tablespace either to a new user via the CREATE USER statement: CREATE USER rajesh IDENTIFIED BY welcome DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; • Or to an existing user via an ALTER USER statement: ALTER USER rajesh TEMPORARY TABLESPACE temp; • To change the database default temporary tablespace, use the ALTER DATABASE statement, like this: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

11. Assigning a Profile to a User • In addition to default and temporary tablespaces, every user is assigned a profile. A profile serves two purposes: first, it can limit the resource usage of some resources, and second, it can enforce password-management rules. • The default profile is appropriately named default. To explicitly assign a profile to a user, include the keywords PROFILE profile_name in the CREATE USER or ALTER USER statement. • For example, to assign the profile named resource_profile to the new user jiang as well as to the existing user hamish, execute the following SQL: CREATE USER jiang IDENTIFIED BY "kneehow.ma" DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp PROFILE resource_profile; ALTER USER hamish PROFILE resource_profile;

12. Removing a User from the Database • You use the DROP USER statement to remove a user from the database. You can optionally include the keyword CASCADE to tell the database to recursively drop all objects owned by that user. • To drop both user rajesh and all objects he owns, execute the following: DROP USER rajesh CASCADE; • Dropping a user implicitly drops any object (but not role or system) privileges in which the user was the grantor. • The data dictionary records both grantee and grantor for object privileges, but only the grantee is recorded for role and system privileges.