Administering Database Users - Oracle Database

Creating Database User:

§    Database users are created using the CREATE USER statement.

§   To Create the User, one must have the CREATE USER system privilege (Query DBA_SYS_PRIVS to know whether a USER has CREATE USER system privilege. Please note I am using SYS user to create/alter/drop users in this post)

SQL> select grantee, privilege from dba_sys_privs where grantee = 'SYS' and privilege like '%USER%';

GRANTEE    PRIVILEGE

---------- ----------------------------------------

SYS            DROP USER

SYS            CREATE USER

SYS            ALTER USER

SYS            BECOME USER

§  After new user is created user should be granted with CREATE SESSION system privilege, else the user cannot be able to connect to DB.

A typical user creation statement looks as below

SQL> CREATE USER TestUser IDENTIFIED BY Test

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMPTS1

QUOTA 50M ON USERS;

User created.

Granting CREATE SESSION System privilege to user created above

SQL> GRANT Create Session TO TestUser;

Grant succeeded.

§  One need to mention the default TBs, TEMP TBs with the quotas while creating the user else the default tablespaces which are mentioned during database creation are considered and mapped to the user created.

If quota clause is not mentioned, then unlimited TBs quota is allotted

Note:

a.       Default TBs which are mentioned during database creation (or Alter database set default tablespace statement) can be found by querying the DATABASE_PROPERTIES data dictionary view.

SQL> select property_name, property_value from database_properties where property_name like 'DEFAULT%TABLESPACE';

PROPERTY_NAME                                     PROPERTY_VALUE

------------------------------                      --------------------

DEFAULT_TEMP_TABLESPACE                TEMPTS1

DEFAULT_PERMANENT_TABLESPACE    USERS

b.      To determine the default TBs & Temp TBs of a user created earlier in a database, query DBA_USERS table as below

SQL> select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username = 'SYSTEM';

DEFAULT_TABLESPACE                TEMPORARY_TABLESPACE

------------------------------          ------------------------------

SYSTEM                                            TEMPTS1

c.       To know the usage of the tablespace quota allotted to a user, query the DBA_TS_QUOTAS

select USERNAME, TABLESPACE_NAME,BYTES,(MAX_BYTES/1024)/1024 as MaxBytes_IN_MB from dba_ts_quotas where username = 'TESTUSER';

USERNAME   TABLESPACE_NAME  BYTES       MAXBYTES_IN_MB

----------       --------------------     ----------   --------------

TESTUSER      USERS                                    0                          50

§  You can revoke the ability of a user to create objects in a tablespace by changing the current quota of the user to zero. After a quota of zero is assigned, the user's objects in the tablespace remain, but new objects cannot be created and existing objects cannot be allocated any new space.

§  Assigning a Temporary Tablespace

You can set the temporary tablespace for a user at user creation, and change it later using the ALTER USER statement. Do not set a quota for temporary tablespaces.

SQL>

CREATE USER TestUser1 IDENTIFIED BY Test

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMPTS1

  4  QUOTA 50M ON USERS QUOTA 50M ON TEMPTS1;

CREATE USER TestUser1 IDENTIFIED BY Test

*

ERROR at line 1:

ORA-30041: Cannot grant quota on the tablespace

§  While creating the USER profile also need to be mentioned to limit database resources and password access to the database. If no profile is specified, then the user is assigned a default profile(You can see from above user TestUser is created without mentioning the profile, so default profile called DEFAULT is assigned)

SQL> select profile from dba_users where username = 'TESTUSER';

PROFILE

----------------

DEFAULT

The different resource limits set on the profile – DEFAULT can be known by querying the dictionary table DBA_PROFILES

SQL> select RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT';

RESOURCE_NAME                                           LIMIT

------------------------------                           ----------

COMPOSITE_LIMIT                                        UNLIMITED

SESSIONS_PER_USER                                    UNLIMITED

CPU_PER_SESSION                                        UNLIMITED

CPU_PER_CALL                                               UNLIMITED

LOGICAL_READS_PER_SESSION                 UNLIMITED

LOGICAL_READS_PER_CALL                        UNLIMITED

IDLE_TIME                                                       UNLIMITED

CONNECT_TIME                                              UNLIMITED

PRIVATE_SGA                                                 UNLIMITED

FAILED_LOGIN_ATTEMPTS                          10

PASSWORD_LIFE_TIME                                180

PASSWORD_REUSE_TIME                            UNLIMITED

PASSWORD_REUSE_MAX                             UNLIMITED

PASSWORD_VERIFY_FUNCTION                 NULL

PASSWORD_LOCK_TIME                              1

PASSWORD_GRACE_TIME                           7

16 rows selected.

Note: To know the details of different dictionary tables query DICT

SQL> select * from DICT where Table_name like '%PROFILE%';

TABLE_NAME                                                 COMMENTS

------------------------------                         ------------------------------

DBA_PROFILES                                             Display all profiles and their limits

DBA_SQL_PROFILES                                    set of sql profiles

DBA_SQL_TRANSLATION_PROFILES        Describes all SQL translation profiles in the database

ALL_SQL_TRANSLATION_PROFILES         Describes all SQL translation profiles accessible to the user

USER_SQL_TRANSLATION_PROFILES      Describes all SQL translation profiles owned by the user



Altering Database User:

 §  Users can change their own passwords. However, to change any other option of a user security domain, you must have the ALTER USER system privilege.

Let’s try to change the password of the TestUser created above. Note that the user security changes will come into effect only from the future sessions (not the current session)

[oracle@ol6-12c ~]$ rlsqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 21 08:57:51 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> connect TESTUSER

Enter password:

Connected.

SQL> show user

USER is "TESTUSER"

SQL> alter user TESTUSER identified by password1;

User altered.

No special privileges (other than those to connect to the database) are required for a user to change passwords.

Dropping Database User:

§  If a user schema and associated objects (Tables, Indexes..) must remain but the user must be denied access to the database, then revoke the CREATE SESSION privilege from the user.

§  When a user is dropped, the user and associated schema are removed from the data dictionary and all schema objects contained in the user schema, if any, are immediately dropped. One should be very careful while dropping a user to make sure that implication are clear as in whether any table created by the user is been referenced as foreign key, used in stored procs etc.

§  To drop a connected user, first we need to terminate the user sessions using ALTER SYSTEM with the KILL SESSION clause. A connected user cannot be dropped

Demonstration to show that the connected user cannot be dropped:

To terminal sessions are opened and one connected as SYS & other as TESTUSER. 

While TESTUSER is still connected to database, couldn’t able to drop the user from the SYS user connection. 

Demonstration of using kill session clause in alter system statement to kill the user session which is currently connected to database.

ALTER SYSTEM KILL SESSION 'sid,serial#';

KILL SESSION command just asks the session to kill itself, it doesn’t kill the user session right away.

§  If the user's schema contains any dependent schema objects, then use the CASCADE option to drop the user and all associated objects and foreign keys that depend on the tables of the user successfully. If you do not specify CASCADE and the user schema contains dependent objects, then an error message is returned and the user is not dropped. Before dropping a user whose schema contains objects, thoroughly investigate which objects the user's schema contains and the implications of dropping them. Pay attention to any unknown cascading effects. 

For example, if you intend to drop a user who owns a table, then check whether any views or procedures depend on that particular table