4.5 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. 4.5.1 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. 4.5.2 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 setas 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.  | 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. 4.5.3 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. 4.5.4 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. 4.5.5 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. 4.5.5.1 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. 4.5.5.2 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. | | For DBA_TAB_PRIVS, there are no differences between the Oracle8 version and earlier versions. | | 4.5.6 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 4.5.7 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. 4.5.8 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. 4.5.9 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. | 
