OCP - Oracle9i Database Administrator Fundamentals 1 Exam Prep - Module 16: Managing Privileges

Practice: Identify System and Object Privileges

This practice covers granting and revoking a System privilege for a user that allows the user to connect to a database.

Assumptions

An Oracle9i database is running and available for access. The database contains a USERS data tablespace and a TEMP temporary tablespace.

Access to the SQL*PLUS utlility is available.

A DBA level user account id and password are available for use. (System is used in the example.)

A database user JSMITH exists in the database but has no system or object privileges granted. (See Topic 36 Practice.)

Instructions

1. Attempt to login to the database as user JSMITH to test whether the user has the System privilege to connect to the database.

$ sqlplus

SQL*Plus: Release 9.0.1.0.0 - Production on Fri Feb 1 13:48:47 2002

Enter user-name: jsmith

Enter password:

ERROR:

ORA-01045: user JSMITH lacks CREATE SESSION privilege; logon denied

Enter user-name:

2. Login to the database as a DBA level user (i.e. system). Grant the CREATE SESSION System privilege to JSMITH to allow the user to connect to the database.

SQL> GRANT CREATE SESSION TO jsmith;

Grant succeeded.

SQL>

3. Exit from the DBA level session. Attempt to login again to the database as user JSMITH to test whether the user has the System privilege to connect to the database

$ sqlplus

SQL*Plus: Release 9.0.1.0.0 - Production on Fri Feb 1 13:53:22 2002

Enter user-name: jsmith

Enter password:

Connected to:

Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production

With the Partitioning option

JServer Release 9.0.1.0.0 - Production

SQL>

4. Exit from the JSMITH session. Login again as a DBA level user. Revoke the CREATE SESSION System privilege from JSMITH to prevent the user from connecting to the database.

SQL> REVOKE CREATE SESSION FROM jsmith;

Revoke succeeded.

SQL> SELECT USERNAME, TABLESPACE_NAME, BYTES, MAX_BYTES

2 FROM DBA_TS_QUOTAS

3 WHERE USERNAME = ‘JSMITH’;

no rows selected.

SQL>

5. Attempt to login to the database as user JSMITH to test whether the user has the system privilege to connect to the database.

$ sqlplus

SQL*Plus: Release 9.0.1.0.0 - Production on Fri Feb 1 13:48:47 2002

Enter user-name: jsmith

Enter password:

ERROR:

ORA-01045: user JSMITH lacks CREATE SESSION privilege; logon denied

Enter user-name:

6. Login to the database as a DBA level user (i.e. system). Grant the CREATE SESSION System privilege to JSMITH to allow them to connect to the database. (Needed for later practices.)

SQL> GRANT CREATE SESSION TO jsmith;

Grant succeeded.

SQL>

Practice: Grant and Revoke Privileges

This practice covers granting and revoking object privileges for a user.

Assumptions

An Oracle9i database is running and available for access.

Access to the SQL*PLUS utlility is available.

A DBA level user account id and password are available for use. (System is used in the example.)

A database user JSMITH exists in the database and has CREATE SESSION system privilege but has no other system or object privileges granted. (See Topic 36 and Topic 38 Practices)

Instructions

1. Login to the database as user JSMITH.

$ sqlplus

SQL*Plus: Release 9.0.1.0.0 - Production on Fri Feb 1 13:48:47 2002

Enter user-name: jsmith

Enter password:

Connected to:

Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production

With the Partitioning option

JServer Release 9.0.1.0.0 - Production

2. List the tables on which JSMITH has object privileges. Attempt to query an object on which the user has no object privilege. (For example DBA_TAB_PRIVS.)

SQL> SELECT OWNER, TABLE_NAME, GRANTOR, PRIVILEGE

2 FROM USER_TAB_PRIVS;

no rows selected

SQL> SELECT COUNT(*) FROM DBA_TAB_PRIVS;

select count(*) from dba_tab_privs

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

3. Exit the from JSMITH session. Login as a DBA level user. Grant user JSMITH the ability to view the rows of the DBA_TAB_PRIVS table.

SQL> GRANT SELECT ON DBA_TAB_PRIVS TO jsmith;

Grant succeeded.

SQL>

4. Exit from the DBA level session. Login again as the JSMITH user. List the tables on which user JSMITH has object privileges. Attempt to query the object on which the user has an object privilege.

SQL> SELECT OWNER, TABLE_NAME, GRANTOR, PRIVILEGE

2 FROM USER_TAB_PRIVS;

OWNER TABLE_NAME GRANTOR PRIVILEGE

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

SYS DBA_TAB_PRIVS SYS SELECT

SQL> SELECT COUNT(*) FROM DBA_TAB_PRIVS;

COUNT(*)

---------

2478

SQL>

5. Exit the from JSMITH session. Login as a DBA level user. Revoke from user JSMITH the ability to view the rows of the DBA_TAB_PRIVS table.

SQL> REVOKE SELECT ON DBA_TAB_PRIVS FROM jsmith;

Revoke succeeded.

SQL>

6. Exit from the DBA level session. Login again as the JSMITH user. Again, list the tables on which JSMITH has object privileges. Attempt to query an object on which the user has no object privilege.

SQL> SELECT OWNER, TABLE_NAME, GRANTOR, PRIVILEGE

2 FROM USER_TAB_PRIVS;

no rows selected

SQL> SELECT COUNT(*) FROM DBA_TAB_PRIVS;

select count(*) from dba_tab_privs

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>