Management of Oracle 9i roles

Oracle 9i Predefined roles

Table 8.2 Oracle 9i Predefined roles

Role name	Description
CONNECT	Database connection role, used to connect to a database, with the right to create clusters, database links, sequences, synonyms, tables, and views, and modify sessions
Dba	The database administrator role, with all system permissions created with the admin option, you can grant system permissions to other users or roles
Delete_catalog_role	Delete a directory role to delete or reconstruct a data dictionary
Execute_catalog_role	Performs directory roles and can execute all system packs
Exp_full_database	Ability to use an exporter to perform a full and incremental export of a database
Imp_full_database	Ability to use an importer to perform a full import of a database
RESOURCE	You can create clusters, tables, sequences, and pl/sql programmatic objects, including procedures, packages, triggers, and so on
Select_catalog_role	Query a data dictionary table or view

To create a role in Enterprise Manager

(1) as shown in Figure 8.16.

(2) The General Information tab of the Create role appears as shown in Figure 8.17.
(3) Figure 8.18 shows the Roles tab for creating roles. Used to assign child roles to multiple roles.

(4) Figure 8.19 shows the System Permissions tab for creating roles.
(5) as shown in Figure 8.20, the Object Permissions tab of the Create role.

(6) Figure 8.21 shows the "Consumer groups" tab of the Create role.
(7) When the role is successfully created, the interface shown in Figure 8.22 appears.

(8) The SQL code for the above process to create the role is as follows.
―――――――――――――――――――――――――――――――――――――
CREATE role "Temprole"
Identified by "Temprole";
GRANT ALTER any of INDEX to "Temprole" with ADMIN OPTION;
GRANT SELECT any TABLE to "Temprole" with ADMIN OPTION;
GRANT "CONNECT" to "Temprole" with ADMIN OPTION;
GRANT "DBA" to "Temprole" with ADMIN OPTION;
BEGIN
Dbms_resource_manager_privs.grant_switch_consumer_group (
Grantee_name => ' Temprole ',
Consumer_group => ' Default_consumer_group ',
Grant_option => FALSE
);
End;
―――――――――――――――――――――――――――――――――――――
"See CD-ROM File": 8th Chapter \createrole.sql.

To create a role in "Sqlplus Worksheet"

(1) Executing the Createrole.sql file directly in "Sqlplus Worksheet" will complete the creation of the role, as shown in Figure 8.23.
(2) indicates that the role Temprole has been successfully created.


Changes to Roles

(1) as shown in Figure 8.24.

(2) in the various tabs that appear, you can modify the various configuration parameters of the role, and the SQL statement of the corresponding roles is "ALTER role" or "REVOKE".
Example 1: The SQL code that changes the role's authentication mode to "external" is as follows.
―――――――――――――――――――――――――――――――――――――
ALTER role "temprole" identified externally;
―――――――――――――――――――――――――――――――――――――
"See CD-ROM File": 8th Chapter \alterrole.sql.
Example 2: The SQL code that deletes the system permission "DBA" for the role is as follows.
―――――――――――――――――――――――――――――――――――――
REVOKE "DBA" from "Temprole";
―――――――――――――――――――――――――――――――――――――
"See CD-ROM File": 8th Chapter \revokerole.sql.

Deletion of roles

(1) The "Role deletion confirmation" interface as shown in Figure 8.25.

(2) The corresponding SQL code for the above process is as follows.
―――――――――――――――――――――――――――――――――――――
DROP role Temprole;
―――――――――――――――――――――――――――――――――――――
"See CD-ROM File": 8th Chapter \droprole.sql.