1 / 12

User Administration and Security

User Administration and Security. By Lecturer / Aisha Dawood. 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

atalo
Download Presentation

User Administration and Security

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

More Related