|
|
In the following sections, you'll look at some of the same tricks you learned in the SQL Server section. You'll create a new user called Alice, see how you can change passwords, and then examine how you work with permissions in Oracle.
Oracle comes with two users that are created by default: SYSTEM and SYS. In order to create new users, you log in using the SYSTEM user because SYSTEM has the required privileges.
To create a new Oracle user, you use the CREATE USER command, which needs to be supplied with the username and its password. Here's how you create the Alice account in Oracle, with a default password of simplepassword:
CREATE USER Alice IDENTIFIED BY simplepassword;
If you want to change the simple password of Alice to a complicated one, you need to use the ALTER USER command. For example:
ALTER USER Alice IDENTIFIED BY complicatedpassword;
Alternatively, you can use the password command in SQL*Plus, which doesn't display the passwords as you type them. When logged in as Alice, simply type password and press Enter. When logged in as SYSTEM, type password Alice to change Alice's password.
You can remove users with the DROP USER command. If you want to give Alice a nasty surprise, try this:
DROP USER Alice;
| Note |
With Oracle, you can assign profiles to user accounts via the CREATE PROFILE command. Using this command, you can set the lifetime of a password, the number of consecutive failed attempts allowed before the account is automatically locked and for how much time it will remain locked, and other similar parameters regarding passwords. Please look up the CREATE PROFILE command in the Oracle Alphabetical Reference for more information about this command. |
Right now, although Alice does have a new account, she can't do many things with it. By default, a new user isn't even allowed to log in—all access rights must be explicitly defined. You'll use GRANT and REVOKE commands to adjust the permissions for each user.
To let Alice connect to Oracle, you need to assign her the CONNECT role. The CONNECT role allows a user to connect to the database, but this right only becomes meaningful when combined with other permissions (such as permissions to access or modify existing data tables).
| Note |
CONNECT, RESOURCE, and DBA are the three standard roles in Oracle. The CONNECT role permits users to connect to the database. Users with the RESOURCE role are allowed to create their own tables, procedures, triggers, and other types of database objects, and the DBA role allows for unlimited access. |
To assign Alice the CONNECT and RESOURCE roles, log in using a database administrator account (such as SYSTEM) and execute the following command:
GRANT CONNECT, RESOURCE to Alice;
Note that the RESOURCE role doesn't include the privileges supplied by CONNECT. You can test this by providing Alice only with the RESOURCE role (without CONNECT) and then trying to connect to the database using the Alice account—the database won't let you in!
Be aware that a user who is granted the RESOURCE role obtains many system-level privileges, including UNLIMITED TABLESPACE, which allows the user to consume any amount of space in any tablespace. Don't haphazardly grant the RESOURCE role to any user, and if you do use it, it's a good idea to revoke the UNLIMITED TABLESPACE privilege.
REVOKE has a similar format, except you use FROM instead of TO. Here's an example:
REVOKE RESOURCE FROM Alice;
After revoking the RESOURCE role from Alice, she won't be able to do anything with her account, except log in to the database. Give her the RESOURCE role again so you can do some more tests.
To grant object-level permissions, you use the usual syntax:
GRANT <privilege type> ON <resource> TO <username>
The object-level permissions supported by Oracle are as follows:
ALTER allows the grantee to change the table definition using the ALTER TABLE command.
DELETE, INSERT, SELECT, and UPDATE are the permissions that apply to data tables and views.
INDEX allows the grantee to create indexes on the mentioned data table.
REFERENCES allows a user to insert rows into a table that has a FOREIGN KEY constraint to another table where the user doesn't have SELECT permissions.
EXECUTE allows the user to run the specified function or stored procedure or to access any program object declared in the specification of a package.
Let's look at a quick example to see how these work. First, connect to Oracle as Alice using a command similar to the following:
CONNECT Alice/complicatedpassword@service name;
where @service name is sometimes required by Oracle to connect to the database server.
| Caution |
Note how you can use the CONNECT command to change the logged-in user; don't confuse the CONNECT command (which changes the current user) with the CONNECT role. |
Second, when logged in as Alice, try to read data from the Student table in the InstantUniversity database. Note that when accessing some other user's objects you need to specify the owner username along with the object name:
| Note |
In these examples we assume you created the InstantUniversity database while logged on with the INSTSQL account. If you created it using another account (for example, SYSTEM), please use that account instead. |
When executing this command using Alice's account, you'll be told that INSTSQL.Student doesn't exist. This is because Alice doesn't have SELECT permissions on Students.
To grant her access on Student, you need to log in again as the administrator of the InstantUniversity database:
CONNECT INSTSQL/password@service name;
| Note |
You could run this command while logged on as SYSTEM, but as a general rule, you should always work with the least privileges necessary to perform a task; because you only need administrative privileges on the InstantUniversity database, you'll use the account that just has privileges for that database. |
When logged in as INSTSQL, execute the following GRANT command, which grants Alice access to SELECT and INSERT data on the Student table:
GRANT SELECT, INSERT ON Student TO Alice;
Now connect again as Alice and perform two operations on the Student table as follows:
INSERT INTO INSTSQL.Student (StudentID, Name) VALUES (13, 'George Bush'); SELECT * FROM INSTSQL.Student;
The results will show the rows inserted with both the INSTSQL account that you've been using and the row added using the Alice account:
StudentID Name ------------------ ---------------------- 1 John Jones 2 Gary Burton 3 Emily Scarlett 4 Bruce Lee 5 Anna Wolff 6 Vic Andrews 7 Steve Alaska 8 Julia Picard 9 Andrew Foster 10 Maria Fernandez 11 Mohammed Akbar 12 Isabelle Jonsson 13 George Bush
Now, try to do an update (while still connected as Alice):
UPDATE INSTSQL.Student SET StudentID = 555 WHERE StudentID = 13;
If you try this, you'll be told you don't have enough privileges. This is true because Alice only has INSERT and SELECT privileges.
You can use GRANT with the WITH GRANT OPTION that gives the user the right to pass along the received privileges to other users. If you wanted Alice to have the rights to give INSERT and SELECT permissions on SomeTable to other users, you need to execute something similar to this:
GRANT SELECT, INSERT ON Student TO Alice WITH GRANT OPTION;
To revoke the INSERT permission on Student from Alice, you use REVOKE:
REVOKE INSERT ON Student FROM Alice;
If you want to revoke the INSERT permission on Student from Alice and from all the other users who received this permission from Alice, you use the CASCADE keyword. This only applies if Alice was provided the permission using WITH GRANT OPTION:
REVOKE INSERT ON Student FROM Alice CASCADE;
In Oracle, in addition to the three system roles mentioned earlier, you can create new roles using the CREATE ROLE command. Oracle even supports associating passwords to roles.
While logged in with the SYSTEM account, create a new role named TestRole:
CREATE ROLE TestRole;
While you're still logged in as SYSTEM, assign it to Alice:
GRANT TestRole TO Alice;
When created, the role doesn't have any privileges. So, log back in as INSTSQL and add the UPDATE privilege on Student to TestRole:
GRANT UPDATE ON Student TO TestRole;
Now log in once again as Alice. While logged in as Alice, try to update Student. Remember that before having the role, this was impossible. Now, the following command executes successfully:
UPDATE INSTSQL.Student SET StudentID = 555 WHERE StudentID = 13;
To remove the granted role from Alice, you use REVOKE while being logged in as SYSTEM:
REVOKE TestRole FROM Alice;
To finally drop the role, do the following:
DROP ROLE TestRole;
Don't forget to delete George Bush from the Student table when you're done.
GRANT and REVOKE also deal with statement-level permissions. With two exceptions, these rights allow the user to create new database objects. The list of statement-level permissions supported by Oracle is very long; please consult the GRANT section (under "System Privileges and Roles") in the Oracle SQL Reference for complete details.
Typical statement-level permissions are CREATE DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, and so on. Statement-level permissions also can be granted using WITH GRANT OPTION, in which case the grantee can grant the received privileges to other users.
Note that many of these permissions are included with the RESOURCE role. For example, users having the RESOURCE role have permissions to create their own data tables. However, if you want to be restrictive about the permissions you grant to a user, you'll prefer to grant individual rights instead of assigning the RESOURCE role.
|
|