30 Questions
What type of privileges do users need to manipulate the content of objects in the database?
Object privileges
Which level of security covers access and use of the database at the system level?
System Security
What is a schema in the context of a database?
A collection of objects owned by a database user
Which system privilege allows a user to create other Oracle users?
Create User
What is the primary role that usually holds system privileges such as creating or removing users?
DBA (Database Administrator)
Which type of privileges are needed to grant additional privileges to other users or roles in a database?
Role Privileges
What privilege is required for a user to access a database?
CREATE SESSION
Which privilege allows a user to create tables in their schema?
CREATE TABLE
What is automatically performed behind the scenes in Oracle Application Express?
CREATE SESSION
Which privilege is needed to create a view in a user's schema?
CREATE VIEW
What type of privileges are associated with each database object?
Object privileges
Which of the following is NOT listed as an object privilege for tables in the provided content?
INDEX
What is the purpose of controlling user access in a database?
To maintain security of the database access and use
What are the two types of privileges discussed in the text?
Object privileges and System privileges
What is required to enable a user to have access to a database?
GRANT command
Which statement assigns privileges to objects in a user's schema to other users and/or PUBLIC?
GRANT ... ON ... TO statement
How does working with sequences relate to controlling user access in a multiple-user environment?
Sequences are used to assign unique numbers for identification purposes
What is the primary focus of system security in Oracle Server database?
Controlling access and use of the database at the system level
Why is data security important in a multiple-user environment?
To control who can alter, delete, update, or insert database objects
How many distinct system privileges exist in Oracle Server database?
More than 100
Which user has the ability to grant users access to the database and its objects in Oracle Server database?
DBA
What does data security in Oracle Server database primarily focus on?
Object-level privileges
Which of the following is NOT a feature of Oracle Server database security?
Executing DML statements on database objects
What is the role of the DBA in Oracle Server database security?
Granting users access to the database and its objects
What does the error message 'table or view does not exist' indicate in Oracle?
The named table or view does not exist
What causes the error message 'table or view does not exist' in Oracle?
Naming a non-existent table or view
Where can you check the system privileges granted to roles in Oracle?
ROLE_SYS_PRIVS view
What type of information can be found in the 'USER_COL_PRIVS_RECD' data dictionary view in Oracle?
Object privileges granted on specific columns to the user
In Oracle, what does the 'USER_TAB_PRIVS_MADE' data dictionary view provide information about?
Object privileges granted by the user on their objects
Where can a user view their privileges in Oracle Application Express Developer?
Security Reports section
Study Notes
Database Security
- Database security is classified into two categories: system security and object security.
- System security covers access and use of the database at the system level, such as creating users, usernames, and passwords, and allocating disk space to users.
- More than 100 distinct system privileges exist, and these are usually held only by the DBA.
Privileges
- Users require system privileges to gain access to the database.
- Object privileges are required to manipulate the content of objects in the database.
- Users can be granted the privilege to grant additional privileges to other users or to roles, which are named groups of related privileges.
Schemas
- 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.
- A schema name is a combination of the user's country/state, school, course, and student number.
System Privileges
- System privileges include CREATE USER, CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, and CREATE VIEW.
- The DBA would not normally grant certain system privileges, such as DROP TABLE, to other users.
Object Privileges
- Object privileges cover access and use of the database objects and the actions users can have on those objects.
- Each object has a particular set of grantable privileges, including ALTER, DELETE, EXECUTE, INDEX, INSERT, REFERENCES, SELECT, and UPDATE.
- Object privileges can be granted to other users or to PUBLIC using the GRANT command.
Granting Privileges
- The GRANT command is used to grant privileges to users or PUBLIC, and can be used to grant system privileges or object privileges.
- The GRANT command requires the privilege to be granted, the object or schema to which the privilege is granted, and the user or role to which the privilege is granted.
Viewing Privileges
- Users can view the privileges they have using the data dictionary views, such as ROLE_SYS_PRIVS, ROLE_TAB_PRIVS, USER_ROLE_PRIVS, USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD, USER_COL_PRIVS_MADE, USER_COL_PRIVS_RECD, and USER_SYS_PRIVS.
- The data dictionary views can be accessed using Oracle Application Express Developer.
Learn about object privileges and system privileges in SQL. Understand how to enable a user to access a database and assign privileges to objects in their schema using the GRANT statement. Practice querying the data dictionary for confirming privileges.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free