I'm new to MySQL, moving from MS SQL Server, and I'm wondering if there's a way to logically group tables, views, sp's, etc in the same manner as you would with schemas in SQL Server….seeing as how the concept of a schema is entirely different in MySQL. In SQL Server I was able to use both schemas and roles to easily manage permissions. I've found that I can create roles in MySQL and suppose I could write a script that grants permissions on a gazillion objects that way. Just wondering if there is a cleaner, easily maintained approach. Am I missing something?
Mysql – Organizing and assigning privileges to multiple objects in MySQL
MySQLpermissionsschema
Related Solutions
Regarding providing DBA access, you might be better-off creating an Active Directory group and adding the DBAs into that group. That way, you can provide group-level permissions and give sysadmin to the group rather than individual users, meaning that when a DBA leaves (or a new one gets hired), you just need to adjust Active Directory rights rather than going into each SQL Server and altering logins. The same applies (though without granting sysadmin!) for other groups of internal users. Windows authentication is more secure than SQL authentication, and if you can use it, you probably should.
Regarding auditing, you can still have individual-level auditing when users are in a group. You can use SUSER_NAME()
to get the username in whatever process you're going to use to log activity. Note that somebody could do an EXECUTE AS
to switch their user context, but you should be able to log that as well as the IP address from which the query came, so you'd still be able to correlate activity to a person.
The idea of using a jumpstation (in your case, you mentioned a server) is possible. It would make administration a bit easier, especially if the laptop domain does not have a full trust relationship with the domain upon which the SQL Server instances are located. It would probably be a bit annoying for the support personnel, honestly, but if you go that route, I'd recommend a virtualized desktop for each IT support team member. That way, they don't have to deal with server contention issues and you can minimize the pain. If you do go down that route, then the IT support team members could have domain accounts and you'd create relevant Windows groups the same way as the DBA group.
I've done this a couple of weeks ago -
Created LOGIN
, ROLE
and USER
. Added USER
to ROLE
and granted explicit permissions to the ROLE
itself. The advantage in my opinion is, that you can add further users later on without the struggle to grant the permissions, again.
First I created the login:
IF NOT EXISTS
(
SELECT
"name"
FROM
"master"."dbo"."syslogins"
WHERE
"name" = 'your_login'
)
BEGIN
CREATE LOGIN your_login WITH PASSWORD = 'your_password', CHECK_POLICY = OFF;
END
I've added the CHECK_POLICY param because things are created on user interaction and I'm not able to see, if policies are activated server-sided.
Followed by the user:
IF NOT EXISTS
(
SELECT
1
FROM
"sys"."database_principals"
WHERE
"name" = N'your_username'
)
BEGIN
CREATE USER your_username FOR LOGIN your_login;
END
And the role third:
IF NOT EXISTS
(
SELECT
1
FROM
"sys"."database_principals"
WHERE
"name" = N'your_role_name'
)
BEGIN
CREATE ROLE "your_role_name";
END
Finally I added the user to the created role:
EXECUTE sp_addrolemember N'your_role_name', N'your_username';
After that, permissions can be set for each type of object, eg
GRANT EXECUTE ON "dbo"."your_sproc" TO your_role_name;
You don't have to add denydatareader
or else, if you explicitly grant permissions to the specific objects. Beware that an for example INSERT
permission doesn't include the SELECT
permission on the same object. ( at least in my case it didn't ;) )
If you have problems executing or selecting from your UDF
, check if you have granted all permissions on the referenced objects in the UDF
itself!
Update after Cobus' update in OP:
I've executed the queries you provided based on my initial answer, results following:
select * from dbo.testtable
-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
update dbo.testtable set id = 4 where id = 2
-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'. -- The UPDATE permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
update dbo.testtable set id = 4
-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
insert into dbo.testtable (id) values (5)
-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
It behaves as intended. Do you have any further permissions granted / set or properties which allow you to access dbo-schema?
Related Question
- Mysql – Why does MySQL allow HAVING to use SELECT aliases
- SSRS Permissions – Executing Subscription
- SQL Server 2000 – How to GRANT Permissions to All Tables, Views, Procedures
- PostgreSQL – How to Manage Default Privileges for Users on Database vs Schema
- SQL Server – Permissions Needed for Views Built on Other Views and Tables
Best Answer
MySQL does not support multiple schema in Database, you must create separate database and grant permissions to roles as per your requirements.