Database Reference
In-Depth Information
You can grant a system privilege to several users at once.
Let's say that PRINCE needs to be able to create his own tables and
views. Plus, you want PRINCE to be allowed to give these privileges to
other users. The following command gives him the privileges needed:
GRANT CREATE TABLE, CREATE VIEW TO PRINCE
WITH ADMIN OPTION;
As you can see, it is possible to list more than one system privilege in a
single GRANT command.
Now, let's say that ARIEL needs to be able to run queries and modify
data in the MUSICCD table. Because ARIEL does not own the table, she
must be granted object privileges. Both the DBA user and the owner of a
table can grant object privileges on a table. So we can connect to the
MUSIC schema and grant privileges to other users.
CONNECT MUSIC/MUSIC@OLTP;
Now run this command to give ARIEL the capabilities she needs:
GRANT SELECT, INSERT, UPDATE, DELETE
ON MUSIC.MUSICCD TO ARIEL;
You decide that the information about artists should be viewable by any
user who can log onto the database. The PUBLIC user group is a special
group accessible by all users. Rather than granting privileges to all users
individually, use PUBLIC. Any privilege granted to PUBLIC is granted to
all users, even users that are created after you issue the GRANT command.
The next command gives all users the ability to query the ARTIST table:
GRANT SELECT ON MUSIC.ARTIST TO PUBLIC;
Now you could log in as ARIEL and test out what you are allowed to see
in the MUSIC schema.
CONNECT ARIEL/MERMAID@OLTP;
