By Melanie Caffrey
January/February 2018
This article is the thirteenth, and last, in a series that helps you build on the fundamentals that you learned in the 12-part SQL 101 series in Oracle Magazine. The previous Beyond SQL 101 article, “Becoming Privileged and Creating Synonymously,” taught you how to create, alter, and drop users and how users relate to schemas. You learned what system privileges and object privileges are and how privileges are granted and revoked. Additionally, you saw that roles are collections of privileges that can be granted and revoked in a similar fashion as individual privileges. Lastly, you witnessed how privileges and roles should be granted on a discretionary as-needed basis for security and ease of administration.
In this article, you will
To try out the examples in this series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Enterprise Edition 12c Release 2 (12.2.0.1.0). If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_201 is the user account to use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for the SYS and SYSTEM users and make a note of them.
Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_201 schema that are used for this article’s examples. (View the script in a text editor for execution instructions.)
The Object of Your Intention
As you learned in the previous Beyond SQL 101 article, “Becoming Privileged and Creating Synonymously,” object privileges allow you the right to perform actions including but not limited to
Listing 1 re-creates the BEYOND_101 user and demonstrates the result received when the user attempts to query the EMPLOYEE table. Although the table exists in schema SQL_201, the query result is
ORA-00942: table or view does not exist
This error means that until the BEYOND_101 user has been granted the appropriate access for the object, Oracle Database does not know that the table or view is an object upon which the BEYOND_101 user may perform actions. This user must be granted the necessary object privilege from the table’s owner.
Code Listing 1: A user must be granted query access to a database table the user does not own
SQL> set lines 60
SQL> connect / as sysdba
Connected.
SQL> alter session set container=bynd;
Session altered.
SQL> CREATE USER beyond_101
2 IDENTIFIED BY 201
3 DEFAULT TABLESPACE users
4 TEMPORARY TABLESPACE temp
5 QUOTA 100M ON USERS;
User created.
SQL> GRANT CREATE SESSION, CREATE TABLE TO beyond_101;
Grant succeeded.
SQL> connect beyond_101@bynd
Enter password:
Connected.
SQL> select first_name||' '||last_name
2 from employee
3 order by last_name;
from employee
*
ERROR at line 2:
ORA-00942: table or view does not exist
Listing 2 shows how the SQL_201 user grants the necessary object privilege to the BEYOND_101 user so that the query may succeed. Note, however, that after the SQL_201 user has granted the SELECT object privilege for the EMPLOYEE table to the BEYOND_101 user, when the BEYOND_101 user reattempts the query action, the action results in the same error as that received in Listing 1. The same error takes place because, in the absence of a synonym (discussed later in this article), each Oracle Database user must qualify the name of any object it does not own. Listing 3 demonstrates how to qualify the name of the EMPLOYEE table so that the BEYOND_101 user may successfully query it. To qualify the name of an object, you provide the schema name and the object name separated by the period (.) character, for example, SQL_201.EMPLOYEE.
Code Listing 2: The SQL_201 user grants the SELECT object privilege for the EMPLOYEE table to the BEYOND_101 user
SQL> connect sql_201@bynd
Enter password:
Connected.
SQL> grant select on employee to beyond_101;
Grant succeeded.
SQL> connect beyond_101@bynd
Enter password:
Connected.
SQL> select first_name||' '||last_name
2 from employee
3 order by last_name;
from employee
*
ERROR at line 2:
ORA-00942: table or view does not exist
Code Listing 3: Qualify the name of the queried table by appending the schema name
SQL> select first_name||' '||last_name 2 from sql_201.employee 3 order by last_name; FIRST_NAME||''||LAST_NAME ———————————————————————————————————————————————————————————— Lori Dovichi Emily Eckhardt Roger Friedli Betsy James Thomas Jeffrey Sasha Meyer Matthew Michaels Frances Newton Donald Newton Don Rose Gerald Sowell Mary Streicher Marcy Tamra Theresa Wong mark leblanc michael peterson 16 rows selected.
By Any Other Name
If you do not wish to qualify an object name for which you’ve been granted access, you can create a synonym. A synonym is an alias for a qualified object name. Listing 4 shows the SQL statement you use to create a synonym. The syntax, at its simplest, is
CREATE [OR REPLACE] SYNONYMFOR
Code Listing 4: Create a synonym to use as an alias for a qualified object name
SQL> connect / as sysdba Connected. SQL> alter session set container=bynd; Session altered. SQL> grant create synonym to beyond_101; Grant succeeded. SQL> connect beyond_101@bynd; Enter password: Connected. SQL> CREATE SYNONYM employee FOR sql_201.employee; Synonym created. SQL> select first_name||' '||last_name 2 from employee 3 order by last_name; FIRST_NAME||''||LAST_NAME ———————————————————————————————————————————————————————————— Lori Dovichi Emily Eckhardt Roger Friedli Betsy James Thomas Jeffrey Sasha Meyer Matthew Michaels Frances Newton Donald Newton Don Rose Gerald Sowell Mary Streicher Marcy Tamra Theresa Wong mark leblanc michael peterson 16 rows selected.
A synonym created using this syntax is called a private synonym, because it is private to the schema that created it. No other schema can use the synonym created in Listing 4, because it is a synonym created within the BEYOND_101 schema as another name for the SQL_201.EMPLOYEE qualified table name. All synonyms are private unless you specify the PUBLIC keyword. A public synonym is visible to every user in your database. For example, the SQL_201 and BEYOND_101 users are able to query the DBA_TABLES data dictionary view without having to qualify the view name with the SYS schema name, because public synonyms were created for all of the data dictionary views when the database and the data dictionary were created.
It is good practice to never create public synonyms for your application objects. Suppose you have a table named PRODUCT in your schema, but a table named PRODUCT also exists in another schema. Also suppose that the PRODUCT table in the other schema has a public synonym that points to it. When you issue a statement against the PRODUCT table without qualifying the schema, Oracle Database will first try to access the PRODUCT table within your schema. If it is unable to access the PRODUCT table within your schema, it will then attempt to refer to the public synonym for the PRODUCT table in the other schema. If the two PRODUCT tables are structurally different or contain different data, trying to access them without qualifying them could yield unexpected or erroneous results.
An Extension of Rights
To grant an object privilege to another user, you must either
The WITH GRANT OPTION addition to an object privilege grant allows the grantee the ability to pass that same privilege on to others. Listing 5 illustrates how the WITH GRANT OPTION privilege addition allows one user the ability to pass an object privilege on to another. The BEYOND_101 user does not have the ability to query the DBA_TABLES data dictionary view. The SQL_201 user tries to grant the access, but has access only to query the view, not to grant access to it to others.
The SYS user is the owner of the data dictionary and, therefore, is able to grant query access to the SQL_201 user with the ability to pass that object privilege on to others. After the SQL_201 user has been granted an object privilege WITH GRANT OPTION, it can pass that privilege on to others, even though it does not own the object for which the grant is being made.
Code Listing 5: A user granted the WITH GRANT OPTION privilege addition can pass an object privilege to another user
SQL> connect beyond_101@bynd
Enter password:
Connected.
SQL> select table_name
2 from dba_tables
3 where owner = 'SQL_201';
from dba_tables
*
ERROR at line 2:
ORA-00942: table or view does not exist
--The SQL_201 user is able to query from the DBA_TABLES data dictionary view
SQL> connect sql_201@bynd
Enter password:
Connected.
SQL> select table_name
2 from dba_tables
3 where owner = 'SQL_201'
4 order by table_name;
TABLE_NAME
————————————————————————————————————————————————————————————
ANNUAL_REVIEW
DEPARTMENT
EMPLOYEE
EMPLOYEE_CTAS
EMPLOYEE_EXTRA
EMPLOYEE_IDENTITY
EMPLOYEE_SUBSET
7 rows selected.
--The SQL_201 user has been granted query access, but cannot yet pass it on
SQL> grant select on dba_tables to beyond_101;
grant select on dba_tables to beyond_101
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> connect / as sysdba
Connected.
SQL> alter session set container=bynd;
Session altered.
--The SYS user grants the SQL_201 user the ability to pass on the object privilege
SQL> grant select on dba_tables to sql_201 WITH GRANT OPTION;
Grant succeeded.
SQL> connect sql_201@bynd
Enter password:
Connected.
--This time, the grant succeeds
SQL> grant select on dba_tables to beyond_101;
Grant succeeded.
SQL> connect beyond_101@bynd
Enter password:
Connected.
--Similarly, now the BEYOND_101 user can query the DBA_TABLES data dictionary view
SQL> select table_name
2 from dba_tables
3 where owner = 'SQL_201'
4 order by table_name;
TABLE_NAME
————————————————————————————————————————————————————————————
ANNUAL_REVIEW
DEPARTMENT
EMPLOYEE
EMPLOYEE_CTAS
EMPLOYEE_EXTRA
EMPLOYEE_IDENTITY
EMPLOYEE_SUBSET
7 rows selected.
Listing 6 demonstrates how the WITH ADMIN OPTION privilege addition can be used to allow a user to pass system privileges on to others as well. The BEYOND_101 user unsuccessfully attempts to create a sequence for a newly created table. After the SYS user grants the SQL_201 user the ability to create sequences and pass such a privilege on to others, the SQL_201 user grants the CREATE SEQUENCE system privilege to the BEYOND_101 user, and the BEYOND_101 user is then able to successfully create a sequence.
Code Listing 6: A user granted the WITH ADMIN OPTION privilege addition can pass a system privilege to another user
SQL> create table test_me (id number); Table created. --The BEYOND_101 user has not been granted the ability to create a sequence SQL> create sequence test_me_seq; create sequence test_me_seq * ERROR at line 1: ORA-01031: insufficient privileges SQL> connect / as sysdba Connected. SQL> alter session set container=bynd; Session altered. SQL> grant create sequence to sql_201 WITH ADMIN OPTION; Grant succeeded. SQL> connect SQL_201@bynd Enter password: Connected. SQL> grant create sequence to beyond_101; Grant succeeded. SQL> connect beyond_101@bynd Enter password: Connected. --This time, the sequence is created successfully. SQL> create sequence test_me_seq; Sequence created.
Knowing Your Limits
By default, every Oracle Database user account is constrained in terms of the amount of database resources it is allowed to consume and how its password features are managed. These constraints are managed through a named profile. A profile can enforce settings including but not limited to the following:
The statements in Listing 7 illustrate how to discover the profile currently associated with a given user account, as well as how to determine the limit restrictions assigned to the profile. The SQL_201 user has been assigned the DEFAULT profile.
Every user is assigned the DEFAULT profile unless another named profile is explicitly assigned to the user during account creation or through an ALTER USER statement. Currently, the SQL_201 user can have no more than 10 failed login attempts before its user account is locked, and it must be unlocked by a privileged user such as a database administrator. Additionally, the SQL_201 user is currently allowed to be connected to the database but not actively executing any statements for an unlimited amount of time. These two limit settings are outlined in bold in the final result set for Listing 7.
Code Listing 7: Discover the name of and resource limits for the profile assigned to the SQL_201 user
SQL> connect / as sysdba Connected. SQL> alter session set container=bynd; Session altered. SQL> select profile 2 from dba_users 3 where username = 'SQL_201'; PROFILE ———————————————————————————————————————————————————————————— DEFAULT SQL> select resource_name, resource_type, limit 2 from dba_profiles 3 where profile = 'DEFAULT' 4 order by resource_type, resource_name; RESOURCE_NAME RESOURCE LIMIT ————————————————————————— ———————— —————————— COMPOSITE_LIMIT KERNEL UNLIMITED CONNECT_TIME KERNEL UNLIMITED CPU_PER_CALL KERNEL UNLIMITED CPU_PER_SESSION KERNEL UNLIMITED IDLE_TIME KERNEL UNLIMITED LOGICAL_READS_PER_CALL KERNEL UNLIMITED LOGICAL_READS_PER_SESSION KERNEL UNLIMITED PRIVATE_SGA KERNEL UNLIMITED SESSIONS_PER_USER KERNEL UNLIMITED FAILED_LOGIN_ATTEMPTS PASSWORD 10 PASSWORD_GRACE_TIME PASSWORD 7 PASSWORD_LIFE_TIME PASSWORD 180 PASSWORD_LOCK_TIME PASSWORD 1 PASSWORD_REUSE_MAX PASSWORD UNLIMITED PASSWORD_REUSE_TIME PASSWORD UNLIMITED PASSWORD_VERIFY_FUNCTION PASSWORD NULL 16 rows selected.
The statement in Listing 8 demonstrates how to create a profile and assign password and kernel limit restrictions to it. The statement in Listing 9 outlines how to explicitly assign a profile to a user. The setting for FAILED_LOGIN_ATTEMPTS has been reduced to five, and the setting for IDLE_TIME has been reduced to 20 minutes. Note that any limit not explicitly set in the SQL_201_PROFILE profile exists with the value of “DEFAULT.” A profile limit that has a DEFAULT value has the same value as that specified for the same-named limit in the DEFAULT profile.
Code Listing 8: Create a profile with assigned password and kernel setting limits
SQL> connect / as sysdba Connected. SQL> alter session set container=bynd; Session altered. SQL> CREATE PROFILE sql_201_profile 2 LIMIT 3 FAILED_LOGIN_ATTEMPTS 5 4 IDLE_TIME 20; Profile created.
Code Listing 9: Assign a profile to a user and review all assigned settings
SQL> alter user sql_201 PROFILE sql_201_profile; User altered. SQL> select resource_name, resource_type, limit 2 from dba_profiles 3 where profile = 'SQL_201_PROFILE' 4 order by resource_type, resource_name; RESOURCE_NAME RESOURCE LIMIT ————————————————————————— ———————— —————————— COMPOSITE_LIMIT KERNEL DEFAULT CONNECT_TIME KERNEL DEFAULT CPU_PER_CALL KERNEL DEFAULT CPU_PER_SESSION KERNEL DEFAULT IDLE_TIME KERNEL 20 LOGICAL_READS_PER_CALL KERNEL DEFAULT LOGICAL_READS_PER_SESSION KERNEL DEFAULT PRIVATE_SGA KERNEL DEFAULT SESSIONS_PER_USER KERNEL DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 5 PASSWORD_GRACE_TIME PASSWORD DEFAULT PASSWORD_LIFE_TIME PASSWORD DEFAULT PASSWORD_LOCK_TIME PASSWORD DEFAULT PASSWORD_REUSE_MAX PASSWORD DEFAULT PASSWORD_REUSE_TIME PASSWORD DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT 16 rows selected.
Conclusion
This article taught you more about object privileges. You learned how to grant them and how grantees might need to qualify an object name with its schema name in order to take advantage of their granted access. Additionally, you explored the difference between public and private synonyms, and why it is good practice to defer to using private synonyms in your application code. You discovered how to extend privileges to others when privileges have been granted with the WITH GRANT OPTION or WITH ADMIN OPTION options. Lastly, you saw how profiles are used to limit a user’s resource and password settings.
This article concludes the Beyond SQL 101 series. You’ve learned basic relational database concepts and many SQL coding constructs beyond those discussed in the SQL 101 series. Be sure to continue to read the documentation and try existing and new Oracle Database features. Thank you for being readers of Oracle Magazine and of the Beyond SQL 101 series. As you continue writing SQL, my hope is that you enjoy it as much as I do.
Next Steps
READ SQL 101, Parts 1–12.
LEARN more about relational database design and concepts.
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.