Controlling User Access 16 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder16Ć2 Schedule: Timing Topic 25 minutes Lecture 20 minutes Practice 45 minutes Total Class Management Note: Files required for this lesson are: Demonstration: None Practice: None This lesson should give students an overview of Oracle7 Server’s security options. It is not intended to go into tremendous detail on each topic. Controlling User Access 16Ć3 Objectives This lesson describes the Oracle7 Server decentralized security system. Using the commands covered in this lesson, you can control database access to specific objects and add new users with different levels of access privileges. You can provide alternative names for objects by using the CREATE SYNONYM command. At the end of this lesson, you should be able to D Explain the concept of the database security model. D Describe system privileges. D Set up and maintain database access by using roles. D Identify object privileges. D Change a password. D Grant and revoke object privileges. D Create synonyms for ease of table access. Introduction to Oracle: SQL and PL/SQL Using Procedure Builder16Ć4 Server Controlling User Access 16Ć5 Overview In a multiple-user environment, you want to maintain security of the database access and use. Oracle7 Server database security allows you to D Control database access. D Give access to specific objects in the database. D Confirm given and received privileges with the Oracle data dictionary. D Create synonyms for database objects. Database security can be classified into two categories: system security and data security. System security covers access and use of the database at the system level, such as username and password, disk space allocated to users, and system operations allowed by the user. Database security covers access and use of the database objects and the actions that those users can have on the objects. Privileges Privileges are the right to execute particular SQL statements. The database administrator is a high level user with the ability to grant users access to the database and its objects. The users require system privileges to gain access to the database and object privileges to manipulate the content of the objects in the database. Users can also be given the privilege to grant additional privileges to other users or to roles, which are named groups of related privileges. Schema A schema is a collection of objects, such as tables, views, and sequences. The schema is owned by a database user and has the same name as that user. For more information, see Oracle7 Server Application Developer’s Guide, Release 7.3, “Establishing a Security Policy” section and Oracle7 Server Concepts Manual, “Database Security” topic. Class Management Note: PowerPoint: The bottom slide contains the build feature. Introduction to Oracle: SQL and PL/SQL Using Procedure Builder16Ć6 Controlling User Access 16Ć7 System Privileges There are more than eighty system privileges available for users and roles. System privileges are typically provided by the database administrator. Typical DBA Privileges System Privilege Operations Authorized CREATE USER Allows grantee to create other Oracle users (a privilege required for a DBA role). DROP USER Drops another user. DROP ANY TABLE Drops a table in any schema. BACKUP ANY TABLE Backs up any table in any schema with the export utility. Creating a User The DBA creates a new Oracle7 Server user by allocating a number of system privileges to that user. These privileges in turn determine what the user can do at the database level. The DBA creates the user by executing the CREATE USER command. The user does not have any system privileges. Abridged Syntax CREATE USER user IDENTIFIED BY password; where: user is the name of the user to be created. password specifies that the user must log in with this password. For more information, see Oracle7 Server SQL Reference, Release 7.3, “GRANT” (System Privileges and Roles) and “CREATE USER.” Introduction to Oracle: SQL and PL/SQL Using Procedure Builder16Ć8 Controlling User Access 16Ć9 System Privileges continued Now that the DBA has created a user, the DBA can assign privileges to that user. Typical User Privileges System Privilege Operations Authorized CREATE SESSION Connect to the database. CREATE TABLE Create tables in the user’s schema. CREATE SEQUENCE Create a sequence in the user’s schema. CREATE VIEW Create a view in the user’s schema. CREATE PROCEDURE Create a stored procedure, function, or package in the user’s schema. Granting System Privileges The DBA uses the GRANT command to allocate system privileges to the user. Once the user has been granted the privileges, the user can immediately use those privileges. Syntax GRANT privilege [, privilege ] TO user [, user ]; where: privilege is the system privilege to be granted. user is the name of the user. Note: The above syntax is abridged. Class Management Note: Note for page 16-10. Question: What is a role? Try to elicit answers from the students. Answer: See the next slide for the answer. PowerPoint: The top slide on page 16-10 contains the build feature. Introduction to Oracle: SQL and PL/SQL Using Procedure Builder16Ć10 Class Management Note: Discuss the four following points about roles: 1.Named groups of related privileges 2.Can be granted to users 3.Simplifies the process of granting and revoking privileges 4.Created by a DBA [...]... 5.0 They are not the same Controlling User Access 16Ć11 16Ć12 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Changing Your Password Every user has a password that is initialized by the DBA when the user is created You can change your password by using the ALTER USER command Syntax ALTER USER user IDENTIFIED BY password; where: user password is the name of the user specifies the new password... SELECT privilege on a table to user B including the WITH GRANT OPTION, then user B can grant to user C the SELECT privilege WITH GRANT OPTION, and user C can then grant to user D the SELECT privilege If user A the revokes the privilege from user B, then the privileges granted to users C and D are also revoked Controlling User Access 16Ć23 Class Management Note: A common use of synonyms is to run an application... any user, allowing that user to create public synonyms Controlling User Access 16Ć27 16Ć28 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Summary DBAs establish initial database security for users by assigning privileges to the users D The DBA creates users who must have a password The DBA is also responsible for establishing the initial system privileges for a user D Once the user. .. Do You Have? You can access the data dictionary to view the privileges you have Data Dictionary Table Description ROLE_SYS_PRIVS System privileges granted to roles ROLE_TAB_PRIVS Table privileges granted to roles USER_ ROLE_PRIVS Roles accessible by the user USER_TAB_PRIVS_MADE Object privileges granted on the user s objects USER_ TAB_PRIVS_RECD Object privileges granted to the user USER_COL_PRIVS_MADE... the columns of the user s objects USER_ COL_PRIVS_RECD Object privileges granted to the user on specific columns Controlling User Access 16Ć21 16Ć22 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Revoking Object Privileges Remove privileges granted to other users by using the REVOKE command When you use the REVOKE command, the privileges you specify are revoked from the users you name,... Example As user Alice, allow user Scott to access your S_DEPT table with the privileges to query the table and add rows to the table Allow Scott to give others these privileges SQL> 2 3 4 GRANT select, insert ON s_dept TO scott WITH GRANT OPTION; Grant succeeded The PUBLIC Keyword An owner of a table can grant access to all users by using the PUBLIC keyword Example As user Scott, allow all users on the... If a user is granted a privilege WITH GRANT OPTION, then that user can also grant the privilege WITH GRANT OPTION, so that a long chain of grantees is possible, but no circular grants are permitted If the owner revokes a privilege from a user who granted the privilege to other users, then the REVOKE cascades to all privileges granted For example, if user A grants SELECT privilege on a table to user. .. object privileges Controlling User Access 16Ć17 Class Management Note: If a statement does not use the full name of an object, then the Oracle7 Server implicitly prefixes the object name with the current user s name (or schema) If user Scott queries the S_DEPT table, then the system will SELECT from table SCOTT.S_DEPT If a statement does not use the full name of an object, and the current user does not... your password, there are many other options You must have the ALTER USER privilege to change any other option For more information, see Oracle7 Server SQL Reference, Release 7.3, “ALTER USER. ” Controlling User Access 16Ć13 16Ć14 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Granting Object Privileges The DBA can allow users to perform a particular action on a specific table, view, sequence,... created an object, the user can pass along any of the available object privileges to other users or to all users by using the GRANT command D A DBA can create roles by using the CREATE ROLE command to pass along a collection of system or object privileges to multiple users Roles make granting and revoking privileges easier to maintain D Users can change their password by using the ALTER USER command D You . Builder16Ć4 Server Controlling User Access 16Ć5 Overview In a multiple -user environment, you want to maintain security of the database access and use. Oracle7. is a high level user with the ability to grant users access to the database and its objects. The users require system privileges to gain access to the database