Oracle DBA Notes

Contents

• User Creation
• Administer User Accounts
• Roles
• Profiles
• System Privileges
• Object Privileges
• Security
• View Users
• Troubleshooting

Also see chapter 11 "Database Security Overview for Application Developers" or "Database Security Overview for Application Developers" in the Oracle documentation (...oracle_doc\appdev.920\a96590\adgsec01.htm)

User Creation

Checklist:

• Choose name
• Choose authorization mechanism: password (preferred) or external authentification (using the operating system).
• Determine quotas on tablespaces (by default, no quota), default tablespace (by default, it is SYSTEM, but it cannot be used if no quota is defined), temporary tablespace so that the SYSTEM does not get fragmented.
• Create the user and grant privileges and roles (see below)
• A user owns objects within a schema (tables with triggers and constraints, indexes, views, sequences, stored procedures, synonymes, user defined types, database links)

Connect as user with dba privileges

CREATE USER <new user name> IDENTIFIED BY { <the password> | EXTERNALLY }
  DEFAULT TABLESPACE <a tablespace>
  TEMPORARY TABLESPACE <a temporary tablespace>
  QUOTA { nM | UNLIMITED } ON tablespace QUOTA { nM | UNLIMITED } ON tablespace
  PASSWORD EXPIRE -- v8
  ACCOUNT { LOCK | UNLOCK }
  PROFILE { profile | DEFAULT } ;

GRANT CREATE SESSION TO <new user name> ; -- or grant a role with CREATE SESSION
GRANT <roles> TO <new user name> ;
ALTER USER <new user name> DEFAULT ROLE CONNECT, <roles>;
ALTER USER <new user name> DEFAULT ROLE ALL; -- all roles are default roles
ALTER USER <new user name> QUOTA { 10M | UNLIMITED } ON USERS; -- unlimited cannot be granted to roles
create user OPS$AUSER identified whatever ...  ; -- Linked to operating system
create user OPS$AUSER identified externally ...; -- Idem, password handled by OS

Note that only the first line is compulsory. Best to also define default and temporary tablespaces.
Minimal script for creation:
CREATE USER SCOTT IDENTIFIED BY tiger
   DEFAULT TABLESPACE USERS
   TEMPORARY TABLESPACE TEMP
   PROFILE DEFAULT;
GRANT CREATE SESSION TO SCOTT;

Problems doing what the user should normally be able to do:
alter user system default role all;

To move a schema to a new user: exp user x, imp to user y.

Profiles are used to define resource limits.

Users authenticated by windows (must be member of administrators group, must have the "Logon as a batch job" user right policy.
Assuming parameter os_authent_prefix = OPS$
Set in sqlnet.ora: SQLNET.AUTHENTICATION_SERVICES= (NTS)
create user "OPS$THE_DOMAIN\THE_USER" identified externally -- capital letters
default tablespace usr_data temporary tablespace temp;
grant connect to "OPS$THE_DOMAIN\THE_USER" ;
alter user "OPS$THE_DOMAIN\THE_USER" default role all;

On UNIX, this user can be the oracle account set up during the installation process.

Administer user accounts

ALTER USER username IDENTIFIED BY password;

Force new password
Everyone can change his/her password. SYSTEM can change all. SYS can change SYSTEM. Last chance: connect internal.

ALTER USER username PASSWORD EXPIRE ;

Force user to enter a new password.

ALTER USER username ACCOUNT { LOCK | UNLOCK } ;

Lock or unlock an account

DROP USER username [ CASCADE ] ;

Drop (cascade if a schema)
Note: to block a user from connecting, revoke CREATE SESSION; no need to drop.

ALTER USER username DEFAULT TABLESPACE tablespace ;

Define default tablespace

ALTER USER username TEMPORARY TABLESPACE tablespace ;

Define temporary tablespace

ALTER USER username QUOTA { nM | UNLIMITED } ON tablespace ;

When quota 0 is applied, the user keeps the existing objects but cannot use more space.

Roles

Use roles to manage groups of users. Grant system and object privileges to roles. You can also grant roles to roles.

Group-based security policy: privileges should be granted only to groups and an individual's access should be inherited through the respective security group. "World", "Public" or individual user IDs should not be granted any access.

Default roles include:

• View the data dictionary: SELECT_CATALOG_ROLE. Otherwise, only for user's schema.
• Execute packages and procedures in the data dictionary: EXECUTE_CATALOG_ROLE
• Allow deletion from AUD$: DELETE_CATALOG_ROLE
• CONNECT
• RESOURCE
• DBA
• EXP_FULL_DATABASE
• IMP_FULL_DATABASE

The user group PUBLIC is not a role.

CREATE ROLE <role name> [IDENTIFIED BY ... ];
ALTER ROLE <role name> NOT IDENTIFIED;         -- Disable password

GRANT <priv>, <priv> TO <role name>;
SET ROLE <role name>; -- Set role of the current user
SET ROLE <role name> IDENTIFIED BY <password>; -- Same as previous, but with password
SET ROLE NONE; -- disable roles.

GRANT <role name> TO <user name>;
REVOKE <user name> FROM <role name>;
ALTER USER <user name> DEFAULT ROLE NONE;
ALTER USER <user name> DEFAULT ROLE <role name>;
ALTER USER <user name> DEFAULT ROLE ALL;
ALTER USER <user name> DEFAULT ROLE ALL EXCEPT <role name>;

CREATE ROLE <role name> ;

No identification needed.

CREATE ROLE <role name> IDENTIFIED BY ... ;

Identification is needed when executing "SET ROLE" unless the role is a default role

CREATE ROLE <role name> IDENTIFIED BY <password>;

Provide a password (unless default role). Must be a single-byte character password.

CREATE ROLE <role name> IDENTIFIED USING <package name>;

The role can be enabled by any module in the package.

CREATE ROLE <role name> IDENTIFIED EXTERNALLY;

External authorization (operating system, network)

CREATE ROLE <role name> IDENTIFIED GLOBALLY;

Enterprise directory service. Note that only the enterprise directory service can then grant and revoke these roles.

Profiles

create profile <profile name> limit FAILED_LOGIN_ATTEMPTS 5;
alter profile DEFAULT limit idle_time 60;
alter profile <profile name> limit PASSWORD_REUSE_MAX 3 PASSWORD_REUSE_TIME UNLIMITED;

create user <user name> identified by whatever profile <profile name>;

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION <function name>;

create profile temp_profile limit
password_verify_function    null
password_reuse_time         unlimited
password_reuse_max          unlimited;

System Privileges

Some comments:

• System privileges at system level, object priveleges for objects; these privileges can be granted to roles.
• Full access to data dictionary: SELECT ANY DICTIONARY
• "ANY": use in any schema.
• Create table, procedure and cluster allows dropping too. Create table allows analyze and creation of index. To truncate a table, the privilege DROP ANY TABLE is needed.
• See system_privilege_map or administrator's guide, chapter 21.
• Note that ADMIN OPTION is powerful, because granted privileges stay after the grantor has lost the privileges.
• System operator privilege includes startup, shutdown, restricted session, open/mount, backups. The number of users to whom system privileges can be granted is limited by the parameter ENTRIES defined when creating the password file.
  (connect as sysdba)
  GRANT SYSOPER TO user1;
SELECT * FROM V$PWFILE_USERS; -- Check password file
  SELECT * FROM DBA_SYS_PRIVS; -- View all granted priveleges
• Note that if the parameter 07_dictionary_accessiblity=false, then there is less visibilty in the data dictionary (can't see other schemas).

GRANT  { system_priv | role } TO   { user | role | PUBLIC } [ WITH ADMIN OPTION ] ;
REVOKE { system_priv | role } FROM { user | role | PUBLIC };

Examples

GRANT CREATE SESSION, CREATE TABLE TO user1;

user can connect and create tables

REVOKE CREATE TABLE FROM user1;

User can no longer create tables

Object Privileges

GRANT { SELECT or INSERT or UPDATE or DELETE } ON table, view or sequence.
GRANT EXECUTE ON procedure.
GRANT ALTER, INDEX and REFERENCES ON other objects, but these are not used (or even dangerous to grant to others in some cases).

Owner can grant privileges (must be my schema) or I must have WITH GRANT OPTION. A user has all privileges on an object that he/she owns.
Note that WITH GRANT OPTION does not work with roles.

GRANT <priv> [(column, column)] , <priv>
ON object TO { user | role | PUBLIC }, { same } [ WITH GRANT OPTION ] ;

Grant privileges

GRANT ALL PRIVILEGES ON . . .

Grant all privileges received

REVOKE { { priv, priv } | ALL }
ON object FROM { user | role | PUBLIC }, { same } [ CASCADE CONSTRAINTS ] ;

Revoke

 

Security

Authentication methods: database passwords, host operating system, network services or Secure Sockets Layer (SSL).

Encrypt passwords (not yet tried):
Environment variable ORA_ENCRYPT_LOGIN = TRUE on client machine.
Set server initialization parameter DBLINK_ENCRYPT_LOGIN = TRUE.

Basic security:

• Install minimum of required applications
• Lock and expire default user accounts (except SYS, SYSTEM, SCOTT, DBSNMP, OUTLN and the three JSERV users)
• Change default passwords
• O7_DICTIONARY_ACCESSIBILITY = FALSE
• Grant the miminal privileges in the database, restrict access on the operating system, protect netword with firewalls, require password for listener (ADMIN_RESTRICTIONS_listener_name = ON) but my experience is that this makes automatic administration of listener more difficult
• It is suggested to set REMOTE_OS_AUTHENT = FALSE
• Apply security patches and workaroungs.

View users

dba_users

View all users

all_users

View users

user_users

View information on current user (one row)

DBA_TS_QUOTAS
USER_TS_QUOTAS

Tablespace quotas

USER_PASSWORD_LIMITS
USER_RESOURCE_LIMITS
RESOURCE_COST
DBA_PROFILES

Profile management

V$SESSION

Session information

V$SESSTAT
V$STATNAME

Session statistics (table statname shows names of statistics)

PROXY_USERS

View users that who can assume other identities

DBA_COL_PRIVS, ALL_COL_PRIVS, USER_COL_PRIVS
ALL_COL_PRIVS_MADE, USER_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD, USER_COL_PRIVS_RECD

Column object grants (compared to tab_privs, contains one column more: "column_name")

DBA_TAB_PRIVS, ALL_TAB_PRIVS, USER_TAB_PRIVS
ALL_TAB_PRIVS_MADE, USER_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD, USER_TAB_PRIVS_RECD

Object grants (compared to col_privs, contains one column more: "hierarchy")

DBA_SYS_PRIVS, USER_SYS_PRIVS

Granted system privileges

DBA_ROLES

all roles in database

DBA_ROLE_PRIVS, USER_ROLE_PRIVS

Granted roles

ROLE_ROLE_PRIVS

Roles granted to roles (sub-set of dba_role_privs)

ROLE_SYS_PRIVS

System privileges granted to roles (sub-set of dba_sys_privs)

ROLE_TAB_PRIVS

Object privileges granted to roles (sub-set of dba_tab_privs)

SESSION_PRIVS, SESSION_ROLES

List privileges and roles grantes to user in current session

Troubleshooting

Check user privileges:
select user, privilege from session_privs;
select user, role from session_roles;

This is my secret. Down to level 5.