In order to secure database, administrator account must be created. Once administrator created, the database becomes secure. To create administrator execute following SQL:
CREATE ADMIN myadmin WITH PASSWORD 'mypwd'
The command will create user "myadmin" and built in roles. The user will be added as a member of "administrator" role and inherit all privileges. If you use a manager, create a new connection in servers explorer tree with specifying username/password.
In order for users to access/modify data or execute procedures, the permissions must be granted. For example create database, table and test procedures:
In a manager execute following script:
create database test; go; use test; go; create table test(id int not null primary key, info char); go; create procedure test_select as begin select * from test end create procedure test_insert (@id int,@info char ) as begin insert into test values(@id,@info) end create procedure test_update (@id int,@info char ) as begin update test set info = @info where id = @id end create procedure test_delete (@id int ) as begin delete from test where id = @id end
Next, create a role with privileges to access/modify tables and execute procedures. Execute SQL script:
USE test; go; CREATE ROLE db_test_access;
USE test; go; -- grant table access rights GRANT INSERT on test to ROLE db_test_access WITH GRANT OPTION; GRANT SELECT on test to ROLE db_test_access WITH GRANT OPTION; GRANT UPDATE on test to ROLE db_test_access WITH GRANT OPTION; GRANT DELETE on test to ROLE db_test_access WITH GRANT OPTION; -- grants execute rights GRANT EXECUTE on test_select to ROLE db_test_access WITH GRANT OPTION; GRANT EXECUTE on test_update to ROLE db_test_access WITH GRANT OPTION; GRANT EXECUTE on test_delete to ROLE db_test_access WITH GRANT OPTION; GRANT EXECUTE on test_insert to ROLE db_test_access WITH GRANT OPTION;
Optional [WITH GRANT OPTION] indicates that the role/user may pass/grant the permission to other user/role.
Now, create a user and add member role db_test_access to user. The user will have the rights of the role. If a new permissions added to the role, user gains the permission too, or, if revoked from role, users' permission also be revoked.
Execute SQL script:
USE test; go; CREATE USER myuser WITH PASSWORD '123' go; ADD ROLE db_test_access TO USER myuser
In a manager add new server connection with users' "myuser" login and execute procedures:
USE test; go; exec test_insert @id=0,@info='test0000' exec test_update @id=0,@info='test0001' exec test_select
to delete, execute:
USE test; go; exec test_delete @id=0
Revoking rights from role will affect the user. Execute revoke script in a query window with "myadmin" credentials:
USE test; go; REVOKE SELECT ON test FROM ROLE db_test_access;
And then execute the SQL with "myuser" credentials:
USE test; go; exec test_select -- should fail
If we grant exclusive SELECT permission by "myadmin", the user will be able to select even if "db_test_access" has no permissions. With "myadmin" execute:
USE test; go; GRANT SELECT ON test TO USER myuser;
now, with "myuser" credentials, should execute without errors:
USE test; go; exec test_select