CERTIFICATION OBJECTIVES
When a user logs on to the database, following some means of identification they connect to a user account, which defines their initial access permissions and the attributes of the session. Associated with a user account is a schema. The terms user, user account, and schema can often be used interchangeably in the Oracle environment, but they are not always the same thing. A user is a person who connects to a user account by establishing a session against the instance and logging on with the user account name. A schema is a set of objects owned by a user account.
A user account must be granted privileges before a session (or sessions) connected to the account can do anything. Many different privileges can be granted for many different objects and actions, and to manage privileges individually is not practical for any but the simplest systems. Privileges are usually grouped into roles, which make privilege administration much easier.
Finally, this chapter looks at profiles. Profiles can be used to manage passwords and (to a limited extent) control the resources a user is allowed to take up within the instance and the database.
CERTIFICATION OBJECTIVE 8.01
Create and Manage Database User Accounts
To establish a session against an instance and a database, a user must connect to a user account. The account must be specified by name and authenticated by some means. The way the account was created will set up a range of attributes for the session, some of which can be changed later while the session is in progress.
User Account Attributes
A user account has a number of attributes defined at account creation time. These will be applied to sessions that connect to the account, although some can be modified by the session or the DBA while the session is running. These attributes are as follows:
Username
Authentication method
Default tablespace
Tablespace quotas
Temporary tablespace
User profile
Account status
All of these should be specified when creating the user, although only username and authentication methods are mandatory; the others have defaults.
Username
The username must be unique in the database and must conform to certain rules. A username must begin with a letter, must be no more than 30 characters, and can consist of only letters, digits, and the dollar sign ($) and underscore (_) characters. A username may not be a reserved word. The letters are case sensitive but will be automatically converted to uppercase. All these rules (with the exception of the length) can be broken if the username is specified within double quotes, as shown on Figure 8-1.
FIGURE 8-1 How to create users with nonstandard names
In the first example in the figure, the username JOHN is created. This was entered in lowercase, but will have been converted to uppercase, as can be seen in the first query. The second example uses double quotes to create the user with a name in lowercase. The third and fourth examples use double quotes to bypass the rules on characters and reserved words; both of these would fail without the double quotes. If a username includes lowercase letters or illegal characters or is a reserved word, then double quotes must always be used to connect to the account subsequently.
It is possible to use nonstandard usernames, but this may cause dreadful confusion. Some applications rely on the case conversion; others always use double quotes. It may be considered good practice always to use uppercase and only the standard characters; this means that double quotes can be used or not.
Default Tablespace and Quotas
Every user account has a default tablespace, which is the tablespace where any schema objects (such as tables or indexes) created by the user will reside. It is possible for a user to own objects in any tablespace on which he has been given a quota, but unless another tablespace is specified when creating the object, it will go into the user’s default tablespace.
A database-wide default tablespace will be applied to all user accounts if a default tablespace is not specified when creating the user. The default can be set when creating the database and then changed later with
If a default tablespace is not specified when the database is created, it will be set to the SYSTEM tablespace.
After creating a database, do not leave the default tablespace as SYSTEM; this is very bad practice. Either change it as soon as you have created another tablespace, or after the CREATE DATABASE command has in fact let you create a default tablespace.
A quota is the amount of space in a tablespace that a user is allowed to occupy. He can create objects and allocate extents to them until the quota is reached. If he has no quota on a tablespace, he cannot create any objects at all. Quotas can be changed at any time. If a user’s quota is reduced below the size of the objects he already owns (or even reduced to zero), the objects will survive and will still be usable, but they will not be permitted to get any bigger.
Figure 8-2 shows how to investigate and set quotas.
FIGURE 8-2 Managing user quotas
The first query in the figure is against DBA_USERS and determines the default and temporary tablespaces for the user JOHN, created in Figure 8-1. DBA_USERS has one row for every user account in the database. User JOHN has picked up the database defaults for the default and temporary tablespaces, which are shown in the last query against DATABASE_PROPERTIES.
Most users will not need any quotas because they will never create objects. They will only have permissions against objects owned by other schemas. The few object-owning schemas will probably have QUOTA UNLIMITED on the tablespaces where their objects reside.
The two ALTER USER commands in Figure 8-2 give JOHN the capability to take up to 10MB of space in the USERS tablespace, and an unlimited amount of space in the EXAMPLE tablespace. The query against DBA_TS_QUOTAS confirms this; the number “–1” is how “unlimited” is represented. At the time the query was run, JOHN had not created any objects, so the figures for BYTES are zeros, indicating that he is not currently using any space in either tablespace.
Before you can create a table, you must have both permission to execute CREATE TABLE and a quota on a tablespace in which to create it.
Temporary Tablespace
Permanent objects (such as tables) are stored in permanent tablespaces; temporary objects are stored in temporary tablespaces. A session will need space in a temporary tablespace if it needs space for certain operations that exceed the space available in the session’s PGA. Every user account is assigned a temporary tablespace, and all user sessions connecting to the account will share this temporary tablespace.
The query against DBA_USERS in Figure 8-2 shows user JOHN’s temporary tablespace, which in this case is the database default temporary tablespace because an alternative temporary tablespace was not specified when the user was created. This is shown by the last query in Figure 8-2, against DATABASE_PROPERTIES.
Users do not need a quota on their temporary tablespaces.
A user does not need to be granted a quota on their temporary tablespace. This is because the objects in it are not actually owned by the user; they are owned by the SYS user, who has an unlimited quota on all tablespaces.
To change a user’s temporary tablespace (which will affect all future sessions that connect to the account), use an ALTER USER command:
If many users are logging on to the same user account, they will share use of one temporary tablespace. This can be a performance bottleneck, which may be avoided by using temporary tablespace groups.
Account Status
Every user account has a certain status, as listed in the ACCOUNT_STATUS column of DBA_USERS. There are five possibilities:
OPEN The account is available for use.
LOCKED This indicates that the DBA deliberately locked the account. No user can connect to a locked account.
EXPIRED This indicates that the lifetime has expired. Passwords can have a limited lifetime. No user can connect to an EXPIRED account until the password is reset.
EXPIRED (GRACE) This indicates that the grace period is in effect. A password need not expire immediately when its lifetime ends; it may be configured with a grace period during which users connecting to the account have the opportunity to change the password.
LOCKED (TIMED) This indicates that the account is locked because of failed login attempts. An account can be configured to lock automatically for a period after an incorrect password is presented a certain number of times.
To lock and unlock an account, use these commands:
To force a user to change their password, use this command:
This will immediately start the grace period, forcing the user to change their password at their next login attempt. There is no such command as “alter…unexpire.” The only way to make the account fully functional again is to reset the password.
Authentication Methods
A user account must have an authentication method: some means whereby the database can determine if the user attempting to create a session connecting to the account is allowed to do so. The simplest technique is by presenting a password that will be matched against a password stored within the database, but there are alternatives. These are the possibilities:
Operating system authentication
Password file authentication
Password authentication
External authentication
Global authentication
The first two techniques are used only for administrators; the last requires an LDAP directory server. The LDAP directory server is the Oracle Internet Directory, shipped as a part of the Oracle Fusion Middleware Suite.
Operating System and Password File Authentication
To enable operating system and password file authentication (the two go together) for an account, you must grant the user an administration role. There are six of these:
Grant the roles as follows:
Granting any (or all) of these privileges will copy the user’s password from the data dictionary into the external password file, where it can be read by the instance even if the database is not open. Following database creation, the only user with these privileges is SYS. It also allows the instance to authenticate users by checking whether the operating system user attempting the connection is a member of the operating system group mapped to the Oracle group name. On Linux, the operating system groups must be specified when installing the Oracle Home. On Windows, there is no choice. The groups are created implicitly with these names:
Oracle Group |
Windows Group |
OSDBA |
ORA_DBA |
OSOPER |
ORA_OPER |
OSASM |
ORA_ASM |
OSBACKUPDBA |
ORA_%HOMENAME%_SYSBACKUP |
OSDGDB |
ORA_%HOMENAME%_SYSDG |
OSKMDBA |
ORA_%HOMENAME%_SYSDG |
To use password file authentication, the user can connect with this syntax with SQL*Plus:
Note that password file authentication can be used for a connection to a remote database over Oracle Net. This is a logical impossibility with operating system authentication because when using a remote database, one never logs on to the remote operating system.
To use operating system authentication, the user can connect with this syntax with SQL*Plus:
The operating system password is not stored by Oracle and therefore there are no issues with changing passwords.
The equivalent of these syntaxes is also available when connecting with Database Express, by selecting the AS SYSDBA check box in the Database Express login window. To determine to whom the SYSDBA and SYSOPER privileges have been granted, query the view V$PWFILE_USERS. Connection with operating system or password file authentication is always possible, no matter what state the instance and database are in, and is necessary to issue STARTUP and SHUTDOWN commands.
All user sessions must be authenticated. There is no such thing as an “anonymous” login, and some authentication method must be used. There is, however, a user ANONYMOUS: this account is used by APEX applications.
Password Authentication
The syntax for a connection with password authentication using SQL*Plus is
When the user connects with password authentication, the instance will validate the password given against the password hash stored with the user account in the data dictionary. For this to work, the database must be open; it is logically impossible to issue STARTUP and SHUTDOWN commands when connected with password authentication. The user SYS is not permitted to connect with password authentication; only password file, operating system, and LDAP authentication are possible for SYS.
Usernames are case sensitive but are automatically converted to uppercase unless specified within double quotes. Passwords are case sensitive and there is no automatic case conversion. It is not necessary to use double quotes; the password will always be read exactly as entered.
Any user can change their user account password at any time, or a highly privileged user (such as SYSTEM) can change any user account password. The syntax (whether you are changing your own password or another one) is
External Authentication
If a user account is created with external authentication, Oracle will delegate the authentication to an external service; it will not prompt for a password. If the Advanced Security Option has been licensed, then the external service can be a number of third-party services, such as a Kerberos server or a RADIUS server. When a user attempts to connect to the user account, rather than authenticating the user itself, the database instance will accept (or reject) the authentication according to whether the external authentication service has authenticated the user. For example, if Kerberos is being used, the database will check that the user does have a valid Kerberos token.
Without the Advanced Security Option, the only form of external authentication that can be used is operating system authentication. This is a requirement for SYSDBA and SYSOPER accounts (as already discussed) but can also be used for normal users. The technique is to create an Oracle user account with the same name as the operating system user account but prefixed with a string specified by the instance parameter OS_AUTHENT_PREFIX. This parameter defaults to the string OPS$. To check its value, use a query such as this:
On Linux or Unix, external operating system authentication is very simple. Assuming that the OS_AUTHENT_PREFIX is set to the default and that there is an operating system user called jwatson, if you create an oracle user and grant him the CREATE SESSION privilege, he will be able to log in with no password and will be connected to the database user account ops$jwatson:
Under Windows, when Oracle queries the operating system to find the identity of the user, Windows will usually (depending on the details of Windows security configuration) return the username prefixed with the Windows domain. Assuming that the Windows logon ID is John Watson (including a space), that the Windows domain is JWACER (which happens to be the machine name), and that the OS_AUTHENT_PREFIX is set to the default, the command will be as follows:
Note that the username must be in uppercase, and because of the illegal characters (a backslash and a space) it must be enclosed in double quotes.
Using external authentication can be very useful, but only if the users actually log on to the machine hosting the database. Users will rarely do this, so the technique is more likely to be of value for accounts used for running maintenance or batch jobs.
Creating Accounts
The CREATE USER command has only two required arguments: a username and a method of authentication. Optionally, it can accept a clause to specify a default tablespace and a temporary tablespace, one or more quota clauses, a named profile, and commands to lock the account and expire the password. This is a typical example (with line numbers added):
Only the first line is required; there are defaults for everything else. Break down the command, line by line:
1. Provide the username and a password for password authentication.
2. Provide the default and temporary tablespaces.
3. Set up quotas on the default and another tablespace.
4. Nominate a profile for password and resource management.
5. Force the user to change his password immediately.
6. Make the account available for use (which would have been the default).
Every attribute of an account can be adjusted later with ALTER USER commands, with the exception of the name. This is how to change the attributes:
Having created a user account, it may be necessary to drop it:
This command will succeed only if the user does not own any objects—if the schema is empty. If you do not want to identify all the objects owned and drop them first, they can be dropped with the user by specifying CASCADE:
EXERCISE 8-1
Create Users
In this exercise, you will create some users to be used for the remaining exercises in this chapter. It is assumed that there is a permanent tablespace called EXAMPLE and a temporary tablespace called TEMP. If these don’t exist, either create them or use any other suitable tablespaces. Here are the steps to follow:
1. Connect to your database with SQL*Plus as a highly privileged user, such as SYSTEM or SYS.
2. Create three users:
3. Confirm that the users have been created with Database Express. From the database home page, the navigation path is the Security tab | Users link. The users should look something like those shown in Figure 8-3.
FIGURE 8-3 Users displayed in Database Express
4. From SQL*Plus, attempt to connect as user ALOIS:
5. When prompted, select a new password (such as “oracle”). This won’t get you anywhere because ALOIS does not have the CREATE SESSION privilege.
6. Refresh the Database Express window and note that the status of the ALOIS account is no longer EXPIRED (indicated with the clock symbol) but rather OPEN (indicated with a tick) because his password has been changed.
CERTIFICATION OBJECTIVE 8.02
Grant and Revoke Privileges
By default, no one can do anything in an Oracle database. A user cannot even connect without being granted a privilege. And once this has been done, they still can’t do anything useful (or dangerous) without being given more privileges. Privileges are assigned to user accounts with a GRANT command and withdrawn with a REVOKE. Additional syntax can give a user the ability to grant any privileges they have to other users. By default, only the DBAs (SYS and SYSTEM) have the right to grant any but the most limited privileges.
Privileges come in two groups: system privileges, which (generally speaking) let users perform actions that affect the data dictionary, and object privileges, which let users perform actions that affect data.
System Privileges
There are more than 200 system privileges. Most apply to actions that affect the data dictionary, such as creating tables or users. Others affect the database or the instance, such as creating tablespaces, adjusting instance parameter values, and establishing a session. These are some of the more commonly used privileges:
CREATE SESSION This privilege lets the user connect. Without it, the user cannot even log on to the database.
RESTRICTED SESSION If the database is started with STARTUP RESTRICT, or adjusted with ALTER SYSTEM ENABLE RESTRICTED SESSION, only users with this privilege will be able to connect.
ALTER DATABASE Gives access to many commands necessary for modifying physical structures.
ALTER SYSTEM Gives control over instance parameters and memory structures.
CREATE TABLESPACE Used along with the ALTER TABLESPACE and DROP TABLESPACE privileges; these privileges will let a user manage tablespaces.
CREATE TABLE Lets the grantee create tables in his own schema; includes the ability to alter and drop them, to run SELECT and DML commands on them, and to create, alter, or drop indexes on them.
GRANT ANY OBJECT PRIVILEGE Lets the grantee grant object permissions on all objects, including those he does not own, to others (but not to himself).
CREATE ANY TABLE The grantee can create tables that belong to other users.
DROP ANY TABLE The grantee can drop tables belonging to any other users.
INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE The grantee can execute these DML commands against tables owned by all other users.
SELECT ANY TABLE The grantee can SELECT from any table in the database, with one provision: tables owned by SYS, including the data dictionary tables, are not visible.
The syntax for granting system privileges is
After creating a user account, a command such as this will grant the system privileges commonly assigned to users who will be involved in developing applications:
These privileges let the user connect and configure his session and then create objects to store data and PL/SQL objects. These objects can only exist in their own schema; they will have no privileges against any other schema. The object creation will also be limited by the quota(s) they may (or may not) have been assigned on various tablespaces.
A variation in the syntax lets the grantee pass their privilege on to a third party. This is an example:
This gives SCOTT the ability to create tables in his own schema, and also to issue the GRANT CREATE TABLE TO command himself.
Revocation of a system privilege will not cascade (unlike revocation of an object privilege).
If a privilege is revoked from a user, any actions they performed using that privilege (such as creating tables) remain intact. Also, if the user has been granted and has used the ADMIN OPTION, any users to whom they passed on the privilege will retain it. There is no record kept of the grantor of a system privilege, so it is not possible for a REVOKE to cascade. Figure 8-4 illustrates this.
FIGURE 8-4 GRANT and REVOKE from SQL*Plus
The ANY privileges give permissions against all relevant objects in the database. Thus,
will let SCOTT query every table in every schema in the database. It is often considered bad practice to grant the ANY privileges to any user other than the system administration staff.
In fact, ANY is not as dangerous now as it was in earlier releases. It no longer includes tables in the SYS schema, so the data dictionary is still protected. However, ANY should still be used with extreme caution because it removes all protection from user tables.
Object Privileges
Object privileges give the ability to perform SELECT, INSERT, UPDATE, and DELETE commands against tables and related objects as well as to execute PL/SQL objects. These privileges do not exist for objects in the users’ own schemas; if a user has the system privilege CREATE TABLE, they can perform SELECT and DML operations against the tables they create with no further need for permissions.
The ANY privileges, which grant permissions against objects in every user account in the database, are not object privileges—they are system privileges.
The object privileges apply to different types of object:
Privilege |
Granted On |
SELECT |
Tables, views, sequences, synonyms |
INSERT |
Tables, views, synonyms |
UPDATE |
Tables, views, synonyms |
DELETE |
Tables, views, synonyms |
ALTER |
Tables, sequences |
EXECUTE |
Procedures, functions, packages, synonyms |
The syntax is
Here is an example:
Variations include the use of ALL, which will apply all the permissions relevant to the type of object, and nominating particular columns of views or tables:
This code will let SCOTT query all columns of HR’s EMPLOYEES table but only write to one nominated column, SALARY. Then SCOTT is given all the object privileges (SELECT and DML) on HR’s REGIONS table.
Granting privileges at the column level is often said to be bad practice because of the massive workload involved. If it is necessary to restrict people’s access to certain columns, creating a view that shows only those columns will often be a better alternative.
Revocation of an object privilege will cascade (unlike revocation of a system privilege).
Using WITH GRANT OPTION (or with Database Express, navigate to Security | Users | Privileges and Roles | Edit) lets a user pass their object privilege on to a third party. Oracle retains a record of who granted object privileges to whom; this allows a REVOKE of an object to cascade to all those in the chain. Consider this sequence of commands:
At the conclusion of these commands, neither SCOTT nor JON nor SUE has the SELECT privilege against HR.EMPLOYEES.
EXERCISE 8-2
Grant Direct Privileges
In this exercise, you will grant some privileges to the users created in Exercise 8-1 and prove that they work. Follow these steps:
1. Connect to your database as user SYSTEM with SQL*Plus and then grant CREATE SESSION to user ALOIS:
2. Open another SQL*Plus session and connect as ALOIS. This time, the login will succeed:
3. As ALOIS, attempt to create a table:
This will fail with the message “ORA-01031: insufficient privileges.”
4. In the SYSTEM session, grant ALOIS the CREATE TABLE privilege:
5. In the ALOIS session, try again:
This will fail with the message “ORA-01950: no privileges on tablespace ‘EXAMPLE’.”
6. In the SYSTEM session, give ALOIS a quota on the EXAMPLE tablespace:
7. In the ALOIS session, try again. This time, the creation will succeed.
8. As ALOIS, grant object privileges on the new table:
9. To retrieve information regarding these grants, as SYSTEM, run these queries:
10. Revoke the privileges granted to AFRA and ANJA:
11. Confirm the revocations by rerunning the first query from step 9.
CERTIFICATION OBJECTIVE 8.03
Create and Manage Roles
Managing security with directly granted privileges works but has two problems. First, it can be a huge workload: an application with thousands of tables and users could need millions of grants. Second, if a privilege has been granted to a user, that user has it in all circumstances: it is not possible to make a privilege active only in certain circumstances. Both these problems are solved by using roles. A role is a bundle of system and/or object privileges that can be granted and revoked as a unit, and having been granted can be temporarily activated or deactivated within a session.
Creating and Granting Roles
Roles are not schema objects: they aren’t owned by anyone and therefore cannot be prefixed with a username. However, they do share the same namespace as users: it is not possible to create a role with the same name as an already-existing user, or a user with the same name as an already-existing role.
Create a role with the CREATE ROLE command:
Then grant privileges to the role with the usual syntax, including WITH ADMIN or WITH GRANT OPTION, as desired.
For example, assume that the HR schema is being used as a repository for data to be used by three groups of staff: managerial staff have full access, senior clerical staff have limited access, and junior clerical staff have very restricted access. First, create a role that might be suitable for the junior clerks; all they can do is answer questions by running queries:
Anyone granted this role will be able to log on to the database and run SELECT statements against the HR tables. Next, create a role for the senior clerks, who can also write data to the EMPLOYEES and JOB_HISTORY tables:
This role is first granted the HR_JUNIOR role (there is no problem granting one role to another) with the syntax that will let the senior users assign the junior role to others. Then it is granted DML privileges on just two tables. Now, create the managers’ role, which can update all the other tables:
This third role is given the HR_SENIOR role with the ability to pass it on, and then gets full control over the contents of all the tables. But note that the only system privilege this role has is CREATE_SESSION, acquired through HR_SENIOR, which acquired it through HR_JUNIOR. Not even this role can create or drop tables; that must be done by the HR user, or an administrator with CREATE ANY TABLE and DROP ANY TABLE system privileges.
Note the syntax WITH ADMIN OPTION, which is the same as that for granting system privileges. As with system privileges, revocation of a role will not cascade; there is no record kept of who has granted a role to whom.
Finally, grant the roles to the relevant staff. If SCOTT is a manager, SUE is a senior clerk, and JON and ROOP are junior clerks, the flow would be as shown in Figure 8-5.
FIGURE 8-5 Granting roles with SQL*Plus
Predefined Roles
There are dozens of predefined roles in an Oracle database. Here are some that every DBA should be aware of:
CONNECT This role exists only for backward compatibility. In previous releases, it had the system privileges necessary to create data-storing objects, such as tables; with the current release, it only has CREATE SESSION.
RESOURCE Also for backward compatibility, this role can create both data objects (such as tables) and procedural objects (such as PL/SQL procedures).
DBA Has most of the system privileges as well as several object privileges and roles. Any user granted DBA can manage virtually all aspects of the database, except for startup and shutdown.
SELECT_CATALOG_ROLE Has thousands of object privileges against data dictionary objects, but no system privileges or privileges against user data. Useful for junior administration staff who must monitor and report on the database but not be able to see user data.
SCHEDULER_ADMIN Has the system privileges necessary for managing the Scheduler job-scheduling service.
Also, the predefined role PUBLIC is always granted to every database user account. It follows that if a privilege is granted to PUBLIC, it will be available to all users. Therefore, following the command
all users will be able to query the HR.REGIONS table.
The PUBLIC role is treated differently from any other role. It does not, for example, appear in the view DBA_ROLES. This is because the source code for DBA_ROLES, which can be seen in the cdsec.sql script called by the catalog.sql script, specifically excludes it.
Enabling Roles
By default, if a user has been granted a role, the role will be enabled. This means that the moment a session is established connecting to the user account, all the privileges (and other roles) granted to the role will be active. This behavior can be modified by making the role nondefault. Following the example given in the preceding section, this query shows what roles have been granted to JON:
JON has been granted HR_JUNIOR. He does not have administration on the role (so he cannot pass it on to anyone else), but it is a default role—he will have this role whenever he connects. This situation may well not be what you want. For example, JON has to be able to see the HR tables (it’s his job) but that doesn’t mean you want him to be able to dial in from home, at midnight, and hack into the tables with SQL*Plus. You want to arrange things such that he can see the tables only when he is at a terminal in the personnel office, running the HR application during working hours.
Here is how to change the default behavior:
Now when JON logs on, he will not have any roles enabled. Unfortunately, this means he can’t log on at all—because it is only HR_JUNIOR that gives him the CREATE SESSION system privilege. This is easily fixed:
Now when JON connects, only his CONNECT role is enabled—and the current version of CONNECT is not dangerous at all. Within the application, software commands can be embedded to enable the HR_JUNIOR role. The basic command to enable a role within a session is
which can be issued by the user at any time. So no security yet. But if the role is created with the syntax
then the role can only be enabled by running the PL/SQL procedure nominated by procedure_name. This procedure can make any number of checks: that the user is working on a particular TCP/IP subnet, that they are running a particular user process (probably not SQL*Plus), that the time is in a certain range, and so on. Embedding calls to the enabling procedures at appropriate points in an application can switch roles on and off, as required, while leaving them disabled at all times when a connection is made with an ad hoc SQL tool such as SQL*Plus.
Privilege Analysis
It is sometimes difficult to identify what privileges a user has and what privileges he actually uses, particularly when roles are involved. For this reason, Oracle provides the Privilege Analysis mechanism. The flow is as follows:
Define the scope of what should be analyzed: privilege usage throughout the entire database; privileges used that were accessed through certain roles; privileges used by particular applications.
Start monitoring activity; allow users to work for a period; stop monitoring activity.
Generate reports on what privileges were used, and what granted privileges were not used, during the analysis period.
The critical procedures are
dbms_privilege_capture.create_capture
dbms_privilege_capture.enable_capture
dbms_privilege_capture.disable_capture
dbms_privilege_capture.generate_result
and the critical views are
dba_used_privs
dba_unused_privs
EXERCISE 8-3
Create and Grant Roles
In this exercise, you will create some roles, grant them to the users, and demonstrate their effectiveness. Follow these steps:
1. Connect to your database with SQL*Plus as user SYSTEM.
2. Create two roles, as follows:
3. Grant some privileges to the roles and then grant USR_ROLE to MGR_ROLE:
4. As user SYSTEM, grant the roles to AFRA and ANJA:
5. Connect to the database as user AFRA:
6. Grant the USR_ROLE to ANJA and then insert a row into ALOIS.T1:
7. Confirm that ANJA can connect and query ALOIS.T1 but do nothing else:
8. As user SYSTEM, adjust ANJA so that by default she can log on but do nothing else:
9. Demonstrate the enabling and disabling of roles:
10. Query the data dictionary to identify their role usage:
CERTIFICATION OBJECTIVE 8.04
Create and Manage Profiles
A profile can be used to enforce a password. Profiles are always used, but the default profile (applied by default to all users, including SYS and SYSTEM) does very little.
Password Profile Limits
These are the limits that can be applied to passwords:
FAILED_LOGIN_ATTEMPTS Specifies the number of consecutive errors on a password before the account is locked. If the correct password is given before this limit is reached, the counter is reset to zero.
PASSWORD_LOCK_TIME The number of days to lock an account after FAILED_LOGIN_ATTEMPTS is reached.
PASSWORD_LIFE_TIME The number of days before a password expires. It may still be usable for a while after this time, depending on PASSWORD_GRACE_TIME.
PASSWORD_GRACE_TIME The number of days following the first successful login after the password has expired during which the password can be changed. The old password is still usable during this time.
PASSWORD_REUSE_TIME The number of days before a password can be reused.
PASSWORD_REUSE_MAX The number of password changes before a password can be reused.
PASSWORD_VERIFY_FUNCTION The name of a function to run whenever a password is changed. The purpose of the function is assumed to be checking the new password for a required degree of complexity, but it can do pretty much anything you want.
Profiles can be also be used to limit resource usage, but a much more sophisticated tool to accomplish this is the Resource Manager.
To see which profile is currently assigned to each user, run this query:
By default, all users will be assigned the profile called DEFAULT. Then the view that will display the profiles themselves is DBA_PROFILES:
The DEFAULT profile has these password limits:
Resource Name |
Limit |
FAILED_LOGIN_ATTEMPTS |
10 |
PASSWORD_LIFE_TIME |
180 days |
PASSWORD_REUSE_TIME |
unlimited |
PASSWORD_REUSE_MAX |
unlimited |
PASSWORD_VERIFY_FUNCTION |
null |
PASSWORD_LOCK_TIME |
1 day |
PASSWORD_GRACE_TIME |
7 days |
These restrictions are not too strict: a password can be entered incorrectly 10 consecutive times before the account is locked for one day, and a password will expire after about six months with a one-week grace period for changing it after that. There is no check on password complexity.
Creating and Assigning Profiles
The simplest way to enable more sophisticated password management is to run code provided in a supplied script. On Unix or Linux, it is
On Windows, it is
On either platform, the script creates a set of functions offering various degrees of password complexity checking.
To create a profile with SQL*Plus, use the CREATE PROFILE command, setting whatever limits are required. Any limits not specified will be picked up from the current version of the DEFAULT profile. For example, it could be the rules of the organization state that accounts should be locked after five consecutive failed login attempts for one hour, except for administrators, who should be locked after two attempts for a whole day, and that all passwords should be subject to the provided standard password complexity verification algorithm.
EXERCISE 8-4
Create and Use Profiles
In this exercise, you’ll create, assign, and test a profile that forces some password control. Here are the steps to follow:
1. Connect to your database with SQL*Plus as user sys.
2. Execute the script that will create the supplied verification functions and then apply one to the default profile. Confirm that the function has been created and applied:
3. Create a profile that will lock accounts after two wrong passwords for 10 minutes:
4. Assign this new profile to ALOIS:
5. Deliberately enter the wrong password for ALOIS a few times:
6. As user SYSTEM, check the status of the ALOIS account and unlock it:
7. Check that ALOIS can now connect:
8. Test the verification function by attempting to change the password a few times:
9. Tidy up by dropping the profile, the roles, and the users. Note the use of CASCADE when dropping the profile to remove it from ALOIS as well as on the DROP USER command to drop his table as well. Roles can be dropped even if they are assigned to users. The privileges granted on the table will be revoked as the table is dropped.
CERTIFICATION SUMMARY
User accounts define users who can connect to the database and are associated with a schema that stores the objects owned by the account. Privileges must be granted to an account (either directly or via roles) before the account is usable in any way.
Privileges come in two forms: system privileges, which control certain actions within the database (typically, actions that involve changes to the data dictionary), and object privileges, which control access to data. A role is a bundle of privileges. Unlike a privilege (which is always enabled once granted), a role can be enabled or disabled within a session.
Profiles give control over account passwords and resource usage. All user accounts have a profile (by default, the profile called DEFAULT). The DEFAULT profile can be adjusted, and the change will immediately apply to all users with the DEFAULT profile. Alternatively, additional profiles can be created and assigned explicitly to certain users.
TWO-MINUTE DRILL
Create and Manage Database User Accounts
Users connect to a user account, which is connected to a schema.
Some form of authentication is always required.
A user must have a quota on a tablespace before they can create any objects.
A user who owns objects cannot be dropped, unless the CASCADE keyword is used.
Grant and Revoke Privileges
Privileges are of two types: object privileges and system privileges.
By default, a user can do nothing. They can’t even log on.
A revocation of a system privilege does not cascade; a revocation of an object privilege does.
Create and Manage Roles
Roles are not schema objects.
Roles can contain both system and object privileges as well as other roles.
A role can be enabled or disabled for a session.
Create and Manage Profiles
Profiles can enforce password policies.
Every user always has a profile (by default, the DEFAULT profile).
SELF TEST
Create and Manage Database User Accounts
1. How can you permit users to connect without requiring them to authenticate themselves? (Choose the best answer.)
A. Grant CREATE SESSION to PUBLIC.
B. Create a user such as this, without a password:
C. Create a profile that disables password authentication and assign it to the users.
D. You cannot do this because all users must be authenticated.
2. You create a user with this statement:
What more must be done before he can create a table in the EXAMPLE tablespace? (Choose all correct answers.)
A. Nothing more is necessary.
B. Give him a quota on EXAMPLE.
C. Grant him the CREATE TABLE privilege.
D. Grant him the CREATE SESSION privilege.
E. Grant him the MANAGE TABLESPACE privilege.
3. If a user owns tables in a tablespace, what will be the effect of attempting to reduce their quota on the tablespace to zero? (Choose the best answer.)
A. The tables will survive, but INSERTS will fail.
B. The tables will survive but cannot get bigger.
C. The attempt will fail unless the tables are dropped first.
D. The tables will be dropped automatically if the CASCADE keyword is used.
4. If you create a user without specifying a temporary tablespace, what temporary tablespace will be assigned? (Choose the best answer.)
A. You must specify a temporary tablespace.
B. SYSTEM.
C. TEMP.
D. The database default temporary tablespace.
E. The user will not have a temporary tablespace.
Grant and Revoke Privileges
5. You issue these commands:
Which grants should be revoked to prevent JON from seeing the contents of HR.REGIONS? (Choose the best answer.)
A. a, b, c, and d
B. a, c, and d
C. a and b
D. c and d
E. a, b, and c
6. Which of these statements about system privileges are correct? (Choose all correct answers.)
A. Only the SYS and SYSTEM users can grant system privileges.
B. If a system privilege is revoked from a user, it will also be revoked from all users to whom he granted it.
C. If a system privilege is revoked from a user, it will not be revoked from all users to whom he granted it.
D. CREATE TABLE is a system privilege.
E. CREATE ANY TABLE is a system privilege.
Create and Manage Roles
7. Study this script (line numbers have been added):
Which line will cause an error? (Choose the best answer.)
A. Line 1, because only users, not roles, have passwords.
B. Line 2, because only users, not roles, can create and own tables.
C. Line 3, because SELECT TABLE is not a privilege.
D. Line 4, because a role cannot have a system privilege in addition to table privileges.
8. Which of these statements is incorrect regarding roles? (Choose the best answer.)
A. You can grant object privileges and system privileges as well as roles to a role.
B. A role cannot have the same name as a table.
C. A role cannot have the same name as a user.
D. Roles can be enabled or disabled within a session.
Create and Manage Profiles
9. If a password profile is dropped, what will be the effect on users to whom it is assigned? (Choose the best answer.)
A. You cannot drop the profile until it is unassigned from the users.
B. The profile will be removed if you use the CASCADE keyword.
C. The users will revert to the default profile.
D. Users to whom it is assigned will continue to use it, but it can no longer be assigned to anyone else.
10. Which of these can be controlled by a password profile? (Choose all correct answers.)
A. Two or more users choosing the same password
B. Preventing the reuse of a password by the same user
C. Forcing a user to change password
D. Enabling or disabling password file authentication
LAB QUESTION
For this question, use Database Express where possible, if you wish, but it is usually necessary to use SQL*Plus to query views directly when trying to understand what is happening with access rights. There are often several ways of getting to a table, and it can be difficult to work out why a user can see it.
Create the user BERND, and give him the necessary permissions to log on to the database with password authentication and create tables in his own schema. Test this by connecting as BERND and creating a table called DATETAB, with a single column of type DATE. Insert a row into DATETAB and then commit the insert.
Now create the user CHRISTA. Give her these privileges:
Connect as CHRISTA, and check that she can read BERND.DATETAB. Revoke her SELECT privilege, and confirm that she can no longer select from BERND.DATETAB, although she can (perhaps oddly) insert rows into it. Why is this, when she was also granted ALL on BERND.DATETAB? Run queries against DBA_TAB_PRIVS at all stages to understand what is happening.
Create the user DORIS and grant her privileges as follows:
Confirm that DORIS can now read BERND.DATETAB. What privileges must be revoked before DORIS will no longer be able to see BERND.DATETAB? Check the permissions at all stages by querying DBA_TAB_PRIVS, DBA_SYS_PRIVS, and DBA_ROLE_PRIVS.
Tidy up by dropping the users. Remember to use CASCADE where appropriate.
SELF TEST ANSWERS
Create and Manage Database User Accounts
1. D. All users must be authenticated.
A, B, and C are incorrect. A is incorrect because although this will give all users permission to connect, they will still have to authenticate. B is incorrect because a NULL is not acceptable as a password. C is incorrect because a profile can only manage passwords, not disable them.
2. C, D. All these actions are necessary.
A, B, and E are incorrect. A is incorrect because without privileges and a quota, jon cannot connect and create a table. E is incorrect because this privilege lets you manage a tablespace, not create objects in it. B is not necessary because an unlimited quota is implicitly granted on the EXAMPLE tablespace to user jon.
3. B. It will not be possible to allocate further extents to the tables.
A, C, and D are incorrect. A is incorrect because inserts will succeed as long as there is space in the extents already allocated. C is incorrect because there is no need to drop the tables. D is incorrect because CASCADE cannot be applied to a quota command.
4. D. There is always a database-wide default, which (by default) is SYSTEM. In many cases, it will have been set to TEMP.
A, B, C, and E are incorrect. A is incorrect because there is a default. B is incorrect because the default temporary tablespace may have been changed. C is incorrect because although TEMP is frequently used by default, it may not be. E is incorrect because all user accounts must have a temporary tablespace.
Grant and Revoke Privileges
5. B. The grant of the DBA role and the grant to PUBLIC must be removed, as well as the directly granted SELECT privilege.
C, D, and E are incorrect. They all leave one grant in place that must be revoked. A is incorrect because it is not necessary to revoke ALL as well as SELECT: either would be sufficient.
6. C, D, and E. Answer C is correct because the revocation of a system privilege does not cascade. D and E are correct because any action that updates the data dictionary is a system privilege.
A and B are incorrect. A is incorrect because system privileges can be granted by any user who has been granted the privilege WITH ADMIN OPTION. B is incorrect because the revocation of a system privilege does not cascade.
Create and Manage Roles
7. C. There is no such privilege as SELECT TABLE; it is granted implicitly with CREATE TABLE.
A, B, and D are incorrect. A is incorrect because roles can be password protected. B is incorrect because even though tables must be owned by users, permission to create them can be granted to a role. D is incorrect because a role can have any combination of object and system privileges.
8. B. Roles are not schema objects and therefore can have the same names as tables.
A, C, and D are incorrect. A is incorrect because roles can have any combination of system, object, and role privileges. C is incorrect because roles cannot have the same names as users. D is incorrect because roles can be enabled and disabled at any time.
Create and Manage Profiles
9. C. Dropping a profile implicitly reassigns all relevant users to the default profile.
A, B, and D are incorrect. A is incorrect because of the implicit reassignment of users. B is incorrect because there is no CASCADE keyword in the DROP PROFILE command: it isn’t necessary. D is incorrect because the effect of dropping a profile is immediate.
10. B and C. These are both password limits.
A and D are incorrect. A is incorrect because it is not possible to control this: Oracle has no knowledge of the actual password, only knowledge of the hash of the password. D is incorrect because this is controlled through the REMOTE_LOGIN_PASSWORDFILE instance parameter, not through profiles.
LAB ANSWER
Here is one possible solution. To begin, create BERND and the table:
The experiment with CHRISTA follows:
For the experiment with DORIS, the queries to be run to investigate the permissions could include the following: