The following article explains how to manage users by creating roles, profiles and assigning them privileges on Oracle database 11gR2 RDBMS.
Users access Oracle Database through database user accounts. Some of these accounts are automatically created administrative accounts — accounts with database administration privileges. You log in to these administrative accounts to create and manage other user accounts, maintain database security, and perform other database administration tasks. A user account is identified by a user name and defines the user’s attributes, including the following:
- Password for database authentication
- Privileges and roles
- Profiles for a user
- Default tablespace for database objects
- Default temporary tablespace for query processing work space
When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects that the user creates. The schema name is the same as the user name, and can be used to refer to objects owned by the user.
Overview of Administrative accounts:
Administrative accounts and privileges enable you to perform administrative functions like managing users, managing database memory, and starting up and shutting down the database. The following administrative user accounts are automatically created when you install Oracle Database.
This is the user account that you log in with to perform all administrative functions other than starting up and shutting down the database.
All base tables and views for the database data dictionary are stored in the SYS schema. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator. You must not create any tables in the SYS schema. SYSDBA is a system privilege that is assigned only to user SYS. It enables SYS to perform high-level administrative tasks such as starting up and shutting down the database. if you want to log in as SYS with the SQL Command Line, you must connect to the database “AS SYSDBA”. Connecting AS SYSDBA invokes the SYSDBA privilege.
To change the passwords of administrative user accounts SYS or SYSTEM, use the following commands,
SQL> ALTER USER SYS IDENTIFIED BY newpassword;
SQL> ALTER USER SYSTEM IDENTIFIED BY newpassword;
Where ‘newpassword’ is the desired password for those accounts.
Internal User accounts:
Certain user accounts are created automatically for database administration. Examples are SYS and SYSTEM. Other accounts are automatically created just so that individual Oracle Database XE features or products can have their own schemas. These automatically created accounts are called internal user accounts, and their schemas are called internal schemas. The only internal accounts that you may log in with are the SYS and SYSTEM accounts, although it is recommended that you avoid logging in with the SYS account. Do not attempt to log in with other internal accounts.
The Oracle Password File (stored in $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 the following command,
- Shutdown the database (SQLPLUS> SHUTDOWN IMMEDIATE)
- Edit the parameter file and ensure REMOTE_LOGIN_PASSWORDFILE=exclusive is set.
- Startup the database (SQLPLUS> STARTUP)
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd
Managing User accounts:
1. Creating and Managing users:
You create a database user with the CREATE USER statement. To create a user, you must have the CREATE USER system privilege. Because it is a powerful privilege, a DBA or security administrator is normally the only user who has the CREATE USER system privilege. A user can be created using the following syntax. Even after the user is created, user needs certain privileges to connect and perform database operations which will discussed later.
SQL> CREATE USER rcat IDENTIFIED BY password;
In the above syntax, “rcat” is the username and “password” is the password for that user to login. You can use “ALTER USER” phrase to change a user’s password or “DROP USER” phrase to delete a user account. The syntaxes for both the phrases are described below.
SQL> ALTER USER rcat IDENTIFIED BY new;
SQL> DROP USER rcat;
The first command will change the password of “rcat” user account to “new” and the second will delete user account “rcat” along with its schema. The “ALTER USER” statement can be used to change many other privileges of the user which will discussed shortly. Also you can specify the default tablespace where user created objects are going to be stored and a temporary tablespace for that user while creating a user or even after creation and you can also specify quotas on those tablespaces for that user. The commands to perform those operations are,
SQL> CREATE USER clerk IDENTIFIED BY password DEFAULT TABLESPACE userdata
TEMPORARY TABLESPACE tempx;
The above statement will create a user “clerk” with password as “password” and assigns default tablespace “userdata” to that user and temporary tablespace “tempx”. Similarly quotas can also be granted while creating user using following command,
SQL> CREATE USER clerk IDENTIFIED BY password DEFAULT TABLESPACE userdata
TEMPORARY TABLESPACE tempx QUOTA 100m ON userdata QUOTA 100m on tempx;
Above command will grant 100MB of usable space each on “userdata” and “tempx” tablespaces to the user “clerk”. The same operations can be also done for an already existing user using “ALTER USER” statement which are shown below. Assuming a user “clerk” exists,
SQL> ALTER USER clerk DEFAULT TABLESPACE userdata TEMPORARY TABLESPACE
SQL> ALTER USER clerk QUOTA 100m on userdata;
SQL> ALTER USER clerk QUOTA 100m on tempx;
The above commands alters the user “clerk” and assigns it a default tablespace “userdata” where the users objects are stored and default temporary tablespace as “tempx” and then grants 100m quotas each on those tablespaces. Above operations can be done in a single command shown below.
SQL> ALTER USER clerk DEFAULT TABLESPACE userdata TEMPORARY TABLESPACE tempx QUOTA 100m on userdata QUOTA 100m on tempx;
Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. Commands to perform those operations are mentioned below,
SQL> ALTER USER clerk ACCOUNT LOCK;
SQL> ALTER USER clerk ACCOUNT UNLOCK;
SQL> ALTER USER clerk PASSWORD EXPIRE;
The first command locks the account revoking create session privilege and second command unlocks the account and third command forces the user to change the password.
SQL> ALTER USER clerk QUOTA UNLIMITED ON USERDATA;
2. User privileges:
When creating a user, you grant privileges to enable the user to connect to the database, to run queries and make updates, and to create schema objects. There are two main types of user privileges:
A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type. For example, the privileges to create tables and to delete the rows of any table in a database are system privileges.
An object privilege is a right to perform a particular action on a specific schema object. Different object privileges are available for different types of schema objects. The privilege to delete rows from the DEPARTMENTS table is an example of an object privilege.
NOTE: Object privileges cannot be granted along with system privileges and roles in the same GRANT statement. Basic syntax for granting System and Object level privileges are,
SQL> GRANT CREATE TABLE TO clerk;
SQL> GRANT SELECT ON emp TO clerk;
Where “CREATE TABLE” is a system level privilege and “SELECT” is an object level privilege. Also we are assuming a table ‘emp’ exists. Revoke statement can be used to revoke privileges from a user. The syntax is shown below and the same syntax can be used to revoke object level privileges too.
SQL> REVOKE CREATE TABLE FROM clerk;
A role groups several privileges and roles, so that they can be granted to and revoked from users simultaneously. A role must be enabled for a user before it can be used by the user. Managing and controlling privileges is made easier by using roles, which are named groups of related privileges. You create roles, grant system and object privileges to the roles, and then grant roles to users. Unlike schema objects, roles are not contained in any schema. Oracle provides some predefined roles to help in database administration. These roles, listed in table below, are automatically defined for Oracle databases when you run the standard scripts that are part of database creation. You can grant privileges and roles to, and revoke privileges and roles from, these predefined roles in the same way as you do with any role you define. Below are some default roles and their privileges.
- CONNECT – Includes the following system privileges: ALTER
- RESOURCE – Includes the following system privileges: CREATE SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE,CREATE VIEW CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER,CREATE TYPE
- DBA All system privileges – WITH ADMIN OPTION
- EXP_FULL_DATABASE – Provides the privileges required to perform full and incremental exports which include SELECT ANY TABLE, BACKUP ANY TABLE
- IMP_FULL_DATABASE – Provides the privileges required to perform full and incremental imports.
- DELETE_CATALOG_ROLE – Provides DELETE privilege on the system audit table
- EXECUTE_CATALOG_ROLE – Provides EXECUTE privilege on objects in the data
- DBSNMPAGENT – This role is used by Enterprise Manager/Intelligent
You can add a privilege to an already existing role using following command,
SQL> GRANT CREATE DATABASE LINK TO manager;
To view existing roles, use the following command,
SQL> SELECT * FROM DBA_ROLES;
There are a total of 208 system level privileges and 26 table level privileges. To view all existing
system level privileges,
SQL> SELECT * FROM SYSTEM_PRIVILEGE_MAP;
To view all table level privileges execute following command,
SQL> SELECT * FROM TABLE_PRIVILEGE_MAP;
To view what privileges a role has, execute below command and substitute “rolename” with actual name of the role. You can see if the privilege is granted using ‘ADMIN OPTION’ too here.
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=’rolename’;
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = ‘rolename’;
To view all the table level privileges a role has and on which table, execute the below command and substitute “rolename” with actual name of the role.
SQL> SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE=’rolename’;
The following query returns all object privileges (not including column-specific privileges) granted to the specified user,
SQL> SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
WHERE GRANTEE = ‘clerk’;
To list all the column-specific privileges that have been granted, use the following query,
SQL> SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE FROM
The following query returns all the roles granted to users and other roles,
SQL> SELECT * FROM DBA_ROLE_PRIVS;
The grant command is used to grant system level privileges. System level privileges are those privileges that you need to actually do something on the system. For example you grant system level privileges to a user so that they can connect to the database (needs CREATE SESSION privilege), create objects (needs CREATE TABLE and CREATE INDEX prvileges), perform DBA activities (needs SYSDBA or SYSOPER privilege), alter session related parameters (needs ALTER SESSION privilege). Basically, even if you create a user account, it will not be able to do anything until you have granted it a basic set of privileges. Typically you would always grant the CREATE SESSION privilege so that the user can connect to the database. Without CREATE SESSION privilege user cannot login to the database. The basic syntax for creating a role is,
SQL> CREATE ROLE manager;
SQL> GRANT CONNECT,RESOURCE TO manager;
The above statements will create a new role called ‘manager’ and grants CONNECT and RESOURCE privileges to that role. Now you can grant ‘manager’ to any user to assign CONNECT and RESOURCE privileges to that user. For example, to grant CONNECT and RESOURCE to our user ‘clerk’, we can grant manager as shown in the command below,
SQL> GRANT manager TO clerk;
You can also GRANT privileges or roles to a user while creation of the user using following
SQL> GRANT manager TO clerk IDENTIFIED BY password;
A user or role that is granted a privilege or role, which specifies the WITH ADMIN OPTION clause, has several expanded capabilities:
- The grantee can grant or revoke the system privilege or role to or from any user or other role in the database. Users cannot revoke a role from themselves.
- The grantee can further grant the system privilege or role with the ADMIN OPTION.
- The grantee of a role can alter or drop the role.
For better understanding, in the following statement, the security administrator grants the manager role to clerk with ADMIN OPTION,
SQL> GRANT manager TO clerk WITH ADMIN OPTION;
SQL> GRANT manager TO clerk2; (This will work even if you are logged in as clerk)
The table level or object level privileges can also be granted the same way as system level privileges. As mentioned earlier table level privileges can be checked in “TABLE_PRIVILEGE_MAP” view. But in table level privileges can be granted on individual columns. Some examples of how to grant privileges on columns are shown below. Note that you can also create a role with a set of object level privileges.
SQL> GRANT DELETE ON emp TO clerk;
SQL> GRANT INSERT(name, age) ON emp TO clerk;
SQL> GRANT ALL ON emp TO clerk;
The first command will grant the privilege to delete the table ‘emp’ to the user ‘clerk’. The second command grants the privilege to insert values into ‘name’ and ‘age’ columns of ‘emp’ table to ‘clerk’ (assuming emp table contains name and age columns). The third command grants all the object level privileges on table ‘emp’ to ‘clerk’. There is an option to include ‘GRANT OPTION’ at the end of granting an object level privilege. This is analogous to ‘ADMIN OPTION’ in granting system level privileges. When this option is specified, the user has the privilege to grant the privilege to other users too. An example for this is shown below,
SQL> GRANT INSERT(name, age) ON emp TO clerk WITH GRANT OPTION;
When revoking any system or object level privilege, you must be the GRANTEE of that privilege. Otherwise you cannot revoke that privilege from the user You can revoke any privilege from a role just like you revoke a privilege from a user. The command to do that is mentioned below. The ADMIN OPTION for a system privilege or role cannot be selectively revoked. The privilege or role must be revoked and then the privilege or role re-granted without the ADMIN OPTION. The same is true when GRANT OPTION is granted along with an object level privilege.
SQL> REVOKE RESOURCE FROM manager;
To revoke an object level privilege,
SQL> REVOKE SELECT, insert ON emp FROM clerk;
Or you can also revoke all the object level privileges of a user from a single command shown below. But the same cannot be done with system level privileges.
SQL> REVOKE ALL ON emp FROM clerk;
Although users can grant column-selective INSERT, UPDATE, and REFERENCES privileges for tables and views, they cannot selectively revoke column specific privileges with a similar REVOKE statement. Instead, the grantor must first revoke the object privilege for all columns of a table or view, and then selectively re-grant the column-specific privileges that should remain. The following commands demonstrate that. Assuming ‘clerk’ has privilege to select and update name(column) from emp table and to revoke only update privilege on that column from clerk,
SQL> REVOKE UPDATE ON emp FROM clerk;
SQL> GRANT SELECT ON emp FROM clerk;
Dropping a role also has the effect of revoking that role from any user and role it has been granted to. To drop a role,
SQL> DROP ROLE rolename;
Similarly you can also drop a user using the following command. But the Oracle Database does not drop users whose schemas contain objects unless you specify CASCADE or unless you first explicitly drop the user’s objects.
SQL> DROP USER clerk;
SQL> DROP USER clerk CASCADE; (to drop schema containing objects)
Profiles are used to limit a user’s resource, it can also enforce password management rules, and only the DBA can change profiles. There is a global default profile which every users is assigned to if they are already not assigned to one. This global default profile is created when you run the script “pupbld.sql” while database creation manually. DBCA runs this script automatically at the end of creation. If a user reaches one of the limits in the profile the transaction is rolled back and an error message is displayed stating that a resource limit has been reached.
There are a number of resources that can be limited, as shown below,
- connect_time – If the duration of a session exceeds the elapsed time limit, then the current transaction is rolled back, the session is dropped, and the resources of the session are returned to the system. This limit is set as a number of elapsed minutes.
- cpu_per_call – limits cpu time by any single database call. If a user exceeds a call-level resource limit, then Oracle Database halts the processing of the statement, rolls back the statement, and returns an error. However, all previous statements of the current transaction remain intact, and the user session remains connected.
- cpu_per_session – Limits cpu by session. To prevent uncontrolled use of CPU time, you can set fixed or dynamic limits on the CPU time and the total amount of CPU time used for Oracle Database calls during a session using this parameter.
- idle_time – If the time between calls in a session reaches the idle time limit, then the current transaction is rolled back, the session is terminated, and the resources of the session are returned to the system.
- logical_reads_per_call – caps the amount of work by any single database call
- logical_reads_per_session – To prevent single sources of excessive I/O, you can limit the logical data block reads for each call and for each session. The limits are set and measured in number of block reads performed by a call or during a session.
- private_sga – Limits memory when using shared servers. This limit is set as a number of bytes of memory in the SGA of an instance. Use the characters K or M to specify kilobytes or megabytes.
- sessions_per_user – limits the number of sessions a user can have. Each user can create only up to a predefined number of concurrent sessions.
- composite_limit – calculated by maximum weighted sum of cpu_per_session, connect_time,
- logical_reads_per_session – If this limit is exceeded, Oracle aborts the session and returns an error.
The above parameters are called kernel parameters. The security features that the profile can also manager are,
- failed_login_attemps – if a user attempts to login more than the specified number of times using a wrong password, the account will be locked. Default is 10 wrong attempts, after which the user account will be locked.
- password_lock_time – if the above is breached, this parameter controls number of days an account will remain locked after failed login attempts has been triggered. Default is 1 day.
- password_life_time – number of days the same password can be used unless a grace period is specified. Default is 108 days.
- password_grace_time – number of grace days for user to change password after password life time. Default is 7 days
password_reuse_time – This parameter specifies number of days that must pass before the same password can be used again. Default is unlimited.
password_reuse_max – This parameter specifies number of times a password must be changed before a previous password can be used again. Default is unlimited
- password_verify_function – allows you to define PL/SQL that can be used for password verification.
The above parameters are called password parameters.
To create a profile, you must have the ‘CREATE PROFILE’ system privilege. Also enable resource limits dynamically with the ‘ALTER SYSTEM’ statement shown below or with the initialization parameter “RESOURCE_LIMIT=true” in parameter file. This parameter does not apply to password resources. Password resources are always enabled.
SQL> ALTER SYSTEM SET RESOURCE_LIMIT=’true’;
To view all the parameters you can use for creating a profile, execute,
SQL> SELECT * FROM DBA_PROFILES;
Now create a profile that defines the limits using the ‘CREATE PROFILE’ statement and assign the profile to the user using the ‘CREATE USER’ or ‘ALTER USER’ statement. When specified with a resource parameter, UNLIMITED indicates that a user assigned this profile can use an unlimited amount of this resource. When specified with a password parameter, ‘UNLIMITED’ indicates that no limit has been set for the parameter. Specify ‘DEFAULT’ if you want to omit a limit for this resource in this profile. A user assigned this profile is subject to the limit for this resource specified in the ‘DEFAULT’ profile. The ‘DEFAULT’ profile initially defines unlimited resources. You can change those limits with the ALTER PROFILE statement. Now let us see an example of creating a profile and assigning it to the user ‘clerk’.
SQL> CREATE PROFILE profile LIMIT
In the above example, ‘profile’ is the name of the profile. Assigning this profile to any user lets the user stay connected to the database for unlimited time, In a single session this user can use unlimited amount of CPU resources and the password of the user expires in 30 days after a new password is created. This profile can be granted to a user using the following statement.
SQL> ALTER USER clerk PROFILE profile;
You can alter any existing profile using “ALTER PROFILE” statement. Let us alter the profile ‘profile’ that we created earlier.
SQL> ALTER PROFILE profile LIMIT
This will change the life time of the password to 15 days after which the user needs to set a new password. To permanently drop a profile, use the following statement. This will assign default profile to the users to whom this profile was previously assigned.
SQL> DROP PROFILE <profile_name> CASCADE;