Subject:            How to determine a User's Resource Privileges
Last Revision Date: 11 September 1994
Author:Ellen Tafeen                                                                  



What may initially appear as a simple procedure can often become a
frustrating experience. Take for example a DBA's need to relocate a
user's tables from one tablespace to another. Let's assume USR1 has
resource privilege on the System tablespace where he has created his
tables and now would like to move them to tablespace TS1. The procedure
entails:

a. User export
b. Drop user's tables
c. Create tablespace TS1 ..... (if necessary)
d. Revoke resource from USR1
e. Revoke resource on system from USR1
f. Grant resource on TS1 to USR1
g. Alter user USR1 default tablespace TS1

Success strongly depends on knowing specifically which tablespaces a user
has resource privileges for. By default, an import will attempt to import
tables into the same tablespace they were exported from.  However, if the
user's resource privilege to this tablespace has been revoked, import
will create the objects in the user's default tablespace. Thus, to
successfully relocate tables to a different tablespace the user being
imported must not have resource privilege on the tablespace the tables
have been exported from.

The dictionary views DBA_TS_QUOTAS and DBA_USERS provide the key to
evaluating which resource privileges a user has been granted.

a. SELECT RESOURCE_PRIV FROM DBA_USERS WHERE USERNAME = 'USR1'

  1. If resource_priv=1, the user has blanket resource privileges
     on all tablespaces. You can correctly assume that the DBA has issued
     'grant resource to usr1'. Accordingly you must revoke this blanket
     resource grant with a 'revoke resource from usr1'.

  2. If resource_priv=0, the user has resource privileges on
     specific tablespaces. Query DBA_TS_QUOTAS to determine which table-
     spaces.

b. SELECT MAX_BLOCKS, TABLESPACE_NAME FROM DBA_TS_QUOTAS WHERE
   USERNAME = 'USR1'

  1. If max_blocks=negative number, the user has unlimited space quota
     on this tablespace.

  2. If max_blocks=positive number, the user has a specific space quota
     on this tablespace.

  3. If max_blocks=0, the user previously had a quota on this tablespace
     but it since has been revoked.

ITEMS TO NOTE:

a. 'grant resource to usr1' provides blanket resource on all tablespaces
    including system.

   'grant resource on system to usr1' provides specific resource privilege
    on the system tablespace.

    If a DBA issued both of the above grants, issue the following two
    statements to completely revoke a user's resource privilege on the
    system tablespace:

             'revoke resource from usr1'
             'revoke resource on system from usr1'

b. If a user has DBA privileges, he has implicitly been granted resource
   on the system tablespace. Thus importing this user's tables from
   the system tablespace to TS1 will not work. You must then revoke
   the user's dba privilege prior to executing the import.

c. If a user has been issued blanket resource on all tablespaces,
   DBA_TS_QUOTAS will not display entries for any of the tablespaces
   until the user creates an object in a tablespace. It is important
   to query resource_priv from DBA_USERS to determine blanket resource
   privileges.