O'Reilly logo

Oracle Security by Marlene Theriault, William Heney

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

The Composition of the Views

This section examines each of the views important for security. We will look at the information the view provides and whether the view has an ALL_ and/or USER_ counterpart. You will also find details on the composition (the columns) for each view and an explanation of why the view is important from a security perspective.

The DBA_PROFILES View

The DBA_PROFILES view lists all profiles and their limits. This view determines what profiles exist in your database, what resources have been limited, and what the limit is for each resource. The new password parameters are set by creating a profile. This view enables you to see the values to which the password limits have been set. The resources not marked as “PASSWORD” in the output below are parameters that are also available in a version 7 database. Columns include:

PROFILE

Profile name. Limited to 30 characters

RESOURCE_NAME

Name of resource controlled by profile

LIMIT

Limit placed on this resource for this profile

RESOURCE_TYPE

Added in Oracle8; indicates whether the profile is KERNEL or PASSWORD

If you never create a profile in an Oracle8 database, this view will contain the sixteen rows shown below. We have added formatting statements to be able to fit the information on one line for each row.

SQL> COLUMN profile FORMAT a10
SQL> COLUMN resource_name FORMAT a25
SQL> COLUMN limit FORMAT a10
SQL> SELECT * 
  2    FROM dba_profiles 
  3   ORDER by 1, 2;

PROFILE    RESOURCE_NAME             RESOURCE LIMIT
---------- ------------------------- -------- ----------
DEFAULT    COMPOSITE_LIMIT           KERNEL   UNLIMITED
DEFAULT    CONNECT_TIME              KERNEL   UNLIMITED
DEFAULT    CPU_PER_CALL              KERNEL   UNLIMITED
DEFAULT    CPU_PER_SESSION           KERNEL   UNLIMITED
DEFAULT    FAILED_LOGIN_ATTEMPTS     PASSWORD UNLIMITED
DEFAULT    IDLE_TIME                 KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_CALL    KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_SESSION KERNEL   UNLIMITED
DEFAULT    PASSWORD_GRACE_TIME       PASSWORD UNLIMITED
DEFAULT    PASSWORD_LIFE_TIME        PASSWORD UNLIMITED
DEFAULT    PASSWORD_LOCK_TIME        PASSWORD UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX        PASSWORD UNLIMITED
DEFAULT    PASSWORD_REUSE_TIME       PASSWORD UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION  PASSWORD UNLIMITED
DEFAULT    PRIVATE_SGA               KERNEL   UNLIMITED
DEFAULT    SESSIONS_PER_USER         KERNEL   UNLIMITED
16 rows selected.

If you create a profile and omit one of the parameters, the value specified in the DEFAULT for that parameter will be used. So, a profile named short_time could be created with one parameter, connect_time, specified. For that profile, the other resource parameters would be the same as the DEFAULT profile — that is, set to UNLIMITED.

The DBA_ROLE_PRIVS View

The DBA_ROLE_PRIVS view (also available in the form USER_ROLE_PRIVS) lists roles granted to users and roles. Columns include:

GRANTEE

Grantee name, user, or role receiving the grant.

GRANTED_ROLE

Granted role name.

ADMIN_OPTION

GRANT was with ADMIN OPTION.

DEFAULT_ROLE

Role is designated as a DEFAULT ROLE for the user.

This view shows the roles granted to each user. It is not necessary that a user have a role. As noted earlier, suppose that the user mary has been granted the system privilege CREATE SESSION. mary, however, has not been granted any roles, so in the following listing, mary does not appear. ralph is seen because that user was granted the CONNECT role.

The ADMIN_OPTION (which for space reasons, shows as ADM in the following output) indicates whether the privilege can be passed on with administrative privilege. This option may be specified for a user in the GRANT STATEMENT with the WITH ADMIN OPTION clause. If the entry in this column is “YES,” then the grantee can grant the privilege to another user. If that privilege is later revoked from the grantee, the other user will still retain the privilege until it is explicitly revoked. See the further discussion of ADMIN OPTION in Chapter 5.

In this example, ralph also has been granted the FINMON role. In this system, this represents the “financial monitor” function and has the necessary privileges on tables to perform that function. This role, however, is not in effect when ralph logs in to the database; it must be manually set—as indicated by the “NO” in the DEFAULT column.

SQL> SELECT * 
  2    FROM dba_role_privs
  3   ORDER BY 1,2;

GRANTEE              GRANTED_ROLE                   ADM DEF
-------------------- ------------------------------ --- ---
RALPH                CONNECT                        NO  YES
RALPH                FINMON                         NO  NO
DBA                  EXP_FULL_DATABASE              NO  YES
DBA                  IMP_FULL_DATABASE              NO  YES
SYS                  CONNECT                        YES YES
SYS                  DBA                            YES YES
SYS                  EXP_FULL_DATABASE              YES YES
SYS                  IMP_FULL_DATABASE              YES YES
SYS                  RESOURCE                       YES YES
SYSTEM               DBA                            YES YES

9 rows selected.

Note

In the above example and all examples that follow, the output has been modified to group the GRANTEE entries for readability.

The FINMON role has been created with a password of blah. In order for ralph to enable this role, ralph must issue the following command:

SQL> SET ROLE finmon IDENTIFIED BY blah;
Role set.

In Chapter 14, we will see how access to a password-protected role can be managed through the application so the user does not need to know the password, does not need to know that a role is being enabled, and does not need to hardcode the password in the application. All three of these actions are essential elements of a practical security system.

The DBA_ROLES View

The DBA_ROLES view lists all roles that exist in the database. You can use this view to easily see the roles available in the database. Columns include:

ROLE

Role name

PASSWORD_REQUIRED

“YES” indicates that the role requires a password to be enabled

This view is very straightforward. It lists roles (no users) and indicates whether a password is required before the role can be enabled. Remember, though, that if a password-protected role is granted to a user and made a default role for that user (the user can have several), then it is active when the user logs into the database. The user does not need to supply a password. In fact, the user may never be aware that the role exists or requires a password to be set.

In the listing that follows, all of the roles except FINMON are roles that will be found in a newly-created database. The standard roles do not have passwords, but the FINMON role does. That means that to enable the role, the user who has been granted the role must supply the password when enabling it. But, if this role is granted as a default role, it is set at login time and the user does not have to supply the password. Normally, password-protected roles are not set at login, as this defeats the benefit of having the password. The password-protected role is a key feature in establishing a controlled security environment.

SQL> SELECT role, password_required 
  2    FROM dba_roles 
  3   ORDER by role;

ROLE                           PASSWORD
------------------------------ --------
AQ_ADMINISTRATOR_ROLE          NO
AQ_USER_ROLE                   NO
CONNECT                        NO
CTXADMIN                       NO
CTXAPP                         NO
CTXUSER                        NO
DBA                            NO
DELETE_CATALOG_ROLE            NO
EXECUTE_CATALOG_ROLE           NO
EXP_FULL_DATABASE              NO
FINMON                         YES
IMP_FULL_DATABASE              NO
RECOVERY_CATALOG_OWNER         NO
RESOURCE                       NO
SELECT_CATALOG_ROLE            NO
SNMPAGENT                      NO

15 rows selected.

Other standard roles may exist after all available installation scripts are executed, but the roles shown here are the ones that exist after the CATALOG.SQL and CATPROC.SQL scripts have been executed.

The DBA_SYS_PRIVS View

The DBA_SYS_PRIVS view (also available in the form USER_SYS_PRIVS) lists the system privileges granted to users and roles. Use this view to determine what system privileges a particular user or role has granted to it. Columns include:

GRANTEE

User or role receiving the grant.

PRIVILEGE

System privilege granted.

ADMIN_OPTION

Grant was with ADMIN OPTION.

System privileges differ from object privileges; system privileges are privileges to request system services, and object privileges are permissions to do something to an object. A short listing from this table is shown below:

SQL> SELECT grantee, privilege, admin_option
  2    FROM dba_sys_privs 
  3   WHERE rownum < 6
  4   UNION
  5  SELECT grantee, privilege, admin_option
  6    FROM dba_sys_privs 
  7   WHERE grantee = 'MARY
  8*  ORDER BY grantee, privilege

GRANTEE PRIVILEGE                                ADM
------- ---------------------------------------- ---
CONNECT ALTER SESSION                            NO
CONNECT CREATE CLUSTER                           NO
CONNECT CREATE DATABASE LINK                     NO
CONNECT CREATE SEQUENCE                          NO
CONNECT CREATE SESSION                           NO
MARY    CREATE SESSION                           NO

6 rows selected.

As with DBA_TAB_PRIVS (discussed next), this view shows the role or user receiving the privilege (GRANTEE in the output), but it does not show who issued the GRANT statement. In the list, you can see that the CONNECT role has several privileges, but mary only has the CREATE SESSION privilege (allows login to the database). As mentioned earlier, the ADM column indicates whether the privilege can be passed on with administrative privilege. If the entry in this column is “YES”, the grantee can grant the privilege to another user. If that privilege is later revoked from the grantee, the other user will still retain the privilege.

The DBA_TAB_PRIVS View

The DBA_TAB_PRIVS view (also available in the form ALL_TAB_PRIVS and USER_TAB_PRIVS) lists privileges on objects in the database. Use this view to determine what privileges have been given to a particular user or role in the database. Columns include:

GRANTEE

User or role granted the privilege

OWNER

Owner of the object

TABLE_NAME

Name of the object

GRANTOR

Name of the user who performed the grant

PRIVILEGE

Type of privilege granted

GRANTABLE

Privilege is grantable

Note that these are object privileges; system privileges were covered in the DBA_SYS_PRIVS view earlier in this chapter.

The object grants

Object privileges enable a user to interact with data or perform work. If you GRANT SELECT on a table to ralph, for example, he will be able to look at the data contained in that table. Likewise, if you GRANT EXECUTE on a procedure to ralph, he will be able to activate that procedure.

The following object privileges are available via the GRANT command:

SELECT

Gives the grantee the ability to look at the contents of an object.

INSERT

Gives permission to add new rows to a table.

UPDATE

Gives permission to change existing rows. This permission may be further qualified to specific columns.

DELETE

Allows the user to delete any row.

EXECUTE

Allows stored programs to be run. This privilege only applies to stored programs, and cannot be granted on a table.

INDEX

Allows a user to create an index on a table, even one not owned by the user.

REFERENCES

Allows the grantee to create a foreign key reference to the named table.

ALL

Allows the user to perform all of the privileges on tables listed above. In addition, ALL allows the user to modify the structure of the table. This means that the user can change the length of a column, change the data type of a column, or add columns to the table. We strongly recommend that you never use GRANT ALL.

Note the slight contradiction in the view name and one of the privileges. EXECUTE cannot refer to a table, only to a stored program. So when EXECUTE on a stored program is granted to either a role or a user, that privilege will be found in the DBA_TAB_PRIVS view even though the name implies only tables.

Refer to the Oracle SQL Language Reference Manual for the correct syntax for the GRANT command, as there are several options for issuing object grants. An example of the data available from this view is shown below:

SQL> SELECT * 
  2    FROM dba_tab_privs 
  3   WHERE rownum < 6 
  4   UNION
  5  SELECT * 
  6    FROM dba_tab_privs 
  7   WHERE grantee = 'MARY';

GRANTEE OWNER TABLE_NAME             GRANTOR PRIVILEGE       GRANTABLE
------- ----- ---------------------- ------- --------------- ---------
PUBLIC  SYS   DUAL                   SYS     SELECT          YES
PUBLIC  SYS   STMT_AUDIT_OPTION_MAP  SYS     SELECT          NO
PUBLIC  SYS   SYSTEM_PRIVILEGE_MAP   SYS     SELECT          YES
PUBLIC  SYS   TABLE_PRIVILEGE_MAP    SYS     SELECT          YES
PUBLIC  SYS   V_$NLS_PARAMETERS      SYS     SELECT          NO
MARY    SYS   EMP                    SYS     INSERT          NO
MARY    SYS   EMP                    SYS     SELECT          YES
MARY    SYS   DO_COMP                SYS     EXECUTE         NO

7 rows selected.

DO_COMP is a stored procedure, not a table, but the privilege is found in this view.

About the output

The output shown above is only a sample of the many records available in this view. The first query in the UNION uses the pseudo column rownum (number assigned as the rows are found) to limit the values to five rows, and the second part of the union specifies that only rows for mary will be returned. We did this simply to limit the amount of data returned for the example. Note that the GRANTEE shown in the output is the user to whom the privilege has been granted, and that there is one entry for each privilege granted. So, it can be determined that any user has the right to query (SELECT) the DUAL table owned by sys.

In the GRANTABLE column a “YES” indicates that the GRANTEE may give (GRANT) the same rights to any other user, although a public grant makes this capability meaningless since all users already have the right to the indicated privilege. As you can see for the three rows for user mary, the right to INSERT rows is restricted to mary, but mary may grant the SELECT privilege to any other user. In the third row, mary has been granted the ability to run the procedure DO_COMP.

If mary grants SELECT to ralph WITH GRANT OPTION, and mary’s privileges are later revoked, what will happen? Will ralph still be able to SELECT on the table? The answer is no! When mary ’s privileges are revoked from the object, anyone to whom mary has granted privileges will also lose their privileges.

Note

For DBA_TAB_PRIVS, there are no differences between the Oracle8 version and earlier versions.

The DBA_USERS View

The DBA_USERS view (also available in the form ALL_USERS and USER_USERS) will be queried frequently because the view provides account information about all users in the database. The DBA and/or security manager should be familiar with the information this view provides. Columns include:

USERNAME

Database login name of the user. Limit 30 characters

USER_ID

System-generated ID number of the user

PASSWORD

30-character encrypted version of the user password

DEFAULT_TABLESPACE

Default tablespace where this user may create tables

TEMPORARY_TABLESPACE

Tablespace used by the system for temporary tables

CREATED

Date the user account was created

PROFILE

Name of the profile assigned to the user

ACCOUNT_STATUS

Added in Oracle8; shows whether an account is locked, expired, or unlocked

LOCK_DATA

Added in Oracle8; if status is “locked,” shows the date the account was locked

EXPIRY_DATE

Added in Oracle8; date the account is set to expire

EXTERNAL_NAME

Added in Oracle8; user’s external name

Unless you have instituted customized profiles (described in Chapter 6), the value of the PROFILE column will always read DEFAULT. The PASSWORD column is always a 16-character encrypted version of the password. There is no relationship between the length of the password and the value in this field. For instance, after creating user mary with a password of “y”, a query on this view resulted in the output shown below. Note that in the example, the column names were shortened with the “column ... format” command, and the row for mary has been italicized for emphasis.

SQL> SELECT username, profile, password 
  2    FROM dba_users;
USERNA PROFILE PASSWORD
------ ------- ------------------------------
SYS    DEFAULT D4C5016086B2DC6A
SYSTEM DEFAULT D4DF7931AB130E37
MARY   DEFAULT CCB9E1C39D4F9898
RALPH  DEFAULT 81B9E3D295699571
                  

The ROLE_ROLE_PRIVS View

The ROLE_ROLE_PRIVS view lists roles which are granted to roles. Its columns are detailed in the following list.

ROLE

Role name

GRANTED_ROLE

Role that was granted

ADMIN_OPTION

GRANT was with ADMIN OPTION

The only difference between the ROLE_ROLE_PRIVS view and the DBA_ROLE_PRIVS view is that the ROLE_ROLE_PRIVS only shows roles granted to roles. The DBA_ROLE_PRIVS shows users as well as roles. This tends to be very confusing. The following example shows Oracle7 output:

SQL> SELECT * 
  2    FROM role_role_privs 
  3   ORDER BY role, granted_role;
ROLE              GRANTED_ROLE                   ADM
----------------- ------------------------------ ---
DBA               EXP_FULL_DATABASE              NO
DBA               IMP_FULL_DATABASE              NO

For Oracle8, the same SELECT statement yields the following:

ROLE                           GRANTED_ROLE                   ADM
------------------------------ ------------------------------ ---
DBA                            DELETE_CATALOG_ROLE            YES
DBA                            EXECUTE_CATALOG_ROLE           YES
DBA                            EXP_FULL_DATABASE              NO
DBA                            IMP_FULL_DATABASE              NO
DBA                            SELECT_CATALOG_ROLE            YES
EXP_FULL_DATABASE              EXECUTE_CATALOG_ROLE           NO
EXP_FULL_DATABASE              SELECT_CATALOG_ROLE            NO
IMP_FULL_DATABASE              EXECUTE_CATALOG_ROLE           NO
IMP_FULL_DATABASE              SELECT_CATALOG_ROLE            NO

9 rows selected.

The ROLE_SYS_PRIVS View

The ROLE_SYS_PRIVS view (also available in the form USER_SYS_PRIVS) lists system privileges granted to roles. Columns include:

ROLE

Role name

PRIVILEGE

System privilege

ADMIN_OPTION

Grant was with ADMIN OPTION

This view shows system privileges, not object privileges. There will not be any specific object names such as “employees,” “salary,” or the like mentioned here. Instead, you will see privileges such as SELECT ANY TABLE or CREATE TABLE. The number of privileges varies with the version of Oracle. Version 7.3.2.2 lists 86 in the system_privilege_map table. The number usually remains consistent within a major release. We have shortened the listing to illustrate the types of privileges available and the types of roles with which they are generally associated.

In Oracle version 8.0.3, there are 139 rows selected (shown in the following example):

SQL> SELECT * 
  2    FROM role_sys_privs 
  3   ORDER BY 1, 2;

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
DBA                            ALTER ANY CLUSTER                        YES
DBA                            ALTER ANY INDEX                          YES
DBA                            ALTER ANY LIBRARY                        YES
DBA                            ALTER ANY PROCEDURE                      YES
DBA                            ALTER ANY ROLE                           YES
DBA                            ALTER ANY SEQUENCE                       YES
DBA                            ALTER ANY SNAPSHOT                       YES
DBA                            ALTER ANY TABLE                          YES
DBA                            ALTER ANY TRIGGER                        YES
DBA                            ALTER ANY TYPE                           YES
DBA                            ALTER DATABASE                           YES
DBA                            ALTER PROFILE                            YES
DBA                            ALTER RESOURCE COST                      YES
DBA                            ALTER ROLLBACK SEGMENT                   YES
DBA                            ALTER SESSION                            YES
DBA                            ALTER SYSTEM                             YES
DBA                            ALTER TABLESPACE                         YES
DBA                            ALTER USER                               YES
DBA                            ANALYZE ANY                              YES
DBA                            AUDIT ANY                                YES
DBA                            AUDIT SYSTEM                             YES
DBA                            BACKUP ANY TABLE                         YES
DBA                            BECOME USER                              YES
DBA                            COMMENT ANY TABLE                        YES
DBA                            CREATE ANY CLUSTER                       YES
DBA                            CREATE ANY DIRECTORY                     YES
DBA                            CREATE ANY INDEX                         YES
DBA                            CREATE ANY LIBRARY                       YES
DBA                            CREATE ANY PROCEDURE                     YES
DBA                            CREATE ANY SEQUENCE                      YES
DBA                            CREATE ANY SNAPSHOT                      YES
DBA                            CREATE ANY SYNONYM                       YES
DBA                            CREATE ANY TABLE                         YES
DBA                            CREATE ANY TRIGGER                       YES
DBA                            CREATE ANY TYPE                          YES
DBA                            CREATE ANY VIEW                          YES
DBA                            CREATE CLUSTER                           YES
DBA                            CREATE DATABASE LINK                     YES
DBA                            CREATE LIBRARY                           YES
DBA                            CREATE PROCEDURE                         YES
DBA                            CREATE PROFILE                           YES
DBA                            CREATE PUBLIC DATABASE LINK              YES
DBA                            CREATE PUBLIC SYNONYM                    YES
DBA                            CREATE ROLE                              YES
DBA                            CREATE ROLLBACK SEGMENT                  YES
DBA                            CREATE SEQUENCE                          YES
DBA                            CREATE SESSION                           YES
DBA                            CREATE SNAPSHOT                          YES
DBA                            CREATE SYNONYM                           YES
DBA                            CREATE TABLE                             YES
DBA                            CREATE TABLESPACE                        YES
DBA                            CREATE TRIGGER                           YES
DBA                            CREATE TYPE                              YES
DBA                            CREATE USER                              YES
DBA                            CREATE VIEW                              YES
DBA                            DELETE ANY TABLE                         YES
DBA                            DROP ANY CLUSTER                         YES
DBA                            DROP ANY DIRECTORY                       YES
DBA                            DROP ANY INDEX                           YES
DBA                            DROP ANY LIBRARY                         YES
DBA                            DROP ANY PROCEDURE                       YES
DBA                            DROP ANY ROLE                            YES
DBA                            DROP ANY SEQUENCE                        YES
DBA                            DROP ANY SNAPSHOT                        YES
DBA                            DROP ANY SYNONYM                         YES
DBA                            DROP ANY TABLE                           YES
DBA                            DROP ANY TRIGGER                         YES
DBA                            DROP ANY TYPE                            YES
DBA                            DROP ANY VIEW                            YES
DBA                            DROP PROFILE                             YES
DBA                            DROP PUBLIC DATABASE LINK                YES
DBA                            DROP PUBLIC SYNONYM                      YES
DBA                            DROP ROLLBACK SEGMENT                    YES
DBA                            DROP TABLESPACE                          YES
DBA                            DROP USER                                YES
DBA                            EXECUTE ANY LIBRARY                      YES
DBA                            EXECUTE ANY PROCEDURE                    YES
DBA                            EXECUTE ANY TYPE                         YES
DBA                            FORCE ANY TRANSACTION                    YES
DBA                            FORCE TRANSACTION                        YES
DBA                            GRANT ANY PRIVILEGE                      YES
DBA                            GRANT ANY ROLE                           YES
DBA                            INSERT ANY TABLE                         YES
DBA                            LOCK ANY TABLE                           YES
DBA                            MANAGE TABLESPACE                        YES
DBA                            RESTRICTED SESSION                       YES
DBA                            SELECT ANY SEQUENCE                      YES
DBA                            SELECT ANY TABLE                         YES
DBA                            UPDATE ANY TABLE                         YES
EXP_FULL_DATABASE              BACKUP ANY TABLE                         NO
EXP_FULL_DATABASE              EXECUTE ANY PROCEDURE                    NO
EXP_FULL_DATABASE              SELECT ANY TABLE                         NO
IMP_FULL_DATABASE              ALTER ANY TABLE                          NO
IMP_FULL_DATABASE              ALTER ANY TYPE                           NO
IMP_FULL_DATABASE              AUDIT ANY                                NO
IMP_FULL_DATABASE              BECOME USER                              NO
IMP_FULL_DATABASE              COMMENT ANY TABLE                        NO
IMP_FULL_DATABASE              CREATE ANY CLUSTER                       NO
IMP_FULL_DATABASE              CREATE ANY DIRECTORY                     NO
IMP_FULL_DATABASE              CREATE ANY INDEX                         NO
IMP_FULL_DATABASE              CREATE ANY LIBRARY                       NO
IMP_FULL_DATABASE              CREATE ANY PROCEDURE                     NO
IMP_FULL_DATABASE              CREATE ANY SEQUENCE                      NO
IMP_FULL_DATABASE              CREATE ANY SNAPSHOT                      NO
IMP_FULL_DATABASE              CREATE ANY SYNONYM                       NO
IMP_FULL_DATABASE              CREATE ANY TABLE                         NO
IMP_FULL_DATABASE              CREATE ANY TRIGGER                       NO
IMP_FULL_DATABASE              CREATE ANY TYPE                          NO
IMP_FULL_DATABASE              CREATE ANY VIEW                          NO
IMP_FULL_DATABASE              CREATE DATABASE LINK                     NO
IMP_FULL_DATABASE              CREATE PROFILE                           NO
IMP_FULL_DATABASE              CREATE PUBLIC DATABASE LINK              NO
IMP_FULL_DATABASE              CREATE PUBLIC SYNONYM                    NO
IMP_FULL_DATABASE              CREATE ROLE                              NO
IMP_FULL_DATABASE              CREATE ROLLBACK SEGMENT                  NO
IMP_FULL_DATABASE              CREATE TABLESPACE                        NO
IMP_FULL_DATABASE              CREATE USER                              NO
IMP_FULL_DATABASE              DROP ANY CLUSTER                         NO
IMP_FULL_DATABASE              DROP ANY DIRECTORY                       NO
IMP_FULL_DATABASE              DROP ANY INDEX                           NO
IMP_FULL_DATABASE              DROP ANY LIBRARY                         NO
IMP_FULL_DATABASE              DROP ANY PROCEDURE                       NO
IMP_FULL_DATABASE              DROP ANY ROLE                            NO
IMP_FULL_DATABASE              DROP ANY SEQUENCE                        NO
IMP_FULL_DATABASE              DROP ANY SNAPSHOT                        NO
IMP_FULL_DATABASE              DROP ANY SYNONYM                         NO
IMP_FULL_DATABASE              DROP ANY TABLE                           NO
IMP_FULL_DATABASE              DROP ANY TRIGGER                         NO
IMP_FULL_DATABASE              DROP ANY TYPE                            NO
IMP_FULL_DATABASE              DROP ANY VIEW                            NO
IMP_FULL_DATABASE              DROP PROFILE                             NO
IMP_FULL_DATABASE              DROP PUBLIC DATABASE LINK                NO
IMP_FULL_DATABASE              DROP PUBLIC SYNONYM                      NO
IMP_FULL_DATABASE              DROP ROLLBACK SEGMENT                    NO
IMP_FULL_DATABASE              DROP TABLESPACE                          NO
IMP_FULL_DATABASE              DROP USER                                NO
IMP_FULL_DATABASE              EXECUTE ANY PROCEDURE                    NO
IMP_FULL_DATABASE              INSERT ANY TABLE                         NO
IMP_FULL_DATABASE              SELECT ANY TABLE                         NO
139 rows selected.

Note that there are no entries for CONNECT and RESOURCE in this list. The system privileges granted to the CONNECT and RESOURCE roles are not visible from this view. They are visible from the DBA_SYS_PRIVS view, however, and the grants follow:

SQL> SELECT * 
  2    FROM dba_sys_privs 
  3   WHERE grantee = 'CONNECT';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        ALTER SESSION                            NO
CONNECT                        CREATE CLUSTER                           NO
CONNECT                        CREATE DATABASE LINK                     NO
CONNECT                        CREATE SEQUENCE                          NO
CONNECT                        CREATE SESSION                           NO
CONNECT                        CREATE SYNONYM                           NO
CONNECT                        CREATE TABLE                             NO
CONNECT                        CREATE VIEW                              NO

8 rows selected.

SQL> SELECT * 
  2    FROM dba_sys_privs 
  3   WHERE grantee = 'RESOURCE';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TABLE                             NO
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE TYPE                              NO

6 rows selected.
                  
                  
                  

The ROLE_TAB_PRIVS View

The ROLE_TAB_PRIVS view (also available in the form USER_TAB_PRIVS) lists table privileges granted to roles. Columns include:

ROLE

Role name

OWNER

Table owner

TABLE_NAME

Table name or sequence name

COLUMN_NAME

Column name if applicable

PRIVILEGE

Table privilege

GRANTABLE

Role may be granted by holder

This view is a complement to the ROLE_SYS_PRIVS view. Whereas that view shows system privileges, this view shows privileges granted on objects such as tables, views, and stored programs. These privileges are shown as granted to roles, not users.

SQL> SELECT * 
  2    FROM role_tab_privs 
  3   ORDER BY 1, 3, 5;
ROLE              ONR TABLE_NAME   COLUMN_NAME     PRIVILEGE       GRANTABLE
----------------- --- ------------ --------------- --------------- ---
CONNECT           SYS TOTEXT                       EXECUTE         NO
EXP_FULL_DATABASE SYS INCEXP                       DELETE          NO
EXP_FULL_DATABASE SYS INCEXP                       INSERT          NO
EXP_FULL_DATABASE SYS INCEXP                       UPDATE          NO
EXP_FULL_DATABASE SYS INCFIL                       DELETE          NO
EXP_FULL_DATABASE SYS INCFIL                       INSERT          NO
EXP_FULL_DATABASE SYS INCFIL                       UPDATE          NO
EXP_FULL_DATABASE SYS INCVID                       DELETE          NO
EXP_FULL_DATABASE SYS INCVID                       INSERT          NO
EXP_FULL_DATABASE SYS INCVID                       UPDATE          NO
9 rows selected.

In Oracle8, there were no rows selected.

When stored procedures and functions were added to Oracle functionality, the decision was made to include them as a part of this view. As you can see, the CONNECT role has the EXECUTE privilege on the TOTEXT object. This is a stored function, not a table, so the view name and the column name are somewhat misleading.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required