So far, you have learned the commands needed to give and take permissions away from users on both the system as a whole and individual database objects. When a user is having access problems or when you are performing routine security audits, it is necessary to be able to determine what privileges are currently granted in the database. To do this, there are two DBA views (dba_sys_privs and dba_tab_privs) that can be queried to find this information. Views are discussed in a later chapter, but for now, think of these DBA views as objects that you can query to find out information. The first provides insight into the system privileges that have been granted to users. The second shows the table access that has been granted. The following are examples of queries to the dba_sys_privs and the dba_tab_privs views. Note that you can use where clauses to focus in on the particular tables or users you want:

SQL> select * from dba_sys_privs;





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

DBA                            ALTER ANY CLUSTER                       YES

DBA                            ALTER ANY INDEX                         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 DATABASE                          YES

DBA                            ALTER PROFILE                           YES

DBA                            ALTER RESOURCE COST                     YES

DBA                            ALTER ROLLBACK SEGMENT                  YES

DBA                            ALTER SESSION                           YES



SQL> select * from dba_tab_privs

  2  where owner=‘JGREENE’;



GRANTEE                       OWNER                         TABLE_NAME

----------------------------- ----------------------------- --------------

GRANTOR                       PRIVILEGE                               GRA

----------------------------- --------------------------------------- ---

BSMITH                        JGREENE                       GOLF_SCORES

JGREENE                       DELETE                                  NO



JSMITH                        JGREENE                       GOLF_SCORES

JGREENE                       DELETE                                  NO

JSMITH                        JGREENE                       GOLF_SCORES

JGREENE                       SELECT                                  NO



SQL>

You may have noticed the ADM/GRANTABLE columns in the output of these views. This column reflects whether this user has administrative privileges to grant this privilege to other users. For example, if you grant select on the golf_scores table to bsmith with the admin option, bsmith can grant select on golf_scores to other users. bsmith can’t grant delete on golf_scores to other users unless bsmith has the grant option associated with the delete privilege on this table. Here is the format for granting the admin option to a user:

grant privilege to user/role with admin option;

Limiting creation privileges is a start to controlling the contents of your database. You may also wish to limit certain creation privileges granted to users. For example, you won’t want one developer to consume every byte of space in every tablespace in your instance. A tool that you can use to limit this access is the tablespace quota. For now, just understand that you can limit the amount of space consumed by the user and also specify which tablespaces the user can use for entering data. For example, the following grants a limit of 10M in the user’s tablespace to user jsmith (once quotas are implemented, Oracle assumes that you have a quota of zero on all tablespaces for which users have not explicitly been granted a quota, unless that user has the unlimited tablespace system privilege):

alter user jsmith quota 10M on users;

Finally, you may wish to grant certain privileges to every user in the database. Perhaps you live in a very open environment where everyone should be enabled to view all data tables. Perhaps you do not wish to have to remember to grant create session (which is needed to even connect to your Oracle instance) to every user ID in the system. Anyway, you can grant privileges to everyone by putting the word public in as the grantee:

grant create session to public;