Oracle user functions and security

From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type "password user_name". Look at this example: SQL> password
Changing password for SCOTT
Old password:
New password:
Retype new password:

.....................................................................
How does one create and drop database users?

Look at these examples:

REATE USER scott
IDENTIFIED BY tiger-- Assign password
DEFAULTTABLESACE tools-- Assign space for table and index segments
EMPORARY TABLESPACE temp;-- Assign sort space

DROP USER scott CASCADE;-- Remove user

After creating a new user, assign the required privileges:

GRANT CONNECT, RESOURCE TO scott;
GRANT DBA TO scott; -- Make user a DB Administrator

Remember to give the user some space quota on its tablespaces:

ALTER USER scott QUOTA UNLIMITED ON tools;

.....................................................................
How does one manage Oracle database users?

Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. For example, all accounts except SYS and SYSTEM will be locked after creating an Oracle9iDB database using the DB Configuration Assistant (dbca). DBA's must unlock these accounts to make them available to users. Look at these examples:

ALTER USER scott ACCOUNT LOCK-- lock a user account
ALTER USER scott ACCOUNT UNLOCK;-- unlocks a locked users account

ALTER USER scott PASSWORD EXPIRE;-- Force user to choose a new password

How does one enforce strict password control?

By default Oracle's security is not extremely good. For example, Oracle will allow users to choose single character passwords and passwords that match their names and userids. Also, passwords don't ever expire. This means that one can hack an account for years without ever locking the user. From Oracle8 one can manage passwords through profiles. Some of the things that one can restrict: •FAILED_LOGIN_ATTEMPTS - failed login attempts before the account is locked

•PASSWORD_LIFE_TIME - limits the number of days the same password can be used for authentication

•PASSWORD_REUSE_TIME - number of days before a password can be reused

•PASSWORD_REUSE_MAX - number of password changes required before the current password can be reused

•PASSWORD_LOCK_TIME - number of days an account will be locked after maximum failed login attempts

•PASSWORD_GRACE_TIME - number of days after the grace period begins during which a warning is issued and login is allowed

•PASSWORD_VERIFY_FUNCTION - password complexity verification script

Look at this simple example: CREATE PROFILE my_profile LIMIT
PASSWORD_LIFE_TIME 30;
ALTER USER scott PROFILE my_profile;

.....................................................................
What is an administrative (privileged) user?

Oracle DBAs and operators typically use administrative accounts to manage the database and database instance. An administrative account is a user that is granted SYSOPER or SYSDBA privileges. SYSDBA and SYSOPER allow access to a database instance even if it is not running. Control of these privileges is managed outside of the database via password files and special operating system groups. This password file is created with the orapwd utility.

.....................................................................
How does one connect to an administrative user?

If an administrative users belongs to the "dba" group on Unix, or the "ORA_DBA" (ORA_sid_DBA) group on NT, he/she can connect like this: connect / as sysdba

No password is required. This is equivalent to the desupported "connect internal" method. A password is required for "non-secure" administrative access. These passwords are stored in password files. Remote connections via Net8 are classified as non-secure. Look at this example:

connect sys/password as sysdba

.....................................................................
How does one create a password file?

The Oracle Password File ($ORACLE_HOME/dbs/orapw or orapwSID) stores passwords for users with administrative privileges. One needs to create a password files before remote administrators (like OEM) will be allowed to connect. Follow this procedure to create a new password file:

•Log in as the Oracle software owner

•Run command: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd

•Shutdown the database (SQLPLUS> SHUTDOWN IMMEDIATE)

•Edit the INIT.ORA file and ensure REMOTE_LOGIN_PASSWORDFILE=exclusive is set.

•Startup the database (SQLPLUS> STARTUP)

NOTE: The orapwd utility presents a security risk in that it receives a password from the command line. This password is visible in the process table of many systems. Administrators needs to be aware of this!

.....................................................................
How does one add users to a password file?

One can select from the SYS.V_$PWFILE_USERS view to see which users are listed in the password file. New users can be added to the password file by granting them SYSDBA or SYSOPER privileges, or by using the orapwd utility. GRANT SYSDBA TO scott;

.....................................................................
How does one switch to another user in Oracle?

Users normally use the "connect" statement to connect from one database user to another. However, DBAs can switch from one user to another without a password. Of course it is not advisable to bridge Oracle's security, but look at this example: SQL> select password from dba_users where username='SCOTT';
PASSWORD
-----------------------------
F894844C34402B67

SQL> alter user scott identified by lion;
User altered.

SQL> connect scott/lion
Connected.

REM Do whatever you like...

SQL> connect system/manager
Connected.

SQL> alter user scott identified by values 'F894844C34402B67';
User altered.

SQL> connect scott/tiger
Connected.

Note: Also see the su.sql script in the Useful Scripts and Sample Programs Page.

.....................................................................
Why are OPS$ accounts a security risk in a client/server environment?

If you set REMOTE_OS_AUTHENT=TRUE in your init.ora file, Oracle assumes that the remote OS has authenticated the user.

If REMOTE_OS_AUTHENT is set to FALSE (recommended), remote users will be unable to connect without a password. IDENTIFIED EXTERNALLY will only be in effect from the local host. Also, if you are using "OPS$" as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY.

.....................................................................
Who created all these users in my database?/ Can I drop this user?

Oracle creates a number of default database users or schemas when a new database is created. Below are a few of them:

SYSTEM/MANAGER
The default DBA user name (please do not use SYS)
Created by: ?/rdbms/admin/sql.bsq
Can password be changed: Yes (Do so right after the database was created)
Can user be dropped: NO

OUTLN/OUTLN
Stored outlines for optimizer plan stability
Created by: ?/rdbms/admin/sql.bsq
Can password be changed: Yes (Do so right after the database was created)
Can user be dropped: NO

SCOTT/TIGER, ADAMS/WOOD, JONES/STEEL, CLARK/CLOTH and BLAKE/PAPER.
Training/ demonstration users containing the popular EMP and DEPT tables
Created by: ?/rdbms/admin/utlsampl.sql
Can password be changed: Yes
Can user be dropped: YES - Drop users cascade from all production environments

HR/HR (Human Resources), OE/OE (Order Entry), SH/SH (Sales History).
Training/ demonstration users containing the popular EMPLOYEES and DEPARTMENTS tables
Created by: ?/demo/schema/mksample.sql
Can password be changed: Yes
Can user be dropped: YES - Drop users cascade from all production environments

CTXSYS/CTXSYS
Oracle interMedia (ConText Cartridge) administrator user
Created by: ?/ctx/admin/dr0csys.sql

TRACESVR/TRACE
Oracle Trace server
Created by: ?/rdbms/admin/otrcsvr.sql

DBSNMP/DBSNMP
Oracle Intelligent agent
Created by: ?/rdbms/admin/catsnmp.sql, called from catalog.sql
Can password be changed: Yes - put the new password in snmp_rw.ora file
Can user be dropped: YES - Only if you do not use the Intelligent Agents

ORDPLUGINS/ORDPLUGINS
Object Relational Data (ORD) User used by Time Series, etc.
Created by: ?/ord/admin/ordinst.sql

ORDSYS/ORDSYS
Object Relational Data (ORD) User used by Time Series, etc
Created by: ?/ord/admin/ordinst.sql

DSSYS/DSSYS
Oracle Dynamic Services and Syndication Server
Created by: ?/ds/sql/dssys_init.sql

MDSYS/MDSYS
Oracle Spatial administrator user
Created by: ?/ord/admin/ordinst.sql

AURORA$ORB$UNAUTHENTICATED/INVALID
Used for users who do not authenticate in Aurora/ORB
Created by: ?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql

PERFSTAT/PERFSTAT
Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT
Created by: ?/rdbms/admin/statscre.sql

Remember to change the passwords for the SYS and SYSTEM users immediately after installation! Except for the user SYS, there should be no problem altering these users to use a different default and temporary tablespace.

.....................................................................
What is Fine Grained Auditing?

Fine Grained Auditing (DBMS_FGA) allows auditing records to be generated when certain rows are selected from a table. A list of defined policies can be obtained from DBA_AUDIT_POLICIES. Audit records are stored in DBA_FGA_AUDIT_TRAIL. Look at this example: -- Add policy on table with autiting condition...
execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');
-- Must ANALYZE, this feature works with CBO (Cost Based Optimizer)
analyze table EMP compute statistics;

select * from EMP where c1 = 11; -- Will trigger auditing
select * from EMP where c1 = 09; -- No auditing

-- Now we can see the statments that triggered the auditing condition...
select sqltext from sys.fga_log$;
delete from sys.fga_log$;

.....................................................................
What is Fine Grained Access Control?

See question "What is a Virtual Private Database".

.....................................................................
What is a Virtual Private Database?

Oracle 8i introduced the notion of a Virtual Private Database (VPD). A VPD offers Fine-Grained Access Control (FGAC) for secure separation of data. This ensures that users only have access to data that pertains to them. Using this option, one could even store multiple companies' data within the same schema, without them knowing about it. VPD configuration is done via the DBMS_RLS (Row Level Security) package. Select from SYS.V$VPD_POLICY to see existing VPD configuration.

.....................................................................
What is Oracle Label Security?

Oracle Label Security (formerly called Trusted Oracle MLS RDBMS) uses the VPD (Virtual Private Database) feature of Oracle8i to implement row level security. Access to rows are restricted according to a user's security sensitivity tag or label. Oracle Label Security is configured, controlled and managed from the Policy Manager, an Enterprise Manager-based GUI utility.

Topics

How does one change an Oracle user's password?

How does one create and drop database users?

How does one manage Oracle database users?

How does one enforce strict password control?

What is an administrative (privileged) user?

How does one connect to an administrative user?

How does one create a password file?

How does one add users to a password file?

How does one switch to another user in Oracle?

Why are OPS$ accounts a security risk in a client/server environment?

Who created all these users in my database?/ Can I drop this user?

What is Fine Grained Auditing?

What is Fine Grained Access Control?

What is a Virtual Private Database?

What is Oracle Label Security?

.....................................................................
Back to Oracle DBA Topics

.....................................................................

How does one change an Oracle user's password?

Issue the following SQL command:
ALTER USER

.....................................................................
How does one create and drop database users?

Look at these examples:

CREATE USER scott
IDENTIFIED BY tiger-- Assign password
DEFAULTTABLESACE tools-- Assign space for table and index segments
TEMPORARY TABLESPACE temp;-- Assign sort space

DROP USER scott CASCADE;-- Remove user

After creating a new user, assign the required privileges:

GRANT CONNECT, RESOURCE TO scott;
GRANT DBA TO scott; -- Make user a DB Administrator

Remember to give the user some space quota on its tablespaces:

ALTER USER scott QUOTA UNLIMITED ON tools;

.....................................................................
How does one manage Oracle database users?

ALTER USER scott ACCOUNT LOCK -- lock a user account
ALTER USER scott ACCOUNT UNLOCK; -- unlocks a locked users account

ALTER USER scott PASSWORD EXPIRE; -- Force user to choose a new password

.....................................................................
How does one enforce strict password control?

•FAILED_LOGIN_ATTEMPTS - failed login attempts before the account is locked