Administering Database Users

/**/

Revoking system privileges from a user will not cascade to anyone

the revokee granted the same system privilege if he had been granted the

privilege WITH ADMIN OPTION.

Object privileges revoked from a user to whom they were granted

WITH GRANT OPTION will also be revoked from anyone the revokee

granted them to; i.e., the revoke will cascade.

It is not possible to grant object privileges to roles WITH GRANT OPTION

--Determining System Privileges Granted

• DBA_SYS_PRIVS for all system privileges granted to all users

• USER_SYS_PRIVS (for system privileges granted to the currently logged-on use

--Determining the Object Privileges Granted

• USER_TAB_PRIVS_MADE Object privileges the user has granted to others

on his schema objects

• USER_TAB_PRIVS_RECD Privileges granted to the user to objects in other

schemas

• ALL_TAB_PRIVS_MADE Object privileges the user has granted to others on

his and other schema objects

• ALL_TAB_PRIVS_RECD Privileges granted to the user to objects in his and

other schemas

• USER_COL_PRIVS_MADE Column privileges granted by the user to tables

in his schema

• USER_COL_PRIVS_RECD Column privileges granted to the user to columns

in tables in other schemas

• ALL_COL_PRIVS_MADE Column privileges granted by the user to tables in

his and other schemas

• ALL_COL_PRIVS_RECD Column privileges granted to the user to columns

in tables in his and other schemas

/*Creating and Managing Roles*/

With roles, there are no cascading revokes for object privileges

granted to roles, because you cannot grant object privileges to a role WITH GRANT

OPTION; the syntax is not allowed. However, you can grant a system privilege, or

another role, to a role WITH ADMIN OPTION.Doing so allows anyone granted the

role to grant those system privileges or roles to others.

--Dropping Roles

In order to drop a role, you must be the user who created the role, have been granted the DROP

ANY ROLE system privilege, or have been granted the role WITH ADMIN OPTION.

When you drop a role, any user or role to which the role being dropped has been

granted will have it revoked at the time the role is dropped

--Predefined Roles

Every Oracle 10g database, once created and once the CATPROC.SQL and CATALOG.SQL scripts are run, will include a number of predefined roles.

/*Profiles*/

--A user cannot himself change his active profile. Only the DBA can modify

a user’s profile or the profile limits.

CREATE PROFILE profile_name LIMIT

[SESSIONS_PER_USER value]

[CPU_PER_SESSION value]

[CPU_PER_CALL value]

[CONNECT_TIME value]

[IDLE_TIME value]

[LOGICAL_READS_PER_SESSION value]

[LOGICAL_READS_PER_CALL value]

[COMPOSITE_LIMIT value]

[PRIVATE_SGA bytes [K|M]]

--

In order to have a profile limit a user’s actions, you need to enable them by setting them in the parameter

file (SPFILE or INIT.ORA file) or by changing the value of the RESOURCE_LIMIT

initialization parameter to TRUE with the ALTER SYSTEM command.

--determine the currently configured profile values

DBA_PROFILES