Creating User and granting rights in Sybase
Implementing Database in Sybase – Part 8
Division 4
Forward: In this part of the series, we see how to create user and grant rights to the user in a Sybase SQL Anywhere 12 database.
By: Chrysanthus Date Published: 27 Aug 2012
Introduction
We are still dealing with data definition. Remember data definition deals with creating of tables, altering tables, dropping tables, creating of indexes and giving different users, privileges to different tables. We have seen how to create tables and give them indexes. In the previous part of the series we saw how to alter and drop tables. In this part we shall see how to create a user account (user name and password) and grant him certain permissions.
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Creating a User
In simple terms the syntax of the SQL statement to create a user is:
CREATE USER user-name [ IDENTIFIED BY password ];
The database administrator (you at the moment) has the permission to create a user.
The user name is the name the user gives you. The password is the secret word the user gives you. We shall create a user with the user-name, newguy and password new44.
You can drop (remove) a user. The syntax to that is:
DROP USER user-name
The database administrator (you at the moment) has the permission to drop a user.
By now you should know how to start the server and database and then connect to the database through the Interactive SQL tool. The database is a file (document) and so it cannot run on its own; it needs a database to run it. So use the following commands to start and connect to the database at the command prompt; when you type one command you wait for it to take effect before you type the next:
cd c:
dbeng12 -n wholesalesrv c:\wholesale\wholesale.db
dbisql -c "server=wholesalesrv;DBF=c:\sholesale\wholesale.db;UID=DBA;PWD=sql"
- Type the following SQL statement into the SQL Statement pane in the Interactive SQL window and execute it:
CREATE USER newguy IDENTIFIED BY new44;
All SQL statements end with semicolon.
Sybase SQL Anywhere has the SQL GRANT statement, which can be used to do many things. We shall only use it here to see how the Database Administrator (DBA) can grant a user the permission to delete row in a particular table, insert (add) row into a particular table row, update (modify) row in a particular table, and to view (retrieve) all rows of a particular table. As you can see in a database, different users have different rights on different tables.
The Grant SQL Statement syntax that interest us is:
GRANT permission, ...
ON [ owner.]object-name
TO userid, ...
[ WITH GRANT OPTION ]
[ FROM userid ]
permission :
ALL [ PRIVILEGES ]
| ALTER
| DELETE
| INSERT
| REFERENCES [ ( column-name, ... ) ]
| SELECT [ ( column-name, ... ) ]
| UPDATE [ ( column-name, ... ) ]
Here, the SELECT permission is the authority to view all table rows. object-name means table name.
We continue with the wholesale example. Let us grant the new user the permissions mentioned above to the Products table.
- Type and execute the following SQL statement in the SQL Statement pane of the Interactive SQL window (erase whatever is there first) and execute it.
GRANT DELETE, INSERT, UPDATE, SELECT ON Products TO newguy;
Note that in the statement, the permissions are separated by commas.
- Now, close the Interactive SQL window (do not save the SQL statements since their executions have automatically been saved). Drop the connection; stop the database and stop the server with the following command at the command prompt (type y is asked):
dbstop wholesalesrv
There is a lot more to granting rights than what I have given in this tutorial. I intend to write a whole series on granting rights and security. What I have given you here is enough to get you working (when you finish the big series).
We have come to the end of the basics of data definition in a database. Let us stop here and continue in the next part of the series.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer – A Jack of all Trade – Poem
NEXT