Managing Users

1. Managing Users

2. Objectives • Creating new database users • Altering and dropping existing database users • Monitoring information about existing users

3. Users and Security Default tablespace Accountlocking • DBA defines users who can access db • Security domain defines the settings that apply to users Authentication mechanism Temporary tablespace Security domain Role privileges Tablespacequotas Direct privileges Resource limits

4. Database Schema (Some of the objects a user can own) Tables - Triggers - Constraints Indexes Views Sequences Stored program units Synonyms User-defined data types Database links • Schema: named collection of objects like tables, views, procedures, etc. • When a user is created a schema with same name is created • Hence username and schema name used interchangeably

5. Checklist for Creating Users (Developers not end users) • 1. Choose a username and authentication mechanism. • 2. Identify tablespaces in which the user needs to store objects. • 3. Decide on quotas for each tablespace. • 4. Assign default tablespace and temporary tablespace. • 5. Create a user. • 6. Grant privileges and roles to the user.

6. Creating a New User: Server Authentication Set the initial password: CREATE USER peter IDENTIFIED BY my1stson DEFAULT TABLESPACE data01 TEMPORARY TABLESPACE temp QUOTA 15m ON data01 PASSWORD EXPIRE; Expires at login forcing user to change password

7. Creating a New User: Operating System Authentication (User logs on to machine running Oracle server) Use OS_AUTHENT_PREFIX (in parameter file) Example: O/S User = user15; OS_AUTHENT_PREFIX OS_empty string““ OPS$(default) Database User OS_USER15USER15 OPS$USER15(default) Remote LoginPossible Create Oracle User: OS_user15 No No Yes

8. Creating a New User: Operating System Authentication (User logs on to machine running Oracle server) • E.g., An OS user tikekarr; • Use IDENTIFIED EXTERNALLY clause with create user • Also exists as a database user • Oracle will not validate • To use sql*plus say • Sqlplus /

9. Creating a New User: Guidelines • Choose a standard password initially;use O/S authentication sparingly. • Use the EXPIRE keyword to force users to reset their passwords. • Always assign temporary tablespace. • Restrict quotas to few users;use QUOTA UNLIMITED with caution. • Educate users: • To connect • To change password

10. Controlling Account Lock and Password ALTER USER peter IDENTIFIED BY hisgrandpa PASSWORD EXPIRE;

11. Changing User Quota on Tablespace ALTER USER peter QUOTA 0 ON data01; • To get a user out of system (fired/resigned): • Use password expiration • Lock account • Alter password • Change profile • Export/import user schema elsewhere

12. Dropping a User DROP USER peter; • Use the CASCADE clause if theschema contains objects. DROP USER peter CASCADE; User currently connected cannot be dropped

13. Monitoring Users DBA_USERS USERNAME USER_ID CREATED ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE DBA_TS_QUOTAS USERNAME TABLESPACE_NAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS

14. Monitoring Users Select tablespace_name, blocks, max_blocks, bytes, max_bytes From dba_ts_quota Where username = ‘SCOTT’; -1 in MAX_BLOCKS or MAX_BYTES indicates unlimited quota Select username, account_status, temporary_tablespace From dba_users; -lists all users, their account status and temp. ts

15. Summary • Creating users specifying the appropriate password mechanism • Controlling usage of space by users